I am sure that many of my readers will be able to co-relate with the situation in today’s post.
We recently faced a situation at the office where one of our application servers was unable to communicate to the database server – the servers could “ping” each other just fine, but the SQL Server was inaccessible. While there were indications of connectivity being an issue, we did not have any definitive proof. The IT administrator continued to maintain that the server has been configured in the same way as all our other application servers were. We were therefore required to come up with a mechanism to test connectivity from the application server to the database server without the use of SSMS, SQLCMD (being the application server, it didn’t have any SQL client components installed) or any other applications. We had to use something that is shipped with the operating system.
Universal Data Link (UDL) file
Creation and Basic Configuration
The Microsoft Data Access Components (MDAC) provide the Data Link Properties dialog box as the common user interface for specifying connection information to a data provider on Windows 2000 and later operating systems. One can use the Data Link Properties dialog box to save connection information in a universal data link (.udl) file. We will be using this *.udl file to configure the most common connection string parameters and then help us test the connectivity to our database server.
| 1. On the desktop, or any other folder, right-click and create a new Text document |
| 2. Rename the newly created file to “Test Database Connectivity.udl” (you can have the file name of your choice, the extension has to be .udl) |
| 3. Double-click the file to open the “Data Link Properties” dialog box |
4. In the “Provider” tab, choose the appropriate provider in use by the application  |
5. Provide the connection information in the “Connection” tab  |
6. You can define the connection timeout value in the “Advanced” tab  |
7. Other connection properties can be defined in the “All” tab  |
| 8. Click “OK” to save the data link information into the UDL file |
Advanced Editing
Under the covers, the UDL file is nothing but a connection string stored in plain text. For seasoned administrators, the UDL file can therefore be opened in NOTEPAD where advanced connection string parameters can be defined by simply editing the connection string.
Shown below is the information from the UDL file we just defined above:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=””;Initial Catalog=AdventureWorks2012;Data Source=W8SQLSERVERSQL2K12;Application Name=My Test Application;Initial File Name=””;Server SPN=””
Testing connectivity using the UDL file
Once the connection string definition in the UDL file has been finalized, one needs to double-click on the UDL file in the Windows explorer again to launch the “Data Link Properties” window.
To test the connection, we need to navigate out to the “Connection” tab and simply click “Test Connection” – If the connection succeeds, the underlying hardware, firewall and other configuration is as required.
Summary
The UDL file method of testing connection strings is not new. Yet, it is often forgotten as being one of the most easiest ways to test SQL Server connectivity. I would like to hear from you regarding the methods you use for testing connection strings when no SSMS, SQLCMD or other tools/applications are available at your disposal.
Further Reading:
- Testing Connection Strings using SSMS [Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
On a Windows 2000 server, SQL Server Native Client 10.0 and 11.0 come pre-installed?
In any case, for the longest time I have used this simple VB Script.
Let me know if this works on a Windows 2000 server.
Dim objConn Set objConn = CreateObject("ADODB.Connection") On Error Resume Next objConn.Open("PROVIDER=SQLOLEDB;DATA SOURCE=SERVERNAMESQLINSTANCE;DATABASE=MYDATABASE;USER ID=sa;PASSWORD=sa") If Err.Number 0 Then WScript.Echo "Connection failed" Else WScript.Echo "Connection successul" End IF objConn.Close Set objConn = NothingLikeLike