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

11
votes

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

  • Windows 7
  • IIS 7
  • Classic ASP page (.asp)
4
sql-server-2008iis-7asp-classicconnectionsql-server-native-client
this may help you connectionstrings.com/sql-server-2008 – Dee
Might sound silly but have you tried provider=sqlncli10? – Shadow Wizard Hates Omicron
@Shadow thats not silly. It solved my problem :). I had provider=sqlncli in my code. Thanks very much. You can put it as answer if you want to earn 25 reputation points from me. – IsmailS
Cheers @Ismail, done that. Glad it worked! :) – Shadow Wizard Hates Omicron
I thought SQL 2008 was Native Client 10 ? – BerggreenDK

4 Answers

15
votes

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. :)

4
votes

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;"
0
votes

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.

-2
votes

Works perfectly fine:

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