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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 8, 2001


By Andy Warren

DSN is an acronym for Data Source Name. It's a simple and standard way to describe how to connect to a data source using an ODBC driver. More importantly, using a DSN means you can change the location of your data by updating the DSN, no update to your application required. Keep in mind that DSN's only describe ODBC connections, not OLEDB connections.

There are three types of DSN's; system, user, and file. System and user DSN's are registry based, while File DSN's are stored in the file system. You normally create and administer DSN's through the ODBC Data Source Administrator, found in Control Panel on NT/9x machines, or in Administrative Tools for Win2K machines. However, there is nothing magical about editing DSN's. You can use regedit to view/modify user or system DSN's, and notepad works fine for file DSN's. 

ODBC Data Source Administrator

Most information about DSN's is stored in the registry. HKLM\Software\ODBC\ODBCINST.INI contains a list of all ODBC drivers installed on your machine. HKLM\Software\ODBC\ODBC.INI contains all of your system DSN's stored as separate subkeys. ODBC.INI has two other subkeys, ODBC Data Sources that contains a list of all your system DSN's, and ODBC FileDSN, which contains the name of the folder where File DSN's are stored (C:\Program Files\Common Files\ODBC\Data Sources is the default). User DSN's are stored in Hkey_Current_User\Software\ODBC, but it has only an ODBC.INI key. No Odbcinst.ini key is needed, as installed drivers are available to all users and so are stored in Hkey_LocalMachine.

Choosing which type of DSN to use depends on how your application works. In almost all cases I've 
found that a system DSN works well since any user using my app on that machine will still be connecting to the same datasource. It also makes installation a snap, since the application can be installed for 'All Users' and the DSN will be accessible to anyone logging in to that machine. If you use a user DSN, then you'll have to do some extra work to make sure the DSN gets created when a user new to that machine logs in for the first time.

To a developer, all DSN's are not quite equal. Here is how a developer would open a connection to the Pubs database using each type of DSN:


User cn.Open "Provider=MSDASQL;DSN=Pubs"
System cn.Open "Provider=MSDASQL;DSN=Pubs"
File cn.Open "Provider=MSDASQL;FILEDSN=Pubs"

As you can see, there is no difference in the syntax required to use either a user or a system DSN - in fact, some quick testing with ADO 2.6 reveals that if you have a system and user DSN of the same name, it will choose the user DSN. Another interesting point is that you can actually have a system, user, and file DSN all with the same name. 

One thing you want to consider is how will you change the DSN on 500 workstations if you move your data? If you use a user/system DSN, you can create .reg file and apply it in the login script (regedit /s filename.reg) or perhaps just email the .reg file to the users with instructions to apply the change (good luck!). 

If you're using a File DSN, you can just copy the new DSN over the old one during the login script or again, email the updated file to your users. Another alternative for file DSN's is to change the default folder for file DSN's to point to a share available to all users. Then you can update the DSN at any time and the change is instantly available to your users. If you choose this method, please set the security on the folder to read only!

Additional reading:

MS SQL Archives

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