UDL files and connection strings

, 2018-12-05

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads