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 Appwiz.cpl snap

IIS 7 features

enter image description here

Windows 7
IIS 7
Classic ASP page (.asp)

@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, Mar 11 '11 at 12:47
this may help you http://www.connectionstrings.com/sql-server-2008 — Dee, Mar 07 '11 at 16:38

score 15 · Accepted Answer · answered Mar 11 '11 at 19:10

15

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

answered Mar 11 '11 at 19:10

Shadow The GPT Wizard

66,030
26
140
208

This answer saved me a ton of time – chrismay Sep 24 '11 at 01:32
I used this question and answer to help understand the switch from SQLOLEDB to SQLNCLI10 in some Excel VBA code. Now my SQL "With" statement works. – Doug Glancy Jan 04 '12 at 22:34

score 4 · Answer 2 · answered Oct 11 '12 at 06:42

4

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

answered Oct 11 '12 at 06:42

IsmailS

10,797
21
82
134

score 0 · Answer 3 · answered Mar 07 '11 at 05:33

0

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.

answered Mar 07 '11 at 05:33

Meligy

35,654
11
85
109

+1 for nice pointers. Let me try them out tonight when I go home. Right now I'm in office network itself and using other system on which it works fine. – IsmailS Mar 07 '11 at 10:34

score -2 · Answer 4 · edited Jul 14 '15 at 04:58

-2

Works perfectly fine:

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

edited Jul 14 '15 at 04:58

Ajay2707

5,690
6
40
58

answered Jul 13 '15 at 10:05

Arvind Kumar

11

1

why are you copying the accepted answer (to several questions)? – kleopatra Jul 13 '15 at 10:18

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

4 Answers4

Linked