Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 6, 2001

DSN, DSN-Less, or UDL

By Andy Warren

In a previous article (DSN) I discussed the basics of DSN's and the different types that you can use. In this article I'd like to continue that discussion by looking at two relatives of the DSN, the DSN-less connection and the Universal Data Link (UDL). 

DSN-less means that you don't use a DSN to store your connection information. Instead, you either prompt your user for it at run time (Query Analyzer is a good example) or you store the equivalent information somewhere else; the registry, an INI file, or even a table. If you truly need to provide a dynamic connection where the user will be connecting to a variety of different sources, DSN-less is definitely easier on the user. Many developers decide to use a DSN-less connection and just store the connection string in the registry, eliminating the need to make sure that a valid DSN for the application exists. Using a DSN-less connection is not good or bad, just a decision you need to make when designing your app. I will say that most help desk folks will be much more familiar with DSN's.

This is the Query Analyzer login dialog. DSN-Less!

Just to give you a feel for the difference, here is how you connect to Pubs using both a DSN and a DSN-Less connection:

'using a DSN

cn.open "DSN=Pubs"



cn.open "Server=Andy;Database=Master;Trusted_Connection=Yes"

OLEDB brought another connection option with it, the UDL. UDL's are created with the Data Link Properties dialog (shown below). UDL's are an evolutionary step from DSN's. They support creating both ODBC and OLEDB connections, plus the dialog displays all of the properties that each particular driver (provider) supports. Because the underlying format of the UDL is pure ascii, it's easy to create one using standard text operations if needed. Even Notepad would work! I like the fact that all of the connection string options are easy to work with (app name, time out, etc). 

This is an example of what the text of a UDL file looks like. To view it, just create one, then rename the file with a .txt extension and open it. You can see that this is a one line connection string with a short header:


; Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=ANDY


You use a UDL almost exactly like a DSN:

cn.open "File Name=C:\Program Files\Common Files\System\OLE DB\Data Links\Test.UDL"

I found several references on MSDN that mentioned that the default location for UDL files is C:\Program Files\Common Files\System\OLE DB\Data Links, but I was unable to find a registry key that would allow this to be configured. Putting your UDL's there would have the effect of making them "system" UDL's. A better approach might be to place them somewhere in the users profile under Documents & Settings.

I found quite a few useful articles while researching. This one has a reg file you can download that gives you the ability to right click on your desktop to create a new UDL: http://support.microsoft.com/support/kb/articles/Q244/6/59.ASP

Right here on Swynk Danny Lesandrini has two great articles: 

ODBC DSN-Less Connection Tutorial 

Problematic or Programmatic ODBC?


SQL Magazine also had a couple articles recently that explore the UDL and Data Link API in more detail than I do here:




I think UDL's and OLE DB are the way to go, but I suspect we'll be dealing with DSN's for a while yet. As always, I look forward to your comments and questions!

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM