Tips and Tricks to fix SQL Connectivity Issues

  • Article

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:

  1. Get the SQL port details.
  2. Check if any aliases are present.
  3. Check if the protocols (TCP/Named Pipes/Shared memory) are enabled.
  4. 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:

  1. Repair SQL Browser by repairing the SQL instance
  2. 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.