CODEWITHSUNDEEP
sql-server-2008iis-7asp-classicconnectionsql-server-native-client
IsmailS
IsmailS

Reputation: 10863

Cannot connect from Classic ASP to SQL Server 2008 R2 using SQL Native Client (Windows 7 - IIS7)

I'm able to connect to SQL server 2008 R2 when I use Provider=SQLOLEDB in my connection string. But when I use Provider=SQLNCLI in connection string I'm unable to connect.

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

/test.asp, line 7

Code written within test.asp is below

<%
    Set cn = Server.CreateObject("ADODB.Connection")

    'Doesn't work
    cn.Open "Provider=SQLNCLI;Server=remoteServer\SQL2008R2;Database=DB;UID=MyUser;PWD=pa55word;"  

    'Works Perfectly
    'cn.Open "Provider=SQLOLEDB;Server=remoteServer\SQL2008R2;Database=DB;UID=MyUser;PWD=pa55word;" 

    cn.CommandTimeout = 900
    cn.Close
    Response.write("dfjslkfsl")
%>

The SQL Server I'm trying to connect (from classic ASP Page within my IIS 7 on windows 7) is located on different server in a different network to which I'm connecting using VPN.

I tested sql native client by creating a sql native client System DSN connection to the said Sql server 2008 R2 (which is connected through VPN) from ODBC datasource administrator. And it got connected successfully.

These snaps are from my windows 7 system Appwiz.cpl snap

IIS 7 features

enter image description here

Upvotes: 11

Views: 32306

Answers (4)

Arvind Kumar
Arvind Kumar

Reputation: 11

Works perfectly fine:

"Provider=SQLNCLI;Server=xxxxxxxx;uid=sa;pwd=xxxxxx;database=xxxxxx;"

Upvotes: -2

IsmailS
IsmailS

Reputation: 10863

For users having only Visual Studio 2012 installed can use Provider=SQLNCLI11 as SQLNCLI10 is not available for me on my fresh installed system.

cn.Open "Provider=SQLNCLI11;Server=remoteServer\SQL2008R2;Database=DB;UID=MyUser;PWD=pa55word;"

Upvotes: 4

Shadow Wizard
Shadow Wizard

Reputation: 66388

Try changing the provider to sqlncli10:

cn.Open "Provider=SQLNCLI10;Server=remoteServer\SQL2008R2;Database=DB;UID=MyUser;PWD=pa55word;"

Maybe the name is differet on your machine. :)

Upvotes: 15

Meligy
Meligy

Reputation: 36594

It could be related to the VPN fact. Maybe the app acts as a different user who doesn't have access to the VPN.

Try to turn off anonymous Authentication in IIS and/or make the Application Pool identity be the same user you use to login to the Windows 7 machine and see if this fixes the problem.

Upvotes: 0

Related Questions