asked on

Classic ASP connecting to SQL Server 2008 R2 error

Hi Experts,

I am trying to migrate a classic ASP site to a webserver (IIS7). All pages are loading fine except the ones requiring Database connectivity to SQL Server 2008 R2. The connection string in use is:

Provider=[SQLNCLI10];Server=10.0.0.51\DBServer;Database=Intranet;Uid=username;Pwd=password;

The error I get is:
ADODB.Recordset error '800a0e7a'
Provider cannot be found. It may not be properly installed.

Following is the architecture and configuration done thus far for the installation:

ARCHITECTURE:

Webserver:

IIS 7.0 on Windows Server 2008 Standard Edition

Database Server:

SQL server 2008 R2 installed on a separate server running on Windows Server 2008R2 Standard Edition

CONFIGURATION:

IIS:

Installed ASP

Application Pool Configuration:

.Net Framework version: No Managed Code

Managed pipeline mode: Classic

Ran following command under elevated command prompt to run the app pool in its isolated storage:

C:\Users\Administrator.BDLAWYERS>%windir%\system32\inetsrv\appcmd set AppPool –a ppPool.name:Site1 - processModel.loadUserProfile:false

[

ASP Configuration:

Enabled parent paths: True

Send Errors to Browser: True

SQL Server 2008 R2 (running on Windows Server 2008R2 Standard Edition):

Enabled remote connections

Firewall:

Opened up ports 135, 1433 and 1434

Added SQL Server Browser service to the excepted program. The path to executable is: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

Access to DB:

Created a user with DBO access to the Database

Tested connectivity by logging in from SSMS 2008 and then running simple queries.

WDAC checking:

Since WDAC is inbuild component of Windows Server 200, I ran following command under elevated command prompt to check for integrity violations on webserver.

SFC /SCANNOW

Your help is much appreciated.

Kind regards,
HM

cyberkiwi

Are you sure you meant to post the password in plain text?
You can request attention for a mod to blank out the password.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sammySeltzer

If your connection string is pointing to a remote server, then use this:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL Server};" & _
"Server=serverName;" & _
"Address=serverName,1433;" & _
"Network=DBMSSOCN;" & _
"Database=DBName;" & _
"Uid=username;" & _
"Pwd=password"

LawCentral

ASKER

Hi Cyberwiki,

Thank you for the info. I have now requested the connection string to be edited.

I did install the SQL Server 2008 R2 native client but it did not fix the errors.

Would you have any other suggestions?

HM

LawCentral

ASKER

HI sammySeltzer,

Can you kindly edit your prior response and take the password out?

I tried your solution and now I get the error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

Any pointers?

HM

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LawCentral

ASKER

Good pickup. It must have been a typo from a copy paste.

I did take it out and now I get the error:

Microsoft SQL Server Native Client 10.0 error '80004005'
Named Pipes Provider: Could not open a connection to SQL Server [5].

I googled and found below blog post:

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

All my settings checked against the post.

-HM

cyberkiwi

Are you on the same network as "10.0.0.51"?
Can you ping it?
Have you enabled remote connections on the SQL Server (default should allow TCP/IP)?
Is the instance name correct and the service running?
Are there any firewalls between you and the SQL Server (try turning them all off to verify, then back on one by one)

LawCentral

ASKER

Disabling Firewall on the Database server allows the connections though. I have ports 135, 1433 and 1434 open. Are there any other ports I need to open?

PS: Yes, all servers are on the same network.

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LawCentral

ASKER

Hi Cyberwiki,

Thank you. As pointed out by you the default instance on the DBServer was using 1433. I allocated a static port as well as created an alias. I then allowed the port through firewall and now everything is in sync and buzing again.

Thank you again.
HM