SQL 2005/ ASP : ADODB.Connection error '800a0e7a' Provider cannot befound. It may not be properly in

[wowfed]

I have a ASP and a ASP.net(2.0) application running under IIS on a
windows 2003 32 bit OS. Connections to our SQL server 2005 databases
works fine from ASP.Net applications. I can also connect using SQL
query analyzer/ enterprise management studio clients which is
installed on the server.
But we get ADO.DB connection error from our ASP web pages and VB 6
Exe applications.
ADODB.Connection error '800a0e7a' Provider cannot be found. It may
not be properly installed.

I tried both these connection strings from ASP looks like this :

DRIVER=SQL
Server;UID=<user>;PWD=<pwd>;Address=Server,port;DATABASE=mydb;AnsiNPW=No;SERVER=Server,port

Provider=SQLOLEDB;Data Source=server,port;Initial Catalog=mydb;User
Id=<user>;Password=<pwd>;

Earlier I used to get "Microsoft VBScript runtime error '800a01ad'
ActiveX component can't create object" when I tried to create the
instance of the connection object.

Set objConn = Server.CreateObject("ADODB.Connection")

Some one suggested to unregister and re-register msado15.dll.

Now server.createobject works fine. But I started getting this
Provider cannot be found. It may not be properly installed. error when
I open the connection using objConn.Open method.

I also tried creating a file dsn which tests out fine but when I use
it in my connection string, it throws the same error. All the
suggestions I checked online say I need to reinsall mdac 2.8 sp2.
Tried that already but no luck. Help really appreciated.

P.S: This issue is happening only on few of our windows 2003
production servers. On some of the other servers QA/SIT (windows 2003)
and our desktop machines (with XP) the connections works fine.
Configuration is identical and most of these servers were built with
same standard list of softwares.

Also, these windows 2003 servers are outside our DMZ and the firewall
has been opened to SQL servers and connectivity works fine with sql
management studio/ query analyzer and SQL.net providers in asp.net
apps.

Thx.

 

[Mark McGinty]

I have a ASP and a ASP.net(2.0) application running under IIS on a
windows 2003 32 bit OS. Connections to our SQL server 2005 databases
works fine from ASP.Net applications. I can also connect using SQL
query analyzer/ enterprise management studio clients which is
installed on the server.
But we get ADO.DB connection error from our ASP web pages and VB 6
Exe applications.
ADODB.Connection error '800a0e7a' Provider cannot be found. It may
not be properly installed.

I tried both these connection strings from ASP looks like this :

DRIVER=SQL
Server;UID=<user>;PWD=<pwd>;Address=Server,port;DATABASE=mydb;AnsiNPW=No;SERVER=Server,port

Provider=SQLOLEDB;Data Source=server,port;Initial Catalog=mydb;User
Id=<user>;Password=<pwd>;

Earlier I used to get "Microsoft VBScript runtime error '800a01ad'
ActiveX component can't create object" when I tried to create the
instance of the connection object.

Set objConn = Server.CreateObject("ADODB.Connection")

Some one suggested to unregister and re-register msado15.dll.

Now server.createobject works fine. But I started getting this
Provider cannot be found. It may not be properly installed. error when
I open the connection using objConn.Open method.

I also tried creating a file dsn which tests out fine but when I use
it in my connection string, it throws the same error. All the
suggestions I checked online say I need to reinsall mdac 2.8 sp2.
Tried that already but no luck. Help really appreciated.

P.S: This issue is happening only on few of our windows 2003
production servers. On some of the other servers QA/SIT (windows 2003)
and our desktop machines (with XP) the connections works fine.
Configuration is identical and most of these servers were built with
same standard list of softwares.

Also, these windows 2003 servers are outside our DMZ and the firewall
has been opened to SQL servers and connectivity works fine with sql
management studio/ query analyzer and SQL.net providers in asp.net
apps.

Has the SQL 2K5 client connectivity stuff been installed on all of them?

SQLOLEDB should work, but I've seen a few boxes where it's been blown-out
somehow, too. But I haven't ever gotten to the bottom of it, just switched
to using SQLNCLI (SQL Native Client), which is the preferred provider for
2K5.


-MM