Tag Archives: SharePoint

Configure Custom SQL port for SharePoint

The article is taken from the below link:


I don’t take any credit for this information.

1. Check you typed the correct name

Well, this is very straightforward but make sure you set up everything on the correct server and you typed that name here, also be careful with typos.

2. Check your instance name

Do you want to use a named instance other than the default one, submit it in the following order: Servername\instancename Make sure it has the same name on SQL server as well among services.

3. Name resolution check

Start a command prompt, ping your machine and review the IP address. Now check the IP addresses on your SQL machine with ipconfig or on the interfaces. If they are matching jump on the next point.

My SP machine resolves this name to an incorrect IP
On my SharePoint machine: resolves this name to an incorrect IP
My SQL machine does not have an IP like that
On the SQL machine: it does not have an IP like that (

Otherwise try troubleshooting this name resolution problem. If your SQL server has multiple cards make sure DNS resolves it to the correct one. Review your records in DNS and correct them if necessary.

DNS record is correct and it resolves to the address
DNS record is correct and it resolves to the address

Also make sure that your SharePoint machine’s host file (C:\windows\system32\drivers\etc) does not contain invalid data.

Hosts file
my hosts file contains invalid data

4. SQL server service is running

It’s lame, I know, but I have seen this so just make sure your DB service of the correct instance is running:

Service is not running
Service is not running (SQL Server MSSQLSERVER)

5. TCP/IP protocol for SQL server is enabled

On the SQL box open SQL Server Configuration Manager and under Network Configuration node check that TCP/IP is enabled. (If you modify this don’t forget to restart the service)

TCP/IP protocol is not enabled on my SQL instance
TCP/IP protocol is NOT enabled on my SQL instance

6. Check listening ports and IP addresses

In SQL Server Configuration Manager open the Properties of the enabled TCP/IP protocol. Here you can specify the IP address and listening port of this instance. On the first tab if Listen All is enabled (Yes) the used port is in the IP Addresses tab’s last section, IPAll/TCP port. This means that the service listens on all interfaces on this port. Use netstat -ab to review this.

Listen on all interfaces and use port 14333
Listen on all interfaces and use port 14333
Sql listens on port 14333
According to the settings above SQL indeed listens on port 14333

If it is not the default port (1433) you have to indicate this in your connection string (in Config Wizard’s database server parameter)

On the first tab if Listen All is disabled (No) you can set listening on individual ports and cards just don’t forget to enable at least one!

Listening on this interface is enabled, uses default port
Listening on this interface is enabled, uses default port
Netstat reflects the changes
The netstat output shows the changes nicely

Wit this info in hand you should be able to telnet from the SharePoint machine to the specified port with the name to verify network connectivity.

Telnet failed
Telnet failed

If it does not work check firewall settings on the SQL box and between the servers.

7. Windows firewall

If windows firewall is enabled verify it blocks the connection or not. First enable logging here (do this on the profile you use): Windows Firewall with Advanced Security / Right click and select Properties / Click Customize in the Logging section:

  • Log dropped packets : Yes
  • Note the logfile’s path

Try to connect with telnet again from the SharePoint machine and review the content of the logfile.

Windows firewall blocks my connection
Windows firewall blocks my connection

To remediate this turn windows firewall off on the selected profile (or on all profiles) otherwise create an allow rule for this incoming connection, whichever suits your scenario.

8. Other connectivity check between your servers

If your telnet does not even reach the SQL server and there is nothing in the logfile maybe you have connectivity issues between your machines. Are there any firewalls between them? If yes, create firewall rules or ask your firewall team to do it.

9. SQL Server client alias setting

Type cliconfg on your SharePoint machine and review the alias tab. If there is any entry make sure it points to the correct server and you use the correct alias during configuration wizard.

SQL alias
My SQL alias points to server sql01 on port 1433 so I can use “sql5” in configuration wizard to set up a connection

SQL alias has the advantage to change SQL server later easily (connection parameters) while the server name remains the same for SharePoint.

10. SQL permissions

On SQL server open the Application log in the event viewer and look for ID 18456, Category Logon. Don’t be fooled this won’t be marked as an error or warning, simple Information is the level.

Failed logon with the install account
Failed logon with the install account

Did you start the configuration wizard with your install account? If yes you have to check SQL Logins and assigned roles for your user with Management Studio (connect to your instance). Remember, your setup user needs a Login and have to be a securityadmin and a dbcreator! (http://technet.microsoft.com/en-us/library/ee662513.aspx)

Setup user roles
Setup user roles: yes indeed, he can create databases and manage permissions

If everything seems right you can always check the logs to gain more information, here they are again:

  • Event viewer on the SharePoint machine
  • SharePoint Products Configuration Wizard’s logfile in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\LOGS (by default and their name’s start with PSCDiagnostics_<date>_<SomeID>)
  • Event viewer on the SQL server
  • Windows firewall logfiles

Configure SSL for SharePoint 2010

Reference: https://blog.mastykarz.nl/configuring-ssl-sharepoint-2010-development-environment/

Once in a while, when working with SharePoint solutions you need to test them on sites using SSL just to be sure that everything is working just as intended. Although I have previously wrote about how to configure SSL for SharePoint development environments, SSL configuration in IIS7 has changed slightly. Find out how to configure SSL on SharePoint 2010 development environments.

When working with SSL in development environments there are two scenarios: you are either creating a new Web Application and would like to make it work with SSL from the very first day or you already have a working Web Application and would like to make it work with SSL as well. Because those scenarios are not that different we will focus on the first one which should provide you with sufficient information should you need to add support for SSL to an existing Web Application.

Configuring SSL for a new Web Application

Let’s start off with creating a new Web Application.

For the purpose of this article I will be using the secure.mavention.local host header which I already added to the HOSTS file on my development machine mapped to the IP address.

Parameters for creating new secure Web Application

The most important things that you should keep in mind, while creating a new secure Web Application, is to set the port to 443 (1) and in the Security Configuration section set the Use Secure Sockets Layer (SSL) option to Yes.

Next, to be able to confirm that everything is working correctly, let’s create a Site Collection. You could create a Site Collection using a specific Site Definition/Web Template but for the purpose of simply confirming that we have configured SSL correctly, using the Blank Site Site Definition is sufficient.

Configuration of the new Site Collection used for SSL configuration verification

If you choose to navigate to the newly created Site Collection at this moment all you will see is an error message similar to the following:

‘Internet Explorer cannot display the webpage’ error message displayed in browser

The reason for this error is that, although we have chosen to have a secure site, we haven’t attached any SSL certificate to the IIS Web Site hosting our Web Application.

Creating Self-Signed Certificate in IIS7

To configure an SSL certificate with our Web Application let’s navigate to the IIS Manager.

For the purpose of this article we will use a self signed certificate. If you have a valid SSL certificate you can use it instead by importing it to IIS on your development machine.

In the Connections list select your local IIS server (1). Then from the IIS section select the Server Certificates option (2).

Opening the Server Certificates configuration in IIS7

Next, from the Actions toolpane choose the Create Self-Signed Certificate… option.

Red arrow pointing to the ‘Create Self-Signed Certificate’ option in IIS7

Enter secure.mavention.local as the friendly name of the certificate and confirm the creation process by clicking the OK button.

Creating Self-Signed certificate in IIS7

The next step is to associate the newly created Self-Signed certificate with our Web Application.

Associating SSL certificate with secure Web Application

Although IIS Manager allows you to configure a variety of settings for your Web Sites, one thing it doesn’t support is configuring a secure binding based on hostname.  As soon as you select an SSL certificate to associate with a binding (1) IIS Manager will clear the existing host header (2).

Editing a secure site binding in IIS Manager

If you would confirm your changes you would end up with a broken Web Application. Without the host header you would end up landing on the default site instead of our secure Web Application.

You can associate your Self-Signed certificate with your Web Application in two ways.

Using the AppCmd Command Line tool to associate an SSL certificate with an IIS Web Site

One way of associating an SSL certificate with a website is to use the appcmd Command Line tool to set the hostname to an existing secure binding.

First, in IIS Manager, from the list of available Web Sites select your secure Web Site. Then, from the Actions toolpane select the Bindings… option.

Red arrow pointing to the Bindings option

In the Bindings settings select your SSL certificate (1). Note that IIS Manager will remove the previously set host name for your Web Site (2).

Editing the site binding in IIS Manager

Confirm changes by clicking the OK button.

Next, open the Command Prompt as Administrator, change the directory to C:\Windows\System32\inetsrv and type the following command:

appcmd set site /site.name:"SharePoint - secure.mavention.local443" /bindings.[protocol='https',bindingInformation='*:443:'].bindingInformation:*:443:secure.mavention.local  

Executing this command will add the host name to the existing secure binding for your Web Site. If you navigate back to IIS Manager you should see the host name set on the secure binding.

Secure binding with host name set

And if you edit the binding you should see that the SSL is correctly associated as we did in the previous step.

Note that, although the host name is correctly configured, IIS Manager doesn’t display it in the Edit Binding window.

Although this approach is rather easy to follow it has a flaw as it allows you to use only one SSL certificate for all Web Sites. If you want to use your development environment for working with multiple secure Web Applications, each using a different SSL certificate you should take a look at the next approach.

Binding SSL certificate by using different IP addresses

Another way of binding SSL certificates to Web Sites in IIS is to use a different IP address for every Web Site. The great advantage of this approach is that it can be fully managed through UI and allows you to use different certificates.

The first thing that you have to do is to add another IP address to your network adapter. You can do this in the Advanced TCP/IP Settings of your Network Adapter.

Multiple IP addresses assigned to a single network adapter

Next, you have to change the binding between the host header and the IP address. As I mentioned before, for the purpose of this article I added an entry to the HOSTS file on my machine. To support this approach I have changed the IP address from to which is the new IP address I added to my network adapter.

Hosts file with the secure.mavention.local host name entry modified to point to the IP address

The last step is to modify the binding of your Web Site in IIS. For this open the IIS Manager, from the list of available Web Sites choose the one that hosts your Web Application and from the Actions toolpane choose the Bindings option. In the Site Bindings window select the secure binding and click the Edit… button. In the IP address field type and select the SSL certificate that you want to use. Confirm the changes by clicking the OK button.

Site binding configured to support the additional IP address

When you navigate to the website now you should see the warning about the untrusted certificate and if you ignore it, you should see the Site Collection that you created initially.

Site Collection of the secure Web Application

PowerShell Script to delete items from SharePoint List

The criteria for the below script is to delete items that created before 7 days , you can put it as scheduale task to run weeklly to clear the large lists.

Add-PSSnapin Microsoft.SharePoint.PowerShell
$list = $web.Lists[“YOUR LIST NAME”]
$DeleteBeforeDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now.AddDays(7))
$caml=‘<Where> <Lt> <FieldRef Name=”Created” /><Value Type=”DateTime”>{0}</Value> </Lt> </Where> ‘ $DeleteBeforeDate
$query=new-object Microsoft.SharePoint.SPQuery
WriteHost $col.Count
$col | % {$list.GetItemById($_.Id).Delete()}

Courtesy: https://gallery.technet.microsoft.com/office/PowerShell-Script-to-f4e8ea90

The report server cannot decrypt the symmetric key – Reporting Servie, SSRS, SharePoint


"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content.



As the error shows, it is an access denied on a registry entry. A Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx trace can show which registry key needs permissions for the service account of the SSRS service.

Usually, it could be this –

HKLM\Software\Microsoft\Microsoft SQL Server\Reporting Services\Service Applications

A brute force change to permissions at HKLM\Software\Microsoft\Microsoft SQL Server by adding the desired user with Read (propagated to child folders) could show if that’s all that is required.

Assign the service account full permission or if it doesn’t work assign everyone full permission on the desired key.

There may be multiple keys which is giving access denied in the process monitor.

try to fix all of them one by one.

Infopath has removed the submit button from SharePoint list form

Do you want people to be able to quickly fill in a form on your site, over and over again?  One option for this has always been datasheet view, but sometimes you have a more complex form.  In this post, I’ll show how you can create a button that will save the current record and immediately go to a new blank form.

In this example, I’ll just use a simple announcements list.


  1. Go to your announcements list. On the List tab, click the button Customize Form.
  2. Put your cursor at the bottom of the form where you want to put your buttons.  From the list of Controls in the ribbon, add two buttons to the form.
  3. On the Data tab, click the Submit Options button.  Click the Advanced button.
  4. Next to After submit, choose Open a new form.  Click OK.
  5. Double-click to select the first button, and in the ribbon, click Manage Rules.
  6. In the Rules pane on the right, click the New button and choose Action.
  7. In the Details for box, type Submit. Click the Add button and choose Submit Data. Select your data connection to submit to.  This example is a SharePoint list, so it automatically has the one called “Main Data Connection”.  Click OK.
  8. Click Add again, and choose Close the form.  Click OK.
  9. In the Properties tab of the ribbon, label this button as SAVE.
  10. Double-click to select the second button still labeled “Button”.  Find the Action drop-down box in the ribbon.  Change the action to Submit.
  11. Change the button label to say Save & New.
  12. Publish the form and close InfoPath.

Done!  Now, if you want to make it look pretty and professional, use picture buttons instead of those old gray ones.  I like to use PowerPoint to create buttons and make them PNG files.

ONE NEGATIVE to this solution:

If you’re using a SharePoint list form customized with InfoPath, you’ll still have the save button in the ribbon.  That Save button will do the “save and new” functionality, and there is not a way to change this without messing up this solution.  One option you have is to remove the ribbon altogether.  Click File and click the Form Options button.  On the Web Browser tab, uncheck the box next to Show InfoPath commands in Ribbon or toolbar.  Unfortunately, when you do this, there won’t be an edit button there on the form either, and the only way people can get to the edit screen will be to use the drop-down box on that item when in the list view, or use the ribbon when in list view.

Reference: http://www.wonderlaura.com/Lists/Posts/Post.aspx?ID=194

Make SharePoint search results link to document instead of DispForm.aspx with no iFilter

Problem: How do I get Search to return the results witha link to the document itself instead of DispForm.aspx?

Resolution: To verify this go to Central Admin > Manage service applications > Search service (your instance) > File Types (found under the crawling section).
1.Add pdf if it isn’t there.
2.Restarted the search service from services.msc.
3.Reset the index
4.Ran a full crawl.

The search results will now linked to the document itself instead of the DispForm.aspx, without installing any iFilter.

Reference: http://sharepoint.stackexchange.com/questions/3260/make-sharepoint-search-results-link-to-document-instead-of-dispform-aspx-with-no

Taxonomy picker control disabled – SharePoint 2010 Term Store

Taxonomy picker control disabled:

You might need to go to the library settings and make sure it is enabled for keywords. Try the Enterprise Metadata and Keywords link. Go to the column [wiki categories] and then select the term set that you would like to use? It remains grayed out until you select some term set to start with :).

If it’s a site column: Goto site settings > site columns > edit the column(managed metadata column)> select the appropriate metadata field> OK


SharePoint Stsadm Backup\Restore vs Import\Export

Below table illustrates the differences.

Stsadm Backup/Restore Stsadm Export/Import
Backup\Restore a Site Collection or a Web application. Exports data of a Subsite, site collection, an entire web application, or a single list.
The GUID of all items in a Site Collection is preserved except the GUID of the Site collection itself. When you restore the backup, SharePoint generates a new GUID for the site collection. It generates a new GUID for every objects such as sites, sub sites, lists and items.
Because of the same Guid, you can restore in the same web application or farm but in a different Content database. Because of the new Guid, you can restore or import the data in the same site collection\site or web application and it does not matter on which content database you run import operation.
This operation is designed to take an exact copy of a site collection, no data will be changed, transformed or lost. This operation is good for merging content of sites, and decide how to handle identical data between source and target.
All workflows instances, associations, history and tasks are preserved. A major drawback of this operation is that it does not preserves workflows instances, associations, history and tasks. Every workflow association must be recreated and there is no way to restore the running instances from original site.

Courtesy: http://www.learningsharepoint.com/2014/03/10/sharepoint-stsadm-backuprestore-vs-importexport-3/#ixzz2vXYWXD8L

Start and Stop SharePoint services using Powershell:

  1. Start and Stop SharePoint services using Powershell:

To Start:

$ServiceName = “Central Administration”

Get-SPServiceInstance -server spinrat2 | where-object {$_.TypeName -eq $ServiceName} | Start-SPServiceInstance -confirm:$false > $null

To Stop:

$ServiceName = “Visio Graphics Service”
Get-SPServiceInstance -server COMPUTERNAME | where-object {$_.TypeName -eq $ServiceName} | Stop-SPServiceInstance -confirm:$false > $null