We know that there are lot of articles over the internet world with
this topic… But this article is from my perspective which has detailed
troubleshooting steps…
The main problem with troubleshooting connectivity issues with SQL Server is because the error message returned to client is almost generic and incase of login failures, the state is always 1
So let me give you step-by-step approach in troubleshooting connectivity issues with SQL Server.
Here is a flow of how connection to SQL Server is made:
1. Protocol used for making connections to SQL Server
Connecting is made using a protocol based on the “Client Protocols” order specified in your local box.
In the case of screenshot given above, the connection made to any SQL Server from this client machine will use “Shared Memory” protocol first and if that connection fails, it will try to use “TCP/IP” protocol and if that fails, connection request will be made using “Named Pipes” protocol. This is because I have all three protocols “Enabled” and I have specified the order in this way.
Shared Memory protocol can be used only for local server connections whereby SQL Server should be running in the same box where you are trying connect. This protocol will not help you to connect to a Clustered SQL Server instance because the IP address of SQL Server is different from your local node.
You can also force to use specific protocol using syntax:
TCP:SQLSRVRNAME\INSTANCE for forcing connections to use TCP/IP protocol
NP:SQLSRVNAME\INSTANCE for forcing connections to use Named Pipe protocol instead you can also use \\.\pipe\instancename\sql\query
LPC:SQLSRVNAME\INSTANCE for forcing connections to use Shared Memory protocol. LPC stands for Local Procedure Call.
If you force these protocols, then connectivity will happen only using specific protocol and if that fails, connection will fail without trying with further protocols. If you are connecting using a SQL Server alias created in the local client, then the protocol specified in the alias will only be used.
2. Connecitivity flow when using TCP/IP Protocol
While making connections using TCP/IP protocol, the client driver will check whether the instance is DEFAULT instance (MSSQLSERVER is the instance name for default instance) and if yes, the connection is made directly to port 1433 using TCP protocol to the target SQL Server Instance.
For ex: If SQLMOSS is the instance name and if I connect from SQL Server Management Studio to SQLMOSS, a connection request is sent to TCP port 1433 for the IP Address returned by DNS to the hostname SQLMOSS.
If this connection fails, a request is sent to port 1434 over UDP protocol and this is the port and protocol in which SQL Server Browser will be listening for incoming requests in the target SQL Server instance SQLMOSS. Now SQL Server Browser would have already read the port in which requested SQL Server Instance SQLMOSS\MSSQLSERVER is listening from the registry. So SQL Server Browser knows the TCP port is say 1488, it will return this information back to the requested client that SQLMOSS instance is listening on port 1488.
Now the client will reconnect to SQLMOSS instance using the TCP port 1488 and provided there is not firewall blocking, this connection will succeed.
There are other things like authentication and authorization to complete a login successfully.
3. Authentication:
It means that the username and password you specified in the connection is valid. If you are using Windows authentication and SQL Server server running with any of two mode “Mixed Mode” or “Windows-only” authentication, SQL Server will request Local Security Authority Subsystem Service (LSASS) to verify the credentials of the specified user. Again if the windows account is a local machine account, it is verified against local system security database and if it is a domain account, LSASS then requests Active Directory to authenticate the user.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article
If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.
4. Authorization:
Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.
So by now, hope you have the complete flow of how connectivity happens from a client to SQL Server.
Here are some Troubleshooting tips:
1. To make sure SQL Server Browser is able to start (Port 1434 is available for SQL Server Browser to grab), you can try start SQL Server Browser from command line :
C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c
You should see something like:
SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on ::[1434]
SQLBrowser is successfully listening on 0.0.0.0[1434]
[9088]: Waiting for next request…
This step will ensure that SQL Browser works fine. If you are able to start SQL Server Browser from command line and not as a service, check the service account configured for SQL Server Browser. To shutdown SQL Browser started from command line, you have to press CTRL + C.
2. If SQL Server Browser failed to start, run TCPView from http://technet.microsoft.com/en-us/sysinternals/bb897437 to make sure that no other process is already listening on 1434 UDP. For example if you have SQL Server 2000 already installed on the same node where SQL Server Browser fails to come online, it could be very well because SQL 2000 SSRP service already listening on 1434 and TCPView will help you to troubleshoot this.
3. If connectivity to SQL Server instance fails, first thing to check is whether it is a Clustered SQL Server instance or a Standalone.
If it is a clustered, try connecting from Active node of SQL Server to the SQL Server Instance. If this works then it could be a firewall blocking connections from passive node and other clients.
The main problem with troubleshooting connectivity issues with SQL Server is because the error message returned to client is almost generic and incase of login failures, the state is always 1
So let me give you step-by-step approach in troubleshooting connectivity issues with SQL Server.
Here is a flow of how connection to SQL Server is made:
1. Protocol used for making connections to SQL Server
Connecting is made using a protocol based on the “Client Protocols” order specified in your local box.
In the case of screenshot given above, the connection made to any SQL Server from this client machine will use “Shared Memory” protocol first and if that connection fails, it will try to use “TCP/IP” protocol and if that fails, connection request will be made using “Named Pipes” protocol. This is because I have all three protocols “Enabled” and I have specified the order in this way.
Shared Memory protocol can be used only for local server connections whereby SQL Server should be running in the same box where you are trying connect. This protocol will not help you to connect to a Clustered SQL Server instance because the IP address of SQL Server is different from your local node.
You can also force to use specific protocol using syntax:
TCP:SQLSRVRNAME\INSTANCE for forcing connections to use TCP/IP protocol
NP:SQLSRVNAME\INSTANCE for forcing connections to use Named Pipe protocol instead you can also use \\.\pipe\instancename\sql\query
LPC:SQLSRVNAME\INSTANCE for forcing connections to use Shared Memory protocol. LPC stands for Local Procedure Call.
If you force these protocols, then connectivity will happen only using specific protocol and if that fails, connection will fail without trying with further protocols. If you are connecting using a SQL Server alias created in the local client, then the protocol specified in the alias will only be used.
2. Connecitivity flow when using TCP/IP Protocol
While making connections using TCP/IP protocol, the client driver will check whether the instance is DEFAULT instance (MSSQLSERVER is the instance name for default instance) and if yes, the connection is made directly to port 1433 using TCP protocol to the target SQL Server Instance.
For ex: If SQLMOSS is the instance name and if I connect from SQL Server Management Studio to SQLMOSS, a connection request is sent to TCP port 1433 for the IP Address returned by DNS to the hostname SQLMOSS.
If this connection fails, a request is sent to port 1434 over UDP protocol and this is the port and protocol in which SQL Server Browser will be listening for incoming requests in the target SQL Server instance SQLMOSS. Now SQL Server Browser would have already read the port in which requested SQL Server Instance SQLMOSS\MSSQLSERVER is listening from the registry. So SQL Server Browser knows the TCP port is say 1488, it will return this information back to the requested client that SQLMOSS instance is listening on port 1488.
Now the client will reconnect to SQLMOSS instance using the TCP port 1488 and provided there is not firewall blocking, this connection will succeed.
There are other things like authentication and authorization to complete a login successfully.
3. Authentication:
It means that the username and password you specified in the connection is valid. If you are using Windows authentication and SQL Server server running with any of two mode “Mixed Mode” or “Windows-only” authentication, SQL Server will request Local Security Authority Subsystem Service (LSASS) to verify the credentials of the specified user. Again if the windows account is a local machine account, it is verified against local system security database and if it is a domain account, LSASS then requests Active Directory to authenticate the user.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article
If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.
4. Authorization:
Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.
So by now, hope you have the complete flow of how connectivity happens from a client to SQL Server.
Here are some Troubleshooting tips:
1. To make sure SQL Server Browser is able to start (Port 1434 is available for SQL Server Browser to grab), you can try start SQL Server Browser from command line :
C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c
You should see something like:
SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on ::[1434]
SQLBrowser is successfully listening on 0.0.0.0[1434]
[9088]: Waiting for next request…
This step will ensure that SQL Browser works fine. If you are able to start SQL Server Browser from command line and not as a service, check the service account configured for SQL Server Browser. To shutdown SQL Browser started from command line, you have to press CTRL + C.
2. If SQL Server Browser failed to start, run TCPView from http://technet.microsoft.com/en-us/sysinternals/bb897437 to make sure that no other process is already listening on 1434 UDP. For example if you have SQL Server 2000 already installed on the same node where SQL Server Browser fails to come online, it could be very well because SQL 2000 SSRP service already listening on 1434 and TCPView will help you to troubleshoot this.
3. If connectivity to SQL Server instance fails, first thing to check is whether it is a Clustered SQL Server instance or a Standalone.
If it is a clustered, try connecting from Active node of SQL Server to the SQL Server Instance. If this works then it could be a firewall blocking connections from passive node and other clients.
No comments:
Post a Comment