Tips and Tricks to fix SQL Connectivity Issues

项目
10/24/2014

Hello all,

Have you had any issues connecting to the SQL instance from an application or remote machine? Well, don’t worry because in this blog, I have:

Explained a few common scenarios;
Approached these connectivity issues; and
Included fixes for these issues.

To illustrate the SQL connectivity issues, I have installed SQL 2012 named instance "SQLTest" on Windows 2012 R2 server "SQLserver2012vm".

While trying to connect to the SQLTest instance from the remote SQL Server management Tool (SSMS), you may have experienced the following connectivity error several times.

Error Text:

Cannot connect to sqlserver2012vm\sqltest.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

Error Number: -1
Severity: 20
State: 0

Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj,Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64timerExpire, Boolean encrypt, Boolean trustServerCert, BooleanintegratedSecurity, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfoserverInfo, String newPassword, SecureString newSecurePassword, BooleanignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfoserverInfo, String newPassword, SecureString newSecurePassword, BooleanredirectedUserInstance, SqlConnectionString connectionOptions, SqlCredentialcredential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimertimeout, SqlConnectionString connectionOptions, SqlCredential credential,String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfoci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Configurations to check during connectivity issues:

Get the SQL port details.
Check if any aliases are present.
Check if the protocols (TCP/Named Pipes/Shared memory) are enabled.
Check if you are able to ping the SQL Server from the client server.

In the following example, SQL Server is listening on Port 63391. All the protocols are enabled.

To check if there are any aliases present or not and to check for the protocols on the client machine, “SQL Server Client Network Utility” tool can be used. To launch the tool, you can run one of the following executables:

32 bit: Start “C:\windows\syswow64\cliconfg.exe”
64 Bit: Start “C:\windows\system32\cliconfg.exe”

In the above screenshot, the (TCP/IP) protocol is enabled, and no client-side alias is present.

I am listing a few common scenarios because of which SQL connectivity issues can occur.

Scenario 1

Figure: The connectivity fails for Microsoft OLEDB Provider for SQL Server

Message Text: Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

UDL test helps testing SQL connectivity. Refer to the following blog to get details on how to setup UDL test: https://blogs.msdn.com/b/farukcelik/archive/2007/12/31/basics-first-udl-test.aspx.

To ensure that the issue is not provider specific, check with SQL Server Native Client 11.0 (SQL Management Studio uses SQL Native Client provider).

Message Text:

SQL Server Native Client Data Link Error
---------------------------
[Microsoft SQL Server Native Client 10.0]: Login timeout expired
[Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[Microsoft SQL Server Native Client 10.0]: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Windows provides the “Telnet” feature to allow communication with remote machine using the telnet protocol. The Telnet client is not installed by default though. So, use the following blog to enable the Telnet client:

https://technet.microsoft.com/en-us/library/cc771275(v=WS.10).aspx.

In this scenario, Telnet test fails.

From the Telnet test, it’s evident that the TCP port 63391 is blocked. To check if the SQL Browser UDP port is blocked or not, we can use PortQryUI. The PortQryUI tool can be downloaded from: https://www.microsoft.com/en-in/download/details.aspx?id=24009.

From the following PortQryUI output, it’s evident that SQL Browser UDP port is blocked as well.

If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked. To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. For more information, follow the blog: https://msdn.microsoft.com/en-IN/library/cc646023(v=sql.110).aspx.

From the Telnet test and PortQryUI test, it’s evident that the SQL Port and Browser Port is unreachable. So to address the issue, I created two inbound rules on Windows Firewall on SQL Server to allow the connection for:

SQL TCP Port: 63391
SQL Browser UDP Port: 1434

After allowing access to the SQL instance, connectivity works. If the port assigned to SQL Server is dynamic, upon SQL Service restart, the port might change. Therefore, you can consider adding sqlservr.exe in program exclusion list.

Scenario 2

Ok, we configured Windows Firewall to allow access to SQL Server and Browser Port. Connectivity is still not working! Let’s explore few other options.

Let us try to connect by forcing the TCP port on which SQL listens on. Test connection succeeded!

Why so? What happens when we force the TCP directly? This is where we need to know how SQL Browser Service works.

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

To get additional information on SQL Browser, follow this: https://technet.microsoft.com/en-us/library/ms165724(v=SQL.90).aspx.

From SQL Server Configuration Manager, SQL Browser Service is stopped.

On starting SQL Browser Service, connectivity works. This was easy, wasn’t it?

Scenario 3

Alright, SQL Browser Service is started, and all the basic checks are done. You are still facing the connectivity issue.

Let’s try forcing the port again to check the connectivity. Viola, connectivity works. But now, we know that the issue is because SQL Browser is unable to resolve the instance name and the port. Even though SQL Browser Service is started, the issue persists. Interesting!

To address the issue, let’s start the SQL Browser Service in console mode using –c switch:

Then try connecting to the SQL instance either via UDL test/SSMS or the application.

SQL Browser Service is up and running, but getting the error “Failed starting SSRP redirection services”. SQL Server Browser listens on a UDP port and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP).

To check if SSRPlistener is enabled or not, check: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser\SSRPListener.

In our case, it is disabled (Value is 0). So SQL browser service is unable to discover the instance.

To fix the issue, change the value of SSRPlistener to 1. (Make sure that you take a backup of registry before modifying it). Post enabling this property, when you start SQL Browser Service, it should be like the screenshot below:

If you still see any connectivity issues post enabling the SSRPListener registry entry, the options available are:

Repair SQL Browser by repairing the SQL instance
Install a new SQL instance which installs SQL browser and then uninstall just the database engine features (If the first option is not possible)

I hope the guidelines in the blog helps.

Reference articles:

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | SQL Server Support Team | CSS – Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.

Comments

Anonymous
October 23, 2014
Crisp and clear!
Anonymous
October 23, 2014
Thank you for sharing the scenarios. These are really helpful.
Anonymous
October 24, 2014
Very well describe.
Anonymous
October 25, 2014
Thank you for the feedback.
Anonymous
July 15, 2015
Awesome!! Great tips for troubleshooting SQL connections. This help me immensely. It turns out the browser service was not running on the SQL Server. Thank you!!!
Anonymous
July 20, 2015
Thank you Steve.
Anonymous
October 19, 2015
I had the same problem but I had already setup open ports on 1433 on the firewall. The solution for me was simple on SQL 2012: Goto "Sql Server Connection Manager" Drop down SQL server network configuration Protocols for MSSQLSERVER > Right click TCIP/IP on every "TCP Port" entered 1433 or whatever port you wish done
Anonymous
December 03, 2015
Thanks for sharing knowledgeable Scenarios.
Anonymous
May 24, 2016
This is very helpful. Thank you for posting this.Just needed to know in case we are getting the below error:"Test Connection failed because of an error in initializing provider. Login timeout expired."
- Anonymous
  May 24, 2016
  Hi Akash,Try forcing the connection using np and tcp and check if the connectivity works. Also try with different providers like SQL Native client, ODBC to rule out provider issues. Check if you have any aliases configured. Also, the error message you posted is very generic. You could try some of the steps mentioned in the blog.
Anonymous
July 28, 2016
Thank you very much for sharing this knowledge.I was consuming a lot of hours till I read this blog.The Alias on 32 en 64 bit cliconfg were causing a lot of problems.
- Anonymous
  August 08, 2016
  Thank you Andre for sharing the feedback. Glad this blog post helped to resolve the connectivity issue.
Anonymous
August 12, 2016
Thanks a lot. Came to know about cliConfig.exe and revealed the issue.
Anonymous
December 23, 2016
Hi, I am facing following error.Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 20003 Adaptive Server connection timed out [20003] (severity 6) [(null)] (SQL: select top 100 [DateAndTime], [TagName], .I thought that I am fetching 5000 records so issue is arriving but this is not so, even I am trying to fetch 100 records, same issue is arriving. I am trying to connect from linux server to windows sql server. I am able to telnet it on given port properly. Can you give me any idea.

通过

Tips and Tricks to fix SQL Connectivity Issues

Comments

其他资源