Q & A: How Can I Test Client Connectivity to SQL Server?

Q: I’m trying to connect a networked client system to my local SQL Server instance and I can’t seem to get it to connect. How can I test if I’ve got good connectivity from the client to my SQL Server system?

A: There are a number of places where you can test. Of course, the first place to look with any network connectivity issue is to use the venerable ping utility to test if you have network connectivity to the server.

C:Ping

First, test using the SQL Server DNS instance name. If that succeeds you can move on to other tests. If it fails then ping your SQL Server system using its IP address. If that succeeds then you know that you have a DNS issue. If that fails then you know you have a network configuration issue. By default SQL Server uses port 1433 for client connectivity and any firewall between the client and the server must have that port open.

Next, you should check your SQL Server instance and make sure that it is supporting the network protocol that you are using. To do that open up SQL Server Configuration Manager. Next, expand the SQL Server Network Configuration and then click on Protocols for MSSQLSERVER (this name would be different if you are using a named instance).  In the Protocols pane check to be sure that either the TCP/IP or Named Pipes protocols are enabled. Most organization will be using the TCP/IP protocol.

After you’ve ensured that you have network connectivity to your SQL Server instance and that the correct network protocols are enabled on the SQL Server side there’s a quick way to test your connection to your SQL Server instance by creating a .udl (Universal Data Link) file on the client and then filling out the provider and connection properties. To create a .udl file simply create a text file and rename the extension from .txt to .udl. Then double click the file to open it. On the Provider tab you can select either the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client 11.0 provider. In this example I selected SQL Server Native Client 11.0 and then clicked the connection tab to display the connection dialog that you can see in Figure 1.

Figure 1 - Testing SQL Server connectivity with a UDL file

Fill out the prompt for Select or enter a server name with the server name or IP address of your SQL Server instance. Then fill out the Enter information to log on to the server with the required authentication information. Finally, click Test Connection