Quick Way to Troubleshoot SQL Server ADO Connections

Mike Wolfe -

Troubleshooting a problem connecting to SQL Server from an end-user's computer can be a real challenge.

One quick way to check for connection problems is to open an empty Universal Data Link configuration file.  Here are the step-by-step instructions:

  1. Open File Explorer
  2. Go to the View tab in the File Explorer ribbon
  3. Check the box next to [√] File name extensions in the Show/hide group
  4. Press [Alt] + [D] to highlight the address bar
  5. Type %temp% then press [Enter] to open the user's Temp folder
  6. Go to the Home tab
  7. Click "New item" then choose "Text Document"
  8. Rename the newly created file to Test.udl
  9. Click [Yes] when Windows warns you about changing the file name extension
  10. Double-click on the Test.udl file you just created

Windows will open the Data Link Properties window:

Enter the relevant information to connect to SQL Server.  In the example above, I entered:

  1. . : a period which is a shortcut for the current computer name (I'm connecting to a development copy of SQL Server that is running locally)
  2. (o) Use Windows NT Integrated security: select this option if you are using Active Directory Windows authentication, otherwise enter the SQL Server user name and password
  3. (o) Select the database on the server NoLongerSet: this is the name of the database on my dev server; the dropdown list will populate as long as options 1 and 2 above are valid

Finally, press [Test Connection] to make sure everything is working.  If all goes according to plan, you should see the following message:

Otherwise, you will receive some sort of (hopefully) meaningful error message, such as this...

In my case, there really is no database server named "MyDbServer." However, this error message is the same whether the server really does not exist or we don't have permission to see it or the port is blocked by a firewall or the network connection is down or ... or ... or ....

...or this...

Whoops! I forgot to enter my SQL Server user name and password.

...or this...

There is no user named "john.doe" in my database. This error message is the same whether "john.doe" is missing entirely or the user exists and the password is wrong. If the error message was different for those two scenarios it would provide information to a potential attacker.

...or this...

There is no database named "MyMissingDatabase" on my server. However, I think this message also appears if there was such a database but I did not have permission to view it.

Provider Tab

Sometimes the problem is that the OLE DB provider you are using to connect on your development computer does not exist on the end user's computer.

The most convenient way to check that is to jump over to the "Provider" tab.  This will list all of the OLE DB providers available to the user on the current machine.  If the provider you are expecting is not listed, you will need to install it for the user or switch to a different OLE DB provider.

Your Tricks

What about you?  What tricks do you use to troubleshoot SQL Server connections on an end user's computer?  Let us know in the comments below.