SQL 2005 Connection String - ASP

Posted on 2006-04-25
Last Modified: 2012-06-27
I have a classic asp web page that works perfectly with SQL 2000. It does not, however, work with SQL 2005. What do I need to do on the server to get this to work? I've already enabled TCP/IP from within the Surface Area Configuration Tool. This is my connection string:

      Dim oConnection
      Set oConnection = Server.CreateObject("ADODB.Connection")
      oConnection.Open "provider=sqloledb;server=;uid=username;pwd=P@ssw0rd;database=dbname;"

Help! :)
Question by:victornegri
    LVL 44

    Accepted Solution

    try this:

    oConnection.Open "Provider=SQLNCLI;Server=;Database=dbname;UID=username;PWD=P@ssw0rd;"

    notice the new DataProvider.

    LVL 44

    Expert Comment

    you can find all the connection string information you could ever need or want at :

    LVL 10

    Author Comment

    Tried the native client... didn't work. Where can I look to help with troublehsooting (i.e. logs)?
    LVL 4

    Expert Comment

    You are allowed to upload and attach SQL Server databases. The database will be created with UserId as prefix. For example, if you attach an SQL Express database with the name 'Employees', the actual database name will be YourUserId_Exployees.

    Here is a sample connection string to connect to SQL Server from your free web hosting account:

    "Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"


    You must enable impersonation in your web.config file in order to make the above connection string work. Make sure you have an entry as shown below in your web.config file:

    <identity impersonate="true" />

    The above entry must be placed within the section <system.web>. Your connection string may fail, unless you have the above entry.

    As for connecting to Express from application code, this should not be any different from connecting to a named instance of SQL Server 2000. Your connection string should look like this, assuming local machine and an instance name of SQLEXPRESS (you might need a different instance name, and you may have to use a machine name, rather than "." (which means local)).

    ConnectionString = "Provider = SQLNCLI;" & _
        "Data Source = .\SQLEXPRESS;" & _
        "Initial Catalog = <db name>;" & _
        "User ID = <username>;" & _
        "Password = <password>;"

    However, you might come across various errors, depending on your configuration and the tool you are using:

    Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server :
    Client unable to establish connection.
    Shared Memory Provider: The specified module could not be found.
    Shared Memory Provider: The parameter is incorrect.
    (Microsoft SQL Server, Error: 87)
    Named pipes provider: The system cannot find the file specified
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
    Shared Memory Provider: The system cannot find the file specified.

    So in addition to the connection string above, some other steps you might have to perform:

    Make sure that SQL Server Express is functioning properly:
    Start / Run... / type "CMD" without the quotes and hit OK
    Type "SQLCMD -S.\SQLEXPRESS" without the quotes and hit Enter
    Verify that you have a "1>" prompt
    Type "Exit" without the quotes and hit Enter
    Start the SQL Browser service:
    Start / Run... / type "NET START SQLBROWSER" without the quotes and hit OK

    Make sure that named pipes and TCP/IP protocols are enabled:
    Start / Programs / SQL Server 2005 / Computer Manager
    Open "Server Network Configuration"
    Highlight "Protocols for SQLEXPRESS"
    Right-click the Tcp node and make sure it is enabled (click "Enable" if it is available)
    Repeat (4) for the Np node
    Restart SQL Express if you made any changes above:
    Start / Run... / type "NET STOP MSSQL$SQLEXPRESS" without the quotes and hit OK
    Start / Run... / type "NET START MSSQL$SQLEXPRESS" without the quotes and hit OK
    If SQL Server 2000 is installed on the same machine, make sure that SP4 is installed prior to installing SQL Server 2005.
    There are some issues with the Shared Memory provider, and you may be better off defaulting to TCP/IP instead. First, make sure TCP/IP is enabled, and once it is, move it out to the front of the list in Client Protocols. Open Programs / Microsoft SQL Server 2005 / SQL Computer Manager, expand Client Network Configuration, then highlight Client Protocols. Right-click TCP/IP, enable it if it is disabled, and then click Move Up until it is the first protocol in the list.

    ot try the following code in web.config:

    <add name="conn" connectionString="data source=YourServerNameInstance;Database=YourDb;User   Id=YourLogInUserId;Password=YourUserIdPassword;"
                providerName="System.Data.SqlClient" />

    hope this will sort out your problem

    Chetan Sachdeva
    LVL 10

    Author Comment

    OK, finally figured it out. I had to do 3 things:

    1) Enable TCP/IP and Named Pipes from within the Surface Area Configuration Tool
    2) Change the connection string to the one Arthur mentioned above
    3) Change the IIS anonymous user account to one with more permissions.

    Thanks all.
    LVL 44

    Expert Comment

    glad to hear you got it working.  Gald to be of assistance

    LVL 2

    Expert Comment

    I am facing the same problem and i tried all the things actualy my site and my SQL Serever are on two different servers

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction After workin in a plethora of programming languages like C, Pro*C, ESQL/C, C++, VC++, VB, Java, HTML,JavaScipt etc, technologies and frameworks like JSP, Servlets, Struts, Spring, IBatis etc and databases like MS Access, SQLServer, Inf…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now