Recordset Paging works in IIS6 but not in IIIS7

Question

Hi there,

I've got a recordset/paging set up - works fine in IIS6 but when I run the site on an IIS7 server I get the following error:

    Microsoft OLE DB Provider for SQL Server  error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

/orders.asp, line 197

the code looks like this:

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

objPagingConn.Open CONN_STRING

Set objPagingRS = Server.CreateObject("ADODB.Recordset") objPagingRS.PageSize = iPageSize objPagingRS.CacheSize = iPageSize objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly, adCmdText

iPageCount = objPagingRS.PageCount iRecordCount = objPagingRS.RecordCount

Line 197 is the objPagingConn,Open ... line. I've got about 10 sites like this to migrate - is there a simple fix in IIS7???

Help is greatly appreciated! Many thanks, Martin

Answer 1

Sounds like you lost permissions to your sql server when you changed over. What is your connection string? Are you trying to use a trusted connection? If so, perhaps IIS7 is running under a different account.

Answer 2

Take a look at this MSDN article:

You may receive this error message when the following conditions are true:

• SQL Server 2005 or SQL Server 2000 is installed on a cluster.
• You are connecting to a SQL Server named instance by using TCP/IP sockets.
• IPSec policy is enabled on the client domain.
• IPSec policy is not enabled on the server domain.

They reccomend updating your connection string with the port # or named pipe name:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=clientID;Data Source=tcp:TcpIpAddress,port

Answer 3

After many, many experiments I found a connection string that worked:

Provider=SQLOLEDB.1;Server=123.123.123.123,1433;Initial Catalog=mydb;Persist Security Info=True;User ID=sqladmin;Password=password;

I hope this helps someone else who comes across the same problem - it's been bugging me for so long!