Tag Archives: custom port

Configure Custom SQL port for SharePoint

The article is taken from the below link:

https://letitknow.wordpress.com/2013/04/04/cannot-connect-to-database-master-at-sql-server-checklist/#no9

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 (10.1.1.111)

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 10.1.1.5 address
DNS record is correct and it resolves to the 10.1.1.5 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
Advertisements