#0288 – SQL Server – How To test database server connectivity without SSMS, SQLCMD or other applications


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
image
5. Provide the connection information in the “Connection” tab
image
6. You can define the connection timeout value in the “Advanced” tab
image
7. Other connection properties can be defined in the “All” tab
image
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=””


image


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.


image



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.

Advertisements

One thought on “#0288 – SQL Server – How To test database server connectivity without SSMS, SQLCMD or other applications

  1. dishdy

    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 = Nothing
    

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s