//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: What should my connection string look like?


What should my connection string look like?

I've been preaching for years that DSNs cause unnecessary overhead and extra maintenance tasks. And all this time, the majority of Microsoft's code samples have displayed the use of DSNs. Now, Microsoft has deprecated ODBC, and is finally backing me up. So, whenever possible, use a native OLEDB provider, rather than a DSN. 
 
The overhead caused by DSNs is clear and testable. Many people scratch their heads about why I insist that DSNs make code harder to maintain. Well, unless you can log into the ISP's machine with remote software (which is typically only allowed when you're leasing entire servers to yourself), it is not trivial to make changes to existing DSNs, or add new ones. You need to have your ISP manage your DSN(s), which certainly takes time, and sometimes costs money (depending on the host). Granted, some hosts have pretty "control panel" type applications that help you do this. But it still moves one of your programming tasks to a location outside of your development environment. 
 
Also, you can only manage so many DSNs reasonably (see KB #252475). Using a DSN-less connection (for example, those found in this article), you can avoid this hassle *and* make your data access faster. 
 

SQL Server 
 
Using SQL Server Authentication: 
 
<% 
    cst = "Provider=SQLOLEDB;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "Initial Catalog=<dbname>;" & _ 
        "Network=DBMSSOCN;" & _ 
        "User Id=<uid>;" & _ 
        "Password=<pwd>" 
 
    ' // Use of Network=DBMSSOCN is to avoid Named Pipes errors; 
    ' // see Article #2082 for more details 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 
To use SQL Server authentication, SQL Server must be set up in 'mixed mode' (both SQL Server and Windows Authentication) and you must have a SQL Server login with appropriate permissions on the database(s) you are connecting to. To set SQL Server to mixed mode, open Enterprise Manager, right-click the relevant server registration, and move to the security tab. Under authentication, make sure that "SQL Server and Windows" is checked. (You should also take this opportunity to make sure that sa has a strong—and definitely not a blank—password). 
 
Using Windows Authentication: 
 
<% 
    cst = "Provider=SQLOLEDB;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "Initial Catalog=<dbname>;" & _ 
        "Integrated Security=SSPI" 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 
Note that to use Windows Authentication, IUSR_<machineName> must be in the domain, and given proper access to the SQL Server; or, you must disable anonymous access on the site / application - which will allow IIS to pass the users' credentials to SQL Server. Not doing either of these things will result in the following error: 
 
Microsoft OLE DB Provider for SQL Server error '80040e4d' 
Login failed for user '<machineName>\IUSR_<machineName>'. Reason: 
Not associated with a trusted SQL Server connection.
 
For information about configuring SQL Server for access through IIS, see KB #247931. 
 
If you are using an IP address or a server name that should be resolved through DNS or over the Internet, you'll want to make sure that TCP/IP is enabled. Go to Start / Programs / Microsoft SQL Server, open the Client Network Utility, and on the General tab, make sure TCP/IP is at the top of the list on the right hand side. 
 
If you are trying to connect to a named instance, you can use the following format: 
 
Data Source = <server/ip>\<instancename>;
 
If you are running SQL Server or MSDE on the same machine as your ASP pages, you are probably tempted to use the hostname "localhost." This doesn't always work, due to different configuration issues, so try (local), 127.0.0.1, the actual host name, or simply a period ("."). 
 
Some people have asked about the difference between SQLOLEDB and SQLOLEDB.1. The former is the version-independent provider name, while the latter uses a specific version (.1, obviously). It is recommended that, unless you need to use the specific .1 version, you use the version-independent provider name—if for no other reason, to ensure that your connection string works on every machine you port it to (some might not have the .1 update). 
 

SQL Server Analysis Services 
 
<% 
    strASConn = "PROVIDER=MSOLAP;" & _ 
        "DATA SOURCE=<ip>;" & _ 
        "INITIAL CATALOG=<dbname>" 
 
or 
 
    strASConn = "PROVIDER=MSOLAP.2;" & _ 
        "DATA SOURCE=<ip>;" & _ 
        "INITIAL CATALOG=<dbname>" 
 
' and then 
 
    set ASCellset = CreateObject("ADOMD.Cellset") 
    ASCellset.ActiveConnection = strASConn 
 
or 
 
    set ASCatalog = CreateObject("ADOMD.Catalog") 
    ASCatalog.ActiveConnection = strASConn 
%>
 

Oracle 
 
If you are using the Microsoft OLEDB provider for Oracle: 
 
<% 
    cst = "Provider=MSDAORA;" & _ 
        "Data Source=<server>.<dbname>;" & _ 
        "User ID=<uid>;" & _ 
        "Password=<pwd>;" 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 
If you are using the Oracle OLEDB Provider (which you can download after registering here):&nbsp
 
<% 
    cst = "Provider=OraOLEDB.Oracle;" & _ 
        "Server=<server>;" & _ 
        "Data Source=<dbname>;" & _ 
        "User ID=<uid>;" & _ 
        "Password=<pwd>" 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 

MySQL 
 
MySQL.com used to have a download available for an OLEDB provider to MySQL (MyOLEDB): 
 
<% 
    cst = "Provider=MySQLProv;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "Database=<dbname>;" & _ 
        "User Id=<uid>;" & _ 
        "Password=<pwd>" 
%>
 
However, it has gone missing in recent months. If you don't have the OLEDB provider, you will need to use ODBC. You can try these connection strings (depending on whether you have MySQL ODBC or MyODBC drivers installed): 
 
<% 
    cst = "Driver={MySQL};" & _  
        "Server=<x.x.x.x>;" & _ 
        "Database=<dbname>;" & _ 
        "Uid=<uid>;" & _ 
        "Pwd=<pwd>" 
 
    cst = "Driver={MySQL ODBC 3.51 Driver};" & _ 
        "Server=<x.x.x.x>;" & _ 
        "Database=<dbname>;" & _ 
        "Uid=<uid>;" & _ 
        "Pwd=<pwd>;" 
%>
 
You can download Windows MyODBC drivers here
 

DB2 and AS/400 
 
Please see the following KB articles for Configuring Data Sources for the Microsoft OLE DB Provider for DB2 and AS/400 and VSAM
 

Microsoft Access 
 
<% 
    cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
        "Data Source=" & Server.MapPath("/<pathtofile.mdb>") 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 
If you don't have the latest version of JET 4.0 installed, consider installing it (see Article #2342). You also might find that with newer versions of MDAC, you get this error: 
 
Provider error '80040e4d'  
Authentication failed.
 
If you can't install the latest JET driver, or you are having issues getting it to work, you can fall back to the native ODBC driver for Access (however note that it is deprecated): 
 
<% 
    cst = "Driver={Microsoft Access Driver (*.mdb)};" & _ 
        "DBQ=" & Server.MapPath("/<pathtofile.mdb>") 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst 
%>
 
If your Access database is on another server, you will need to ensure that Jet 4.0 is installed on the remote server, and that you know the *local* location of the MDB file on that server. Once you have those two vital pieces, you can use a connection string like this (see Article #2168 for more details): 
 
<%  
    cst = "Provider=MS Remote;" &_  
        "Remote Server=http://<x.x.x.x>;" &_  
        "Remote Provider=Microsoft.Jet.OLEDB.4.0;" &_  
        "Data Source=c:\inetpub\wwwroot\file1.mdb;"  
    set conn = CreateObject("ADODB.Connection")  
    conn.open cst 
%>
 
(And I feel sorry for you. Access is not really up to this task, as I'm sure you'll quickly learn.) 
 
In each case, of course, filling in the <variables> with the proper values. 
 
Regarding using JET over ODBC, according to KB #222135
 
"When running Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications)." 
 
If you are using a Workgroup file, you might have seen this error: 
 
Microsoft JET Database Engine (0x80040E4D) 
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
 
To get around this, you need to properly specify your workgroup file location, and pass a valid username and password, as follows: 
 
<% 
    cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
        "Data Source=" & Server.MapPath("/<pathtofile.mdb>") & ";" & _ 
        "Jet OLEDB:System Database=<wg_file_name>.mdw" 
 
    set conn = CreateObject("ADODB.Connection") 
    conn.open cst, "Username", "Password" 
%>
 

 
For a more exhaustive list of connection string types, see Connection Strings , where Carl Prothman has compiled several different techniques of connecting to just about any data source -- from DB2 to Sybase to Visual FoxPro... 
 
You can also see Jimmy Engström's ConnectionStrings.com   - &#160... for a variety of connection strings.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

 

 


Created: 7/9/2000 | Last Updated: 6/4/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (65)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...