Problem using SQLOLEDB in connection string 1

[IsobelRae]

I have various web sites using ASP and MSSQL 2000 databases.
I use the following syntax for setting the connection string in global.asa
"Provider=SQLOLEDB; Data_Source=(name of SQL server instance); Initial Catalog=Northwind; User Id=sa; Password=;"

Now this approach seems to work fine when the computer which hosts the web site (using IIS) also has MSSQL server 2000 installed - in fact I have many different examples of my code working - including examples where the SQL server instance is not on the same server as the web site. The problem comes when I move the web site to a brand new server without MSSQL installed, which needs to access a database on another server where MSSQL is installed. This creates the error [ODBC Error]data source not found or no driver specified. Now it is NOT ODBC - this is SQLOLEDB. So I think to myself - install client utility on the web server. I did that. Then I installed MDAC 2.7 on the web server. Still the same error. Anyone else encountered problems like this ?
I do have ISA server on the web server, but this should not be a complication as I am only trialling the site via 'localhost' at the moment.

 

[DreXor]

verify your IUSR_MachineName rights between the dataserv and web serv

also verify that you have local network access between the two machines in case of improper networking etc..

seems as if the datasource is not failing, but the machines are not 'seeing' each other or allowing each other to see each other

other things to check are norton script blocking and possible software firewalls like zonealarm

just random thoughts, but hope the info helps

 

[IsobelRae]

Thank you that is useful. I think it may be that the SQL Server server may not be 'seen' by Active Directory. I am going to follow up this theory, instead of pursuing the idea that MDAC is the problem - very grateful for the pointer - Isobel

 

[DreXor]

no problem, let me know if this ends up being a dead end we can look into more possibilities