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

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:\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=;

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

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.


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.



About the Author

From ASP101

Articles originally posted on ASP101.com

Comments

  • kSNHo lmf vQUo

    Posted by FXYZfNaeVG on 07/20/2013 12:59am

    buy cheap xanax get xanax online no prescription - where to buy xanax online no prescription

    Reply
  • Hovedtelefoner isnot bare et headset, er et vindue til at se verden

    Posted by wanzixiao on 06/04/2013 08:20am

    [url=http://www.beatsbydrdredanmark.webgarden.com/]beats by dre tilbud[/url] Beats enkel sammen med skiftes i sammen med frigivet eventuelle udskiftelige pandebånd er nået smertefri sammen med sat på mod stillet af blot beats by dremagneter. Dybest set vælter enhver pandebånd på grund af en indehaver toppen inden for headsets sammen med tilføje en anden. Progressionen modtage en smule af med en minimal sammen med før du kender det hele, vil du have et helt andet sæt af moderigtigt koordinerede headsets. Brug hvad vores firma er næsten garanteret er helt sikkert pude-soft kunstlæder høre kopper kaffe ud over et pandebånd, at leveres i en god samling for farvestoffer sammen med sorter disse virkelig er nødt til at føle, udseende sammen med tone enhver utroligt perfekt under forretningen. [url=http://www.beatsbydrdredanmark.webstarts.com/]beats by dre danmark[/url] De Beats by Dr. Dre Wireless, kan du nyde fuld musikalsk frihed. Den trådløse headset, designet af den populære Solo HD, har Bluetooth, som du nemt sluttes til din smartphone (eller MP3-afspiller med Bluetooth). Alle nødvendige kontroller for volumen, springe spor eller leje eller arbejder? Opsige et telefonopkald placeret til højre forkammer og altid inden for rækkevidde. [url=http://www.nyebeatsbydrdre.350.com/]beats by dre danmark[/url] Dawn Harper, den olympiske 100m forhindringer mester, postet et billede på Twitter af hendes spor og felt holdkammerater og udtalelsen : “Jeg er beæret over at være en olympisk, men wedemandchange rule40.” Hun har også lagt et billede af sig selv med tape over hendes mund emblazoned med ordene “regel 40?.På fredag, besluttede juridiske rådgivere til LOCOG ikke at gribe ind over for en global annoncekampagne efter Nike, hvilket ikke er en officiel 2012 sponsor, der spillede hverdagens atleter konkurrerer på steder rundt omkring i verden hedder London.

    Reply
  • You pine proper for some tomato basil and mozzarella. For indoor utilization, these slippers are as phosphorescence and manueverable as sneakers.

    Posted by Soaceddew on 04/20/2013 01:15am

    Has upright released some chic color Let off Inneva Woven shoes, Nike recently with another pathway to regurgitate shoes with different styling to all [url=http://fossilsdirect.co.uk/glossarey.cfm]nike huarache[/url] eyes. This brings special print run Let off Inneva Woven is a Creamy Name of works in the series, represents shoes Italian made the assurance. Latest Safe from Inneva Woven black and pornographic are readily obtainable in two color schemes, to hand-knit Woven vamp in addition to infiltrated Italy's [url=http://fossilsdirect.co.uk/glossarey.cfm]nike huarache[/url] finest crafts, for the moment gives athletes arrange to the foot of comfort, the most superior possibility a affairs is the outclass of Free 5 configuration, barefoot feel it will allure cannot be ignored. Nike Empty Inneva Woven SP Oyster-white Label Wedge on Walk 16 at outlets around the [url=http://northernroofing.co.uk/roofins.cfm]nike free uk[/url] trade name on the shelves, and on trade in narrow bearing, interested friends should recompense terminate attention to Nike announced the news.

    Reply
  • Nice one there

    Posted by Slalaleasyday on 03/12/2013 08:21pm

    Nice Post. ---------- I love http://youtube.com

    Reply
  • connection strings

    Posted by smriti on 03/04/2013 05:58am

    Thanks alot.. for these clarifications. i was looking for connection strings pdfs.this article presents a clear view for the beginers..really tooo fruitful it z.i expect some more with a neat example with oracle engine.... thank u

    Reply
  • ASP ADO CONNECTIVITY

    Posted by smriti on 03/04/2013 05:57am

    Thanks alot.. for these clarifications. i was looking for connection strings pdfs.this article presents a clear view for the beginers..really tooo fruitful it z.i expect some more with a neat example with oracle engine.... thank u

    Reply
  • IBGAvL Ae rx UwV QNkh NW

    Posted by RLGRhuIGSZ on 02/17/2013 01:41pm

    buy tramadol rx buy tramadol online fedex delivery - tramadol tablets dosage

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 14, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Data protection has long been considered "overhead" by many organizations in the past, many chalking it up to an insurance policy or an extended warranty you may never use. The realities of today makes data protection a must-have, as we live in a data-driven society -- the digital assets we create, share, and collaborate with others on must be managed and protected for many purposes. Check out this upcoming eSeminar and join Seagate Cloud …

  • Hybrid cloud platforms need to think in terms of sweet spots when it comes to application platform interface (API) integration. Cloud Velocity has taken a unique approach to tight integration with the API sweet spot; enough to support the agility of physical and virtual apps, including multi-tier environments and databases, while reducing capital and operating costs. Read this case study to learn how a global-level Fortune 1000 company was able to deploy an entire 6+ TB Oracle eCommerce stack in Amazon Web …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds