DSN and DSN-less Connection Strings

Article Details
URL: https://support.quadrahosting.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=23
Article ID: 23
Created On: 07 Aug 2004 05:00 AM


DSN or DSN-less?

Connection to your database can be done using either a DSN (Data Source Name) or without DSN aka DSN-less.

DSN stores all the information required to connect to your database, including your database type, server / file location, username and password as well as connection options is stored in the DSN itself. Your script simply refers to the connection using the DSN name.

On the contrary, when using DSN-less method, your script would have to specify all the above details in the connection string itself. See examples below.

Should you use DSN or DSN-less method?

Some people tend to think that DSN-less is faster because it saves the time to look up the DSN information in the registry. Some people think DSN is faster based on several tests.

The main consideration really is this:

With DSN method, you will have to maintain two sets of settings: your script and the DSN itself. With DSN-less method, all you need to maintain is the script / connection string.

For portability reasons, it would be better to use DSN-less method. However depending on your circumstances, DSN method might be more appropriate / convenient.

For MS Access Database connection strings, you MUST use the DSN-less OLEDB driver. ODBC driver for MS Access causes instability on the server.

DO NOT USE ODBC (DSN or DSN-less) for MS Access!


Example of DSN Connection:

MS SQL / MySQL / PostgreSQL



The DSN itself can be created / configured through the control panel - ODBC section.

Please note that there is normally a prefix that is enforced upon the DSN name which is based on your account to avoid name clashes with other users. Be sure to use the full DSN name as displayed on the control panel, including the prefix.


Examples of DSN-less connection:

MS Access


"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:somepathmyDb.mdb;"


With ODBC:

"Driver={SQL Server}; Server=MyServerName; Database=myDatabaseName; UID=myUsername; PWD=myPassword"

With OLEDB (Recommended):

"Provider=sqloledb; Data Source=myServerName; Initial Catalog=myDatabaseName; User Id=myUsername; Password=myPassword"


With ODBC:

"Driver={MySQL ODBC 3.51 Driver}; Server=servername; Database=dbname; UID=username; PWD=password; Option=3"

MySQL (When connecting from Windows 2008)

With ODBC:

"Driver={MySQL ODBC 5.1 Driver}; Server=servername; Database=dbname; UID=username; PWD=password; Option=3"


With ODBC:

"Driver={PostgreSQL}; Server=servername; Database=dbname; UID=username; PWD=password"


Microsoft's Knowledge Base provides more in-depth information on DSN and DSN-less connections.