Not an original post by any means , but used this yesterday as was reminded that not many DBA’s seem to know about this.
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
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
; 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. :