Troubleshooting SQL Server Connections and Authentications

1.      When to use which protocol?


·        Shared memory works only for both server and client on the same machine. Although it cannot be used as in most databases, it can be used for diagnosing for problems if they are related to network or SQL Server itself.

·        Named pipe runs on the top of TCP or other network protocols and it must pass Windows authentication to visit the shared IPC$ on the server. If both client and SQL Server are in the same LAN, and it can pass Windows authentication, named pipe can be used.

·        If client and server in a WAN, and hard to pass Windows authentication, it is better to use TCP/IP. In general, TCP/IP is recommended.

2.      SQL Server Browser

a.     Background:


Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.


b.     How SQL Server Browser is better than SSRP? – use minimum Account Privileges

SQL Server Browser listens on a UDP port and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP). SQL Server Browser should be run in the security context of a low privileged user to minimize exposure to a malicious attack. The logon account can be changed by using the SQL Server Configuration Manager. The minimum user rights for SQL Server Browser are the following:

·        Deny access to this computer from the network

·        Deny logon locally

·        Deny Log on as a batch job

·        Deny Log On Through Terminal Services

·        Log on as a service

·        Read and write the SQL Server registry keys related to network communication (ports and pipes)


c.      Purpose:


The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

·        Browsing a list of available servers

·        Connecting to the correct server instance

·        Connecting to dedicated administrator connection (DAC) endpoints


d.     How SQL Server Browser Works?


When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.


Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.


When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.


e.     What if SQL Server is not running?

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

·       Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

·       Any component that generates or passes server\instance information that could later be used by other components to reconnect.

·       Connecting to a named instance without providing the port number or pipe.

·       DAC to a named instance or the default instance if not using TCP/IP port 1433.

·       The OLAP redirector service.

·       Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.


If you are using SQL Server in a client-server scenario (for example, when your application is accessing SQL Server across a network), if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

·       You must update and maintain client application code to ensure it is connecting to the proper port.

·       The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.


f.       Connection Problems related to SLQ Server Browser?

·      If you see “SQL Server doesn’t exist or access denied”, the UDP 1434 may be prohibited. You need to open the port on the firewall and gateway. You may also try a different port or piped name to see if SQL Server can be connected.

·      SQL Server Browser itself rarely causes problems.

·      You need to make sure that the service account for SQL Server Browser has the permission to Read and write the SQL Server registry keys related to network communication (ports and pipes) (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib)

3.      Which Driver Library should a client use?

a.      MDAC

i.                  APIs for non .NET applications such as old ODBC and OLEDB

ii.                Cannot use new features since SQL Server 2005, thus, for back compatibility only.

b.      SQL Server Native Client

i.                  APIs for .NET applications introduced in SQL Server 2005, supporting new features

ii.                Different versions of the native client can co-exist

c.      JDBC – for Java applications

4.      What is the order of a client choosing the connection protocol if multiple protocols are available?

a.      Specified in the connection string

i.        Option 1: Server = [protocol:] Server[,port], where protocol can be tcp (TCP/IP), lpc (shared memory), or np (named pipes)


Example 1: Server=np:myServer\myInstance

Example 2: if you specify the connection server is np:server_name in SSMS, then SSMS will only use this named pipe for the connection.


ii.      Option 2: Network = dbmssocn (for TCP/IP) or dbnmpntw (for Named Pipe)


Only one of the methods can be used. If the connection failed, an error is reported.

b.      Alias – If the connection failed, an error is reported.

c.      The “LastConnect” value in the registry – if not successful, will try the methods below

d.      If no protocol is specified in method 1, server name does not appear in the alias or ‘LastConnect”, then the client chooses the protocol based on the precedence of protocols, SQL Server Browser will acquire the port or piped name dynamically.

5.      Troubleshooting the connection problem with named pipe?

a.      How named pipe works?

i.                  Named pipe is based on the shared ICP$ on the server.

ii.                SQL Server first create a named pipe on the server and monitor it, then the client connects to this pipe.

iii.               Named pipe is named using UNC: \\server\Pipe\path_name such as \\.\pipe\sql\query for the default instance or \\.Pipe\MSSQL$instancename\sql\query for a named instance.

iv.               How to know if SQL Server monitor the named pipe? – check the errorlog

b.     Troubleshooting named pipe connection problems

i.                  Use SQL Server Client Connection Utility to check the setup of the named pipe and make sure SQL Server monitors it

ii.                Make sure named pipe is enabled, and make sure the connected one or the alias is the one SQL Server is monitoring

iii.               Make sure network connection is good such as you can PING the IP for SQL Server or the name of the SQL Server

iv.               Make sure the client can pass through the Windows authentication for SQL Server, If there is an error, it probably is insufficient permission on the server.


Net view \\servername

Net use \\servername \IPC$


v.                Make sure the login has the proper permission on SQL Server.

c.      Examples of troubleshooting

i.                  [named pipes] SQL Server does not exist or access denied

1.      Cause: the client has not found the named pipe.

2.      Solution: (1) make sure network connection is through (PING), (2) SQL Server is started, (3) the setup of named pipe matches on the server and client.

ii.                Login failed for user ‘NULL’ or Login failed for user anonymous

1.      Cause: the named pipe does not have permission on the server.

2.      Solution: (1) make sure the piped name has the right on IPC$, you can use Net use \\servername \IPC$ to check.

iii.               Login failed for user ‘xxx’

1.      Cause: insufficient permission

2.      Solution: give the user sufficient permission

d.      Tips

i.                  Use the following script to check the protocol used – LPC=local system

Select Net_library, hostname, program_name, nt_domain, nt_username,loginame

FROM master..sysprocesses

WHERE spid>50


ii.                Use ODBCAD32.exe to test ODBC sources

6.      Troubleshooting the connection problem with TCP/IP?

a.      Two key elements in TCP

i.                  IP address – dynamically assigned

ii.                Port number: any unused one, >5000 or <1024 is recommended

b.      How to know which ports are used by SQL Server?


Netstat –an in DOS


c.      Steps to troubleshoot

i.                  Make sure SQL Server is listening on TCP/IP – errorlog

ii.                Make sure the TCP/IP port the server is listening is the same as that set up on the client machine or the alias

iii.               Examine the network connectivity by PING the IP address and Name of SQL Server

1.      Make sure you can PING SQL Server IP address

a.      How to obtain the IP address for SQL Server?

If you're running SQL Server 2005 or above you can use:



from sys.dm_exec_connections

where session_id=@@SPId;


client_net_address         local_net_address  

Limitation: You only get IP addresses if you're connective via TCP/IP. 
If you're local and using Shared Memory then those attributes don't exist. If you turn off Shared Memory (or any protocols except for TCP/IP) via Server Configuration Manager you will always get IP address for any connection using this technique.


b.      How to PING?



c.      The client and the server should be on the same network. You can run ipconfig /all on both the client and the server to check. Or run the above script in SSMS.


2.      Make sure you can PING SQL Server name

a.      PING XU_XPS

b.      If there is a problem, try to add the IP/name pair to the hosts file under c:\windows\system32\drivers\etc

3.      Use Telnet to check the port that SQL Server is listening on. (Telnet was disable in Windows 8.1 by default, you need to turn it on through Windows features)


Telnet 1433


d.      TIPS

i.                  How to know which program is using port 1433 or 1434?

1.      Download and install ortQry.exe

2.      Run the following query:

Portqry-n localhost –p TCP –e 1433

Portqry-n localhost –p UDP –e 1434


ii.                Which protocol is used?

Select Net_library, hostname, program_name, nt_domain, nt_username,loginame

FROM master..sysprocesses

WHERE spid>50


iii.               How to log into SQL Server with a specific port?


Use TCP: ServerName, 1433 instead of ServerName on the SSMS Connection UI


e.      Troubleshooting General Network Errors

i.                  SQL Server service has been found, but something is wrong somewhere in the process.

ii.                Check the error message to diagnose where the error occurred.

iii.               Many possible reasons:

1.      Too much workload on the server

2.      No connection pool available on a busy server

3.      Network traffic problems between the server and some clients

4.      Windows system for its own protection, may refuse some large/busy network requests to SQL Server. Some errors are on the server, some on the clients.

5.      Inappropriate system setting

a.      Security harden such as modification of TCP parameters in (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters)

b.      Some SQL Server setting may cause the GNEs such as sp_configure ‘priority boost’, [0-7] and sp_configure ‘lightweight pooling’, [0-1] - 1 causes SQL Server to switch to fiber mode scheduling. The default value for this option is 0.

6.      AV and Firewall – positive false

7.      Network routers or firmware causes the GNEs

iv.               Recommendations

1.      Communicate with Network Administrators

2.      Check SQL Server and client health

a.     Check windows error log

b.     CPU usage cannot be 100% for a long time

c.      No problems on cache and RAM

d.     No serious error in errorlog such as AV, RAM, disk, or 17883/17884

e.     SQL Server cannot have blocks over 100.

f.       SQL Server should not have many runnable and running processes

Select *

FROM master..sysprocesses

WHERE spid>50


3.      Best practices

a.     Set EnableRSS in HKey to 0

b.     Set DisableTaskOff to 1

c.      Disable TCP Chimney

d.     Set TCPA in HKey to 0

e.     Restart the machine

f.       Do not change priority boost and lightweight pooling

g.     Do not change the default values under theHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

h.     Do not use teaming network card

i.       Make sure the automatic closing function for the idle status of the network equipment is disabled.

j.       Temporarily disable AV and Firewall

k.      Make the server and client machine physically closer if possible

l.       Change TCP/IP to a named pipe or vice versa

7.      Authentication and Delegation

a.      Typical errors:

i.                  Illegal login – Error 18456




State 2 and State 5

Invalid userid

State 6

Attempt to use a Windows login name with SQL Authentication

State 7

Login disabled and password mismatch

State 8

Password mismatch

State 9

Invalid password

State 11 and State 12    

Valid login but server access failure

State 13

SQL Server service paused

State 18

Change password required


ii.                Authentication error – rarely on SQL Server login, mostly on Windows Login

b.      SQL Server Authentication

i.                  SQL Server does the validation and rarely has problems

ii.                SQL Server 2005/Windows Server 2003+ make the password management almost as complex as the Windows system.

c.      Windows authentication

i.                  Two techniques: NTLM and Kerberos

ii.                Which one to use is not determined by the client, neither the server. Windows determines which technique to use based on the value returned from the authentication API.

iii.               NTLM is a simple one and does on hop authentication between a client and a server.

iv.               Double hop or multi hop authentication needs Kerberos authentication, which is much complicated than NTLM

d.      Delegation

i.                  When authenticating many machines, delegation may be needed in the middle machine.

ii.                The setup of delegation is more than Kerberos.