• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41993
  • Last Modified:

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=127.0.0.1;uid=username;pwd=P@ssw0rd;database=dbname;"
%>

Help! :)
0
victornegri
Asked:
victornegri
1 Solution
 
Arthur_WoodCommented:
try this:


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

notice the new DataProvider.

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

http://www.connectionstrings.com/

AW
0
 
victornegriAuthor Commented:
Tried the native client... didn't work. Where can I look to help with troublehsooting (i.e. logs)?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
csachdevaCommented:
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"

Impersonation

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

Regards,
Chetan Sachdeva
0
 
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.
0
 
Arthur_WoodCommented:
glad to hear you got it working.  Gald to be of assistance

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now