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

Question

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

• Windows 7
• IIS 7
• Classic ASP page (.asp)

Answer 1

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