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.
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:
; 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.
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.
- Testing Connection Strings using SSMS [Link]
Until we meet next time,