SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

UDL files and connection strings

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...