Steps to troubleshoot SQL Server connectivity issues

  • Article

Steps to troubleshoot SQL Server connectivity issues

 

When you have a connectivity issue with SQL Server you can use the following steps to verify some of the possible causes: 

1. First you must identify the exact error message you receive : in SQL Server Management Studio or while starting a specific SQL Service;

2. You have to identify the ports that are used by SQL Server. To do that you have to go to SQL Server Configuration Manager – SQL Server Network Protocol- Protocols for InstanceName – right click on TCP/IP – properties- IP Addresses – IP All;

3. Make sure all protocols are enabled : Shared Memory, TCP/IP and Named Pipes;

4. Verify in SQL Server Configuration Manager if you have any aliases configured (if you have aliases configured, try to see if the issue is reproduced without them):

- SQL Native Client 11.0 Configuration (32bit) –Client Protocols and Aliases;

- SQL Native Client 11.0 Configuration–Client Protocols and Aliases;

5. Make sure the startup accounts for all SQL Server Services have the appropriate rights, if not, you can change it to an domain account that is also sysadmin for SQL Server(just to verify this possible cause and afterward change it back);

6. You can open errorlog file for SQL Server to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under: %ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log;

7. Verify if you have Firewall enabled on the machine. If  this is the case please make sure you have the following rules that allows SQL Server ports to be opened:

  1. o For TCP protocol, you need put the TCP port on which the SQL Server listens on into exception;
  • o For SQL Browser, please put UDP port 1434 into exception;

8. Disable the antivirus if you have one;

9. Use the Port Query tool to verify the status of the ports:

- Download and install PortQuery tool on the SQL Server machine where the connectivity does not work. More details about the tool you can find in the article Description of the Portqry.exe command-line utility.  ttp://www.microsoft.com/downloads/details.aspx?FamilyID=8355e537-1ea6-4569-aabb-f248f4bd91d0&DisplayLang=en

- Run the PortQuery tool (portqueryui.exe).

- Query SQL Service:

  • In “Enter destination IP or FQDN to query” text box please type the FQDN of the machine where the SQL Server is installed ;
  • In “Query Type” select “SQL Service”;
  • Click “Query” ;

- Query specific TCP port number:

  • In “Enter destination IP or FQDN to query” text box please type the FQDN of the machine where the SQL Server is installed;
  • In “Query Type” select “Manually input query ports”;
  • Then select SQL Server port it should be 4022 and in “Protocol” drop-down list select “TCP”;
  • Click “Query” ;

10. Execute in a Command Prompt opened with administrator rights, netstat –anob and analyze the ports that are used by SQL Server;

11. You can execute the following commands using sqlcmd to connect to SQL Server Instance:

- sqlcmd -S <ComputerName>\<InstanceName> -E

- sqlcmd -Stcp: <ComputerName>\<InstanceName>

- sqlcmd -Slpc: <ComputerName>\<InstanceName>

- sqlcmd -Stcp:127.0.0.1,portnumber

- sqlcmd -Snp: <ComputerName>\<InstanceName>

12. Enable the telnet client on the server using the following link : https://technet.microsoft.com/en-us/library/cc732046(v=ws.10).aspx

-  Execute the following command in a Command Prompt opened with administrator rights: telnet 127.0.0.1 1433

14. You can verify if there is any network issue by capturing a network trace when the issue is reproduced. You can download the autorun from : https://www.microsoft.com/en-us/download/details.aspx?id=6537  You just have to download the .exe file and with double click it starts the network trace collection;

15. To verify a possible network issue you can also ping the IP of the server;