Difference Between Types of DSN and a UDL

Issue Details

What is the difference between a System DSN, a User DSN, a File DSN, Universal Data Link (UDL), and DSN-less connection?

Solution

Data source name (DSN) is a data structure that contains information about a database. The information is needed in order to connect to the database. A Universal Data Link (UDL, also known as a Microsoft Data Link) is a universal file that links to a database. 

There are many different database drivers (or providers) with many ways of connecting to them. The table below list the five most common ways in which you can interact with a database provider.

Connection Method	Who Can Access Them?	Where Connection Information is Stored
File DSN	Any user who has access to the File	In a file format (*.dsn)
System DSN	Any user on that system	System Registry
User DSN	Only for the user for which that data source was created	System Registry
UDL (Universal Data Link)	Any user who has access to the File	In a file format (*.udl)
DSN-less	Any one who has access to the database files (i.e. mdb,.xls)	No static storage of connection information (passed as the connection string during runtime)

Additional Information

System DSNs
System DSNs work for anyone using that system. For example, no matter who logs onto the machine, system DSNs can be seen by all users, and they are stored in the Local Machine section of the Registry.

User DSNs
User DSNs work only for a specific user on a machine. Therefore, if someone other than the person who created the user DSN logs onto that machine, then the User DSN will not show up. This DSN is stored in the Current User section of the Registry.

File DSNs
File DSNs are not stored in the Registry but rather in a file. That means you can store these DSNs to disk or network and use them on any machine or any user that has access to it.

UDLs
National Instrument's Database Connectivity Toolkit uses OLE DB to communicate with the databases. If you use a DSN, then the commands must be translated from OLE DB to ODBC through the OLE DB Provider for ODBC. Therefore, it is more efficient, and recommended, to use UDLs as the method of connection with the toolkit if an OLE DB Provider is available for the Database.

DSN-less
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 (SQL Query Analyzer is a good example) or store the equivalent information somewhere else such as 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,  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.