How to test connectivity from User Desktop to SQL Server using UDL and Cliconfg.exe to check OLEDB or ODBC Connectivity using TCP/IP and Named Pipes

This post is for junior DBA's on how to check connectivity to SQL Server from user desktop or application server. Frequently you will be getting call from application team or developers or users saying "We are getting SQL Server does not exist or Access Denied" error while creating DSN or running VBScript. So note down below steps which may help you.

How to open SQL Server Client Configuration Tool
1. Goto Start --> Run --> Type "cliconfg"

2. Now a window will open similair to below


3. Now click "TCP/IP" from left section and click "Enable>>"

4. Now click "Named Pipes" from left section and click "Enable>>"

5. One both are enabled, use down and up arrow to give priority to paricular protocol which will be used first when connecting to SQL Server. To Know more about these protcols, refer this msdn artcile.

6. For Example, If you choose that TCP/IP should be the first priority then "TCP/IP" should be first in the right section. After you have done this, then your Client Configuration will look like below


7. Now we need to create UDL file to test connectivity.

How to create ".udl" file to test connectivity
1. Using udl you can test connection and also you can get connection string.

2. Right click within any folder or desktop and select New and then text document.

3. Name it as test.udl. When it prompts you about whether you want to change the extension, click on Yes. Remember it should not have "txt" extension.

4. This will create a test.udl file for you and logo will also change.

5. Double click on the file and you will get this screen:


6. On the first tab, is the selection of the different OLEDB providers from which you can choose from. For this post, we will select the Microsoft OLEDB provider for SQL Server. One cal also choose SQL Native Client (SQL Server 2005), Oracle OLEDB Provider etc. depending upon the data source and the type of the application and the application needs.

7. Then click next. You will come to the connection tab.


On this tab, you can specify the connection parameters for connectivity to the data source. In the case of SQL Server when using the OLEDB Provider for SQL Server, you can choose between Windows and SQL Authentication and then choose your database as well. For the sake of generating the connection string with the UDL, you can choose to store the password and can test your connection. It is a quick and dirty way to test your connectivity as well to the data source.

8. On the next tab, you have some of the advanced settings like specifying a connection time out.


9. And on the last tab, one gets a listing of all the parameters for that connection string. One can choose to specify additional attributes over here like the ApplicationName, the WorkStationID etc. All this helps when troubleshooting where the connections are coming from since this information will then be reflected in the sysprocesses view on the server side.


10. Once you have tested the connection string and are happy with the settings, you can save it and then right click and open it with a notepad. For the UDL created above, you will get this entry:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=ReportServer;Data Source=(local)

As you can see above, the connection string is now available for you. This prevents the need for typing out the connection strings and running into typing errors - in addition, this is a quick and dirty way of verifying your connections as well.

Here when you have choosen that "TCP/IP" is having priority 1 while connecting to SQL Server and you wanted to check NamedPipe connectivity then no need to change the protocol priority, Just add below prefixes as needed.

What Prefix can be addded to force Named Pipe or TCP/IP or Shared Memory connectivity
1. For forcing connectivity through Named Pipes then use "np:{ServerName}". For example np:APPSDB
1. For forcing connectivity through TCP/IP then use "tcp:{ServerName}". For example tcp:APPSDB
1. For forcing connectivity through Shared Memory then use "lpc:{ServerName}". For example lpc:APPSDB (Shared Memory will work within server only)
This forcing can be done in Query Analyser, OSQL, SQLCMD, UDL....

Useful Links:
How you should set your firewall rules to be able to connect to a SQL Server 2005/2008 clustered instance