Intro:
DBA/Application teams frequently experience SQL Connectivity issues while connecting from apps during routine SQL Instance activities. In this essay, I’ll go over a couple checklist techniques for resolving SQL Connectivity issues.
A client must first authenticate and then authorize itself before it can connect to SQL Server. If the issue is with authorization, the client will be able to connect physically to SQL Server, but the issue will arise during authorization, leading to things like login errors because of permission problems, etc. The first place to look for solutions if the issue is due to a lack of authorization should be the SQL Server Error log.
A crucial first step in fixing SQL Connectivity problems is identifying the problematic component: Concerns about connectivity can be divided into the following groups:
- There is a network issue
- A configuration issue with SQL Server.
- There is a firewall issue
- A driver’s licence issue for the client
- A setting issue with the application.
- Problems with logon and authentication
CHECKLIST STEPS:
1. Obtain details about the servers involved in the connection.
- How many servers are participating in all?
- Is this a thick-client or web-based application?
- Does the client connect to the application server, which connects to SQL Server, and vice versa?
- Does only one client box have a connectivity issue, or do multiple clients have trouble connecting?
- Make a list of the names and IP addresses of each server.
- Verify whether SQL Server is reachable. Using network ping to check whether the server can be reached is an easy test.
Ping the Server If Ping is unable to detect the SQL Server, it signifies that the client cannot find the SQL Server system, and we will require the assistance of the Network team to further troubleshoot this. - Verify whether SQL Server is reachable. Using network ping to check whether the server can be reached is an easy test.
- On SQL Server machines, local networking is accomplished using the shared memory protocol. The next step is to confirm the protocols used by SQL Server and confirm that TCP/IP and Named Pipes are set up to accept remote connections:
- Type: to check if the client is able to connect to the SQL Server port. Using the telnet application, we can check to determine if the SQL Server port is accepting incoming connections.
For example in the case: For instance, if the client box is unable to connect to the port on which the SQL Server is listening and the host name of the SQL Server is SQLJumpbox and the port on which SQL listening is 1433 telnet SQLJumpbox 1433, and check if you are receiving any error:
- Telnet failure is a definite sign that a device is blocking the port if it also fails (firewall). To check if a port is blocked or not, use the tool portqueryUI.
Visit this link to obtain PortqueryUI: https://www.microsoft.com/enin/download/details.aspx?id=24009
If Filtered appears in the output, the port is blocked.
The next step is to inquire about opening the port with the Windows/networking team. Make sure that inbound and outbound rules are created at the operating system level to permit the connection for the SQL port.
- After the port has been authorized, restart the PortqueryUI program and verify that the status is LISTENING.
The only purpose of the Telnet utility is to check whether a TCP port is blocked. However, it is possible to determine whether a port is blocked for both TCP and UDP using the PortQueryUI tool.
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
For example:
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.
9. 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.
Once the default database and SQL Server name have been entered, click test connection.
Incorrect connection string setup for the application is indicated if the connection is made using udl.
Check to see if you can reproduce the connectivity issue by simulating it with the same settings as the connection string.
If the connectivity works from UDL, ask the application team if the connection string may be replaced with the one provided by UDL. [Open the udl file in notepad to obtain the dynamically generated connection string.]
- Check to see if an alternative provider can be used to connect to SQL if the issue is being caused by the client provider. Choose a few SQL service providers from the udl file, then check the connections.
Consider using the SQL native client if the issue only affects Microsoft OLE DB for SQL Server. - Create a user DSN with the ODBC Data Source Administrator tool (odbcad32) and verify connectivity if the data source name, also known as DSN, is used in the application connection string.
ODBC administrator tool launch in 32-bit mode: C:\Windows\SysWOW64\odbcad32.exe
- Look for Last SQL Server connect in the registry location below on the client PC. HKEY LOCAL MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
Clear and delete the lastConnect entries for the specific SQL Server in question. [Make certain you have a registry backup before making any changes.]
- Check for client-side aliases using the SQL Server Client network utility:
Activate the tool by:
Look through the orphaned alias entries on the alias tab. Make a backup of any aliases that have been set up for the SQL Server instance that we are troubleshooting and remove the entries.
Follow these instructions to launch the SQL Client Configuration Tool in 32-bit mode. C:\Windows\SysWOW64\cliconfg.exe
14.See whether you can test connectivity using named pipes and other protocols, such as TCP.
Ask the application team if the connection string can be altered to force the protocol in the data source field if connectivity works after forcing the protocol.
Follow these steps to check if the SQL connection is using TCP/named pipes: run the following command:select session_id, net_transport from sys.dm_exec_connections
If the connection string cannot be changed and connectivity is enforced by a protocol, see if a client side alias may be created.
Example: If the connectivity only supports the Named pipes protocol, create a named pipe alias using the SQL Server client network utility.15.You can test whether you can connect to the instance by forcing the SQL port.
If you can connect while manually forcing the port, SQL Browser is broken. The task of matching the SQL Server port and instance name falls to SQL Browser. Then, to resolve browser issues, confirm whether or not SQL UDP browser port 1434 is blocked.
- Instead of using the SQL Server’s hostname, check connectivity by entering the server’s IP address. For instance:
If SQL listens on port 5223 and the instance hosting SQL has an IP address of 10.16.17.18, then:
If connectivity is achieved by hardcoding the IP address in the Server name, add a host file entry with the Server name and the IP address mapping to avoid DNS name resolution and to involve the DNS team in case name resolution problems arise.
- The next step is to use the Network Monitor tool to gather network traces if the connectivity issue still exists after trying the aforementioned solutions.
I sincerely hope the steps outlined above can help you fix SQL Connectivity issues.