What’s in an ADO Connection String?

by John Peterson


Introduction

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:pathfilename.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.

Microsoft.Jet.OLEDB.4.0

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:pathfilename.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:pathfilename.mdb; User ID=admin; Password=;

SQLOLEDB

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:

DSN=data_source_name;

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:pathfilename.mdb;

Microsoft Excel

Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:pathfilename.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:pathfilename.mdb;

but this to work:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:pathfilename.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.

Conclusion

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.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read