A co-worker showed me a really neat trick the other day. We deal with a lot of connection problems and one of the first places I look is the connection string. Now I’ve gotten pretty good at it over the years and more often than not I can point to problems. However, those other times can be a real pain. There is a great reference for connection strings but even it doesn’t always help. So what was the trick?

It turns out that udl files are mapped to something called OLE DB Core Services.

This neat little tool will let you test or create connection strings.

Create

I haven’t found a way to just open the tool but if you create a udl file and double click on it then it will open.

Currently, it’s blank, and the first step is to confirm the provider on the provider tab. I’m switching from the OLE DB provider for MS SQL to SQL Server Native Client 11.0.

Next fill in the server, login (trusted or SQL Id) and the initial database if any.

Last but not least you have the Advanced options (only the connection time in this case) and then you can hit Test Connection. Assuming it tests correctly you can now close the tool and open the udl file with a text editor. In this case here are the contents:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=&&o;Data Source=KENNETH-LAPTOP\SQL2016CS;Initial File Name="";Server SPN=""

And you’ll see that line 3 of the file is the connection string. Of course, there are other ways to create connection strings but this is pretty handy.

Now, my favorite part of this is the ability to test them.

Test

First, create a UDL file just like before and open it with a text editor. Here’s where things got weird. Those first two lines? I had to copy them exactly into the new file. I’m guessing there are other options here but I don’t know them and every letter had to be exact for this to work. Once that was done however I was able to put my connection string in with very limited information.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Data Source=KENNETH-LAPTOP\SQL2016CS

I save the file, then double click on it and the editor comes back up. And this time I was able to just hit the connection test button and confirm that it works! I can of course also make changes, test them and then look in the file to see the results.

Additional reading: Timeless and Handy: The Microsoft Data Link File