What's in an ADO Connection String?

Tuesday Jan 1st 2008 by From ASP101

The connection string is how ADO (the DB access layer used in ASP) knows how to find your database. How you build your connection string and it's syntax will vary based on the provider you want to use. I'll be covering OLE DB and ODBC.

by John Peterson


I actually had some trouble naming this article. I started out with something like "It Don't Mean A Thing If You Ain't Got That String" since quite often people have everything else in their code fine, but can't get connected. I decided that wouldn't work since I wanted you to be able to tell what the heck the article was going to cover and I wasn't sure people would get the Duke Ellington reference. Then I started thinking about something along the lines of "Get Yourself Connected." Again I found the lack of descriptiveness (if that's a word) troublesome and I didn't want people who don't get HBO to miss all the Sopranos references I would have been forced to make.

So anyway... I ended up with the ever so descriptive "What's in an ADO Connection String?" It's not as colorful as I was hoping for, but it'll have to do and hey... it actually tells you what I'm going to cover so I guess I can just forget the rest of this intro...

... or (upon a second read) maybe not. Let me give you a brief crash course. The connection string is how ADO (the DB access layer used in ASP) knows how to find your database. How you build your connection string and it's syntax will vary based on the provider you want to use. I'll be covering OLE DB and ODBC. If you're looking for info on something else then it's beyond the scope of this article and quite honestly probably beyond the scope of the site!

Connection Strings in General

To begin with, connection strings are simply string variables which contain database connection information. There's nothing magical about them so you can use all the string functions you're used to using to manipulate them if you need to. The only thing that makes them a connection string is that they are then passed to ADO which will interpret them and act accordingly. Since they're going to be passed to ADO, they need to be in a format ADO understands. Your standard connection string contains a number of arguments set equal to their associated values and seperated by semicolons. It's basic form is something like this:

argument1=value1; argument2=value2; argument3=value3;

Of those arguments, there are only four that ADO understands. The rest are simply passed through to the provider. The four ADO recognizes are Provider, File Name, Remote Provider and Remote Server. The last two are only used with RDS and I won't be covering them.

File Name is used to point to a UDL file which can be used to provide all the other parameters. As such your connection string would just contain this one parameter and would look something like this:

File Name=C:\path\filename.udl;

You can create a UDL file by simply creating a new empty text file and giving it the extension .udl. Once created, you can set and adjust it's properties by simply double-clicking it from Windows Explorer or right-clicking it and selecting properties.

For more on using UDL files to help you build your connection strings, check out these articles from 4guysfromrolla.com: A Nifty Way to Create Connection Strings and Creating Connection Strings in Windows 2000. This can be a really useful way to start building your own connection strings in a user friendly environment. Just be sure to take a look at the resulting file in a text editor.

This leaves us with only one argument: Provider. This is the important one. It tells ADO what provider it should connect to in order to access the data you want. It is often left off in which case it defaults to MSDASQL which is Microsoft's OLE DB Provider for ODBC. There are a number of providers supplied when you install MDAC, but I'll be focusing on MSDASQL, Microsoft.Jet.OLEDB.4.0, and SQLOLEDB which are the three most commonly used in ASP.

For additional information about other providers and about MDAC, OLE DB, and ODBC in general, check out Microsoft's Universal Data Access Web Site. It's an invaluable site which contains downloads of the latest drivers and providers as well as links to the detailed documentation on MSDN.

OLE DB Connection Strings

Since they became available (around MDAC 2.0 I believe) we've been recommending and using the OLE DB providers: Microsoft.Jet.OLEDB.4.0 and SQLOLEDB. Microsoft.Jet.OLEDB.4.0 is for use with Microsoft Access and SQLOLEDB is for use with Microsoft SQL Server. They generally offer better performance and reliability when compared to MSDASQL.


This is the OLE DB provider for Access. To connect to an Access database using this provider, the only other attribute required to make a connection is the Data Source attribute which is used to specify the full path and file name of the Access .mdb file. A minimal OLE DB Provider for Jet connection string would therefore look something like this:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb;

The only other commonly used parameters are User ID and Password which are used to specify user authentication values. A user name of "admin" and a blank password are the defaults we tend to use resulting in the following default connection string:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb; User ID=admin; Password=;


The OLE DB provider for MS SQL Server is a little bit more complex. The Data Source parameter is once again used, but this time it specifies the name or address of the SQL Server. Because SQL Server can run multiple databases and we haven't yet picked one we need another parameter to indicate the databse we want to use. This parameter is called Initial Catalog. That combined with the same parameters as above for User ID and Password results in this basic form:

Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;

I'm not sure if it's more because of the networking required or the additional capabilities of the database, but there are more parameters one can specify when utilizing SQLOLEDB then there are with MS Jet. You can get a list of all of them from the SQL Server ADO Programmer's Reference in the SQL Server Books Online. In particular, you're looking for the Provider-Specific Connection Parameters of the connection object.

Honestly though... the only other parameter I ever use is Network Library and this one I use pretty religiously. It's relatively important and has been the key to solving many a connection problem. You see, Named Pipes is often set up as the default network library to use when connecting the client (IIS) to the server (SQL Server). While this can be changed via the SQL Server Client Network Utility, it rarely is. This configuration is fine if the client and server are on the same network, but in today's TCP/IP based world, Named Pipes is often not the network library of choice. To force the client to use TCP/IP to connect (ignoring the defaults altogether) specify a value of dbmssocn for the Network Library parameter.

For reference, here's a list of the values for the different Win32 network libraries:

Network Library Library Name
TCP/IP dbmssocn
Named Pipes dbnmpntw
Multiprotocol (RPC) dbmsrpcn
NWLink IPX/SPX dbmsspxn
AppleTalk dbmsadsn
Banyan VINES dbmsvinn

So, adding that to our basic form above results in this generic version of the basic connection string I'd start with when setting up a SQLOLEDB connection via TCP/IP:

Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password; Network Library=dbmssocn;

ODBC Connection Strings

While I indicated earlier that we prefer OLE DB, ODBC (MSDASQL) has been around for a long time and back when ASP was first released it was really your only option for ASP database connections. Since it is still so widely in use and is actually the default provider, I felt I'd be somewhat remiss if I didn't cover it as well.

There are two styles of ODBC connection string: those that utilize a DSN (Data Source Name) and those that do not (often called DSN-less connections). The parameters they use are identical, it's just a matter of where they are specified and stored.

DSN Connections

DSN connections store their connection information in the Windows Registry. Naturally it's not a good idea to have people poking around in the registry so in the Windows NT4 Control Panel you'll find an applet called "ODBC Data Sources" which provides a wizard like interface to collecting connection settings. In Windows 2000, it's been moved to the "Administrative Tools" Folder in the Control Panel and renamed "Data Sources (ODBC)", but it functions the same.

In a nutshell, it's only real functionality is to help you create a connection string, save these connection settings to the registry, and associate them with a unique DSN. If you're using DSNs for ASP, make sure they're System DSNs so that they're available for all users. Since these settings are all previously saved, the connection string becomes simplicity itself:

Provider=MSDASQL; DSN=data_source_name;

or since MSDASQL is the default, simply:


If you need to, you can also specify username and password information, but the parameter names are slightly different then with OLE DB resulting in a fully qualified connection to a DSN that looks something like this:

Provider=MSDASQL; DSN=data_source_name; UID=username; PWD=password;

Any other parameters you need, including what driver to use and even what type of database to connect to, is set via the wizard-like setup in the DSN configuration utility in the control panel discussed earlier.

DSN-less Connections

A DSN-less connection is operationally identical to a DSN except that the server doesn't have to access the registry to access the parameters since they're all specified in the connection string itself.

The only parameter required by all ODBC connection strings is Driver which specifies which driver to use. Unfortunately this isn't enough to get connected to anything and additional information is required by each driver. Most have a similar syntax and I'll provide samples of the couple most popular ones below:

Microsoft Access

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

Microsoft Excel

Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\path\filename.xls;

Microsoft Text

Provider=MSDASQL; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\path\;

Notice there is no file name. The directory acts like a database with files working in the role of tables. Hence, you don't specify the file name until opening a recordset. It's a little weird... I recommend you experiment with it some first if you find a need to use this driver.

Microsoft SQL Server

The SQL Server driver again requires some additional information to get connected:

Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;


A few final notes about all the ODBC connections above. There is an interesting bug that I seem to remember causing problems for a while. If the above strings aren't working, try removing the space following the semi-colon after the Provider parameter. There used to be a bug in the OLE DB Provider for ODBC that would cause this to fail:

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

but this to work:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

And remember that MSDASQL is the default value for Provider so if you want to, you can simply leave the parameter off altogether. In fact, you'll notice that almost all the sample code you run across does leave it off. I've included it in the samples above for illustration and because the Provider is a relatively central point to get in order to really understand how all these parameters fit together to allow these connection strings to actually get you connected.


I hope this article will help clear up some of the confusion many beginners feel when they come face to face with a connection string. Not knowing what all the parameters mean can really put a damper on things when you need to make a change. Hopefully this article has helped, but please don't be discouraged if you're still a little bit lost. It really is a pretty complex topic when you get down into it. Fortunately, as you use the them more and gain some experience, things almost always start to fall into place and make sense. If there's anything you think should be added to this article or clarified, please let me know.

A Note on SQL Server Named Instances

I've gotten a number of questions about how to connect to a SQL Server Named Instance via ADO. For those of you not familiar with the concept of a named instance you can read more about them here: MSDN: Working with Named and Multiple Instances of SQL Server 2000.

In most cases, it's just as easy to connect to a named instance as it is to connect to a default instance. The only difference is that you need to include the instance name when specifying the name or address of the SQL Server you're connecting to.

For example, if you're using the OLE DB provider for MS SQL Server, instead of (the connection string listed above under SQLOLEDB):

Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;

you would need to use:

Provider=SQLOLEDB; Data Source=server_name_or_address\instance_name; Initial Catalog=database_name; User ID=username; Password=password;

That should work for most client applications. The only time you might run into problems is if you try and use an older version of a tool that doesn't know about named instances. In cases where this is necessary, see Microsoft's Knowledge Base article: INF: How to Connect to an SQL Server 2000 Named Instance with the Previous Version's Client Tools.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved