SQL 2005 Connection String - ASP

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! :)
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

try this:

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

notice the new DataProvider.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
you can find all the connection string information you could ever need or want at :


victornegriAuthor Commented:
Tried the native client... didn't work. Where can I look to help with troublehsooting (i.e. logs)?
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

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
victornegriAuthor Commented:
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.
glad to hear you got it working.  Gald to be of assistance

I am facing the same problem and i tried all the things actualy my site and my SQL Serever are on two different servers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.