Using .UDL Data Link OLEDB File to perform a basic SQL Connectivity test from App / Web Server

Not an original post by any means , but used this yesterday as was reminded that not many DBA’s seem to know about this.

Background

Quite often we are confronted with App / Web site / team where they are claiming connectivity problems to SQL Server. This technique allows you to do a quick connection test to SQL Server from a Wkstn / Server without the SQL client tools .

I like this method as you can generate the file yourself and send it to an App / Dev guy who say doesn’t know what he is doing and / or doesn’t have the same first language as you*

In our scenario we are trying to test connection to :

SQL Server Name** = KneeDeep, Instance = Clunge , TCPPort Listening on = 1678, DatabaseName=ShonkyDB

(** Remember if your app is using DNS Alias , use this here)

1. Create a new UDL file somewhere

by doing Start / Run / Notepad c:\somedir\somefile.udl . Obviously you will be prompted by the usual “Cannot find file / do you want to create message” . Say Yes and Save and Exit. Note it has to be a .udl file.

2. Open the file.

In Windows Explorer double click on the File . You should get the standard OLEDB Connection Dialogue.

Click on the Provider tab and change it driver to “Microsoft OLEDB Provider for SQL Server”.

Click either Next or back or the Connection tab.

3. Fill in your Server details as per below

image

N.B in my example I am using Windows Authentication – obviously this will run under the credentials of the user logged into the relevant Wkstn / Server. If you want to truly represent the application , you would need to do a RunAs with what ever credentials it uses.

4. Optionally fill in other details on “All” Tab

A nice side effect of this technique is that it will enumerate all of the Connection properties for the relevant driver here. i.e. you could change the Connection Timeout (default 15 seconds) or Application Name properties

5. Click “Test Connection” button on the Connection tab

and thats it

Click on OK will exit and save the file. Below is my example

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ShonkyDB;Data Source=KNEEDEEP\CLUNGE;Application Name=Shonky App Test

You can then send this to whoever to save on to App / Web server and get them to double click.

Notes and Caveats (Not complete)

– Your app might not be using SQL / OLEDB – i.e. could be .Net / SQLClient so this might not be a 100% test.

– In my example I’m using SQL OLEDB driver ,but obviously you could use this technique to connect to a legacy DB – like Oracle – that has an OLEDB Provider

– Also can help , as it enumerate the OLEDB drivers on the relevant Wkstn / Server.

– Could use ODBC to do this (ODBCAD32) but not as portable , bit manual.

– Can force TCP or named pipes in the usual way i.e. :

np:KneeDeep\Clunge

tcp:KneeDeep,1678

Advertisements

Leave a Reply

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