DBA/Application teams regularly encounter SQL Connectivity difficulties while connecting from apps during daily operations with SQL Instances. I’ll go over a few checklist procedures for troubleshooting SQL Connectivity difficulties in this article.
When a client connects to SQL Server, it must first authenticate and then authorize itself. If the problem is due to authorization, the client will be able to establish a physical connection with SQL Server, but the problem will occur while authorizing with SQL Server, such as Login errors due to permission issues, and so on. If the problem is caused by a lack of authorization, the SQL Server Error log should be the first place to go for answers.
Isolating which component is causing the problem is a critical step in resolving SQL Connectivity issues: We can categorize connectivity concerns into the following categories:
A problem with the network
A problem with SQL Server’s settings.
A problem with the firewall
An issue with the client’s driver
A problem with the application’s settings.
Issues with authentication and logon.
The following are some of the most common error messages generated by the programme when connecting to SQL Server:
Error 4: Error is Similar to this not exactly the same error
[Microsoft] [SQL Native Client 10.0] TCP Provider: The remote server forcibly closes the existing connection.
1.Gather information about the servers participating in the connection.
What is the total number of servers involved?
Is this a web-based or thick-client application?
Is the client establishing a connection to the application server, which then establishes a connection to SQL Server?
Is the connectivity problem limited to one client box, or are several clients unable to connect?
Make a list of all the servers’ names and IP addresses.
2. Check to see if SQL Server is accessible. A simple test is to use network ping to see if the server can be reached.
Ping <SQLServername> or Ping <IP address of SQL Server>If Ping fails to discover the SQL Server box, it means the client is unable to locate the SQL Server machine, and we will need the help of the Network team to debug this further.
3.Check to see if SQL Server is accessible. A simple test is to use network ping to see if the server can be reached.
4.Shared memory protocol is used for local networking on SQL Server boxes. The next step is to verify SQL Server’s protocols and make sure that TCP/IP and Named Pipes are enabled for remote connections:
5. To see if the client can connect to the SQL Server port, type: To see if the SQL Server port is accepting inbound connections, we can use the telnet programme.
For example: For instance, if the SQL Server’s host name is SQLprod and the port on which SQL is listening is 1433 telnet sqlprod 1433, and the client box is unable to contact the port on which SQL is listening, we will receive the following error:
6. If telnet also fails, this is a clear indicator that the port is being blocked by a device (firewall). The utility portqueryUI can be used to see if a port is blocked or not.
Download the PortqueryUI utility here: https://www.microsoft.com/enin/download/details.aspx?id=24009
If you see Filtered in the output, that means the port is blocked.
For example, if the SQL hostname is aoindia and SQL Port is 1433:
The next step is to check with the Windows/networking team to see if the port can be opened. At the operating system level, ensure that inbound and outbound rules are created to allow the connection for SQL port.
7. Run the PortqueryUI software again after the port has been allowed, and make sure the status is LISTENING.
The Telnet tool can only be used to see if a TCP port is blocked. However, the PortQueryUI application can be used to check if a port is banned for both TCP and UDP.
8. Gather the application’s connection string information. If the application is web-based, the connection string information is usually found in the web.config file. Collect the following information: a. Data Source b. Initial Catalog c. Integrated Security d. Provider a. Data Source b. Initial Catalog c. Integrated Security d. Provider
Provider=SQLNCLI10; Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes;
Few important details to be studied from connection string are: Type of authentication used (Windows/SQL), provider used (OLE DB or ODBC), Data Source field.
A simple test to see if the problem is caused by the application is to connect to the SQL instance from the client box using SQL management studio. However, the SSMS tool may not be installed on the client PCs in the majority of cases. We can construct a universal data link tool (UDL) to assess connectivity in this situation.
Create a text document on the client system with the extension.udl and rename it.
For example: test.udl
Select the appropriate provider list in the udl file based on the application’s provider (Microsoft OLE DB for SQL Server) or SQL native Client provider and click next.
Click on test connection after entering the SQL Server name and default database:
If the connection is established via udl, it means that the application’s connection string configuration is incorrect.
Simulate the connectivity issue with the same settings as the connection string and see if you can duplicate the problem.
Check with the application team to see if the connection string can be replaced with the one provided by UDL if the connectivity works from UDL. [To acquire the dynamically produced connection string, open the udl file in notepad.]
10. If the problem is caused by the client provider, see if a different provider can be used to connect to SQL. Select several SQL providers from the udl file and verify whether or not connectivity works.
For example, If the problem is only with Microsoft OLE DB for SQL Server, see if the SQL native client can help.
11. If the data source name (commonly known as DSN) is used in the application connection string, create a user DSN with the ODBC Data Source Administrator tool (odbcad32) and test connectivity.
To launch ODBC administrator tool in 32-bit mode: C:\Windows\SysWOW64\odbcad32.exe
12. On the client PC, go to the registry location below and look for Last SQL Server connect HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
Clear the entries for the SQL Server in question specified for lastConnect and delete them. [Before making any changes, make sure you have a registry backup.]
13. Use the SQL Server Client network utility to see if there are any client-side aliases:
To launch the tool:
Examine the alias tab for any orphaned alias entries. If there are any aliases established for the SQL Server instance we’re troubleshooting, make a backup of them and delete the entries.
To run the SQL Client Configuration Tool in 32-bit mode, follow these steps.: C:\Windows\SysWOW64\cliconfg.exe
14. Check to see if you can test connectivity using protocols like TCP and Named Pipes.
If the connectivity works after forcing the protocol, ask the application team if the connection string can be changed to force the protocol in the data source field.
To see if the SQL connection is using TCP/named pipes, do the following: run the following query:
select session_id, net_transport from sys.dm_exec_connections
Check if a client side alias can be formed if no changes to the connection string can be made and the connectivity works by imposing a protocol.
Example: Create a named pipe alias using SQL Server client network utility if the connectivity only works with Named pipes protocol.
15. By forcing the SQL port, you can see if you can connect to the instance. For example:
If you can connect while forcing the port number, there’s a problem with SQL Browser. SQL Browser is in charge of matching the instance name to the SQL Server port. Then, to troubleshoot browser difficulties, verify if SQL UDP browser port 1434 is banned or not.
16. Check for connectivity by giving the SQL Server’s IP address rather than the hostname. For example:
If the instance hosting SQL has an IP address of 10.16.17.18 and SQL listens on port 5223, then:
Create a host file entry with the Server name and the IP address mapping to prevent DNS name resolution and involve the DNS team for name resolution issues if the connectivity works by hardcoding the IP address in the Server name.
17. If the connectivity problem persists after doing the aforementioned methods, the next step is to collect network traces using the Network Monitor programme.
I hope the techniques outlined above may assist you in resolving SQL Connectivity difficulties.