Error with classic asp database connection after migration of website from IIS6 to IIS7

2
votes

I have a classic asp site which has the following database connection:

<%
set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Driver={SQL Server};Server=123.456.789.012;Database=mydatabase;Uid=myuser;Pwd=123xyz;"
objConnection.Open
%>

The site has just been moved from a Windows 2003 server running IIS6 to a Windows 2008 server running IIS7 (both using MSSQL 2005 Express database) and it now gives an error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
/inc/dbconnection.asp, line 4

I'm assuming it's because there's a different driver with IIS7? What would be the updated connection string to get this to work - I've googled and just not found anything that works or makes sense to me (I'm at the edge of my understanding here to be honest!)?

Many thanks for any pointers folks...

2
sql-serversql-server-2005iis-7asp-classicadodb
you might have to add the instance name to the servername stackoverflow.com/questions/843864/… – Flakes
Did you try to disable Windows Firewall or add SQL server to allowed program list? When I activated Windows Firewall, I can not connect to sql database. So I added SQL Server to allowed programs list, and my application worked.. – Salih

2 Answers

4
votes

You are trying to call the ODBC driver for SQL Server, which was traditionally provided with MDAC. Rather than try to troubleshoot that in IIS7, I would probably use one of the SQL Server Native Client drivers, as in:

"Driver={SQL Native Client};..."

or

"Provider=SQLNCLI10;..."

Some history can be found at MSDN, and other connection string options and info can be found at the great site, connectionstrings.com.

0
votes

If the database is on the same server as the website, and the MSSQL 2005 Express database isn't a named instance, you could use the following Data Source:

<%
set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=mydatabase; User ID=myuser;"
objConnection.Open
%>

If you've installed MSSQL 2005 Express locally using the default instance name of "sqlexpress" then you'll need to use the following Data Source

<%
set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=SQLOLEDB; Data Source=.\sqlexpress; Initial Catalog=mydatabase; User ID=myuser;"
objConnection.Open
%>