Classic ASP - Ado Recordset Open()

Question

I am trying to set up a legacy site written in ASP Classic on my local machine. I am using SQL Server 2008 and IIS ver 7.5.

I am running into an error when I try to open a connection to the database.

I used the advice in this post

to use a .udl file to create and test my connection string so I know the connection string works. I am using Windows Authentication and I am certain the app pool that is running this site has access to the database as I use the same pool for multiple sites and they can all connect fine.

When opening the connection I am trying to run a stored procedure, if I just write out the stored proc string and run it directly in my database it works as expected.

Here is the code:

Const adUseClient = 3
Const adCmdStoredProc = 4
Const adOpenStatic = 3
Const adLockBatchOptimistic = 4

dim connectstring,sql
connectstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=my-database;Data Source=(local)\SQLEXPRESS"
sql = "MyProc '" & param1 & "','" & param2 & "'"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic
' this is where it is failing
rs.Open sql, connectstring, 3

One of the problems is the error I get seems to be generated by IIS, it simply says:

An error occurred on the server when processing the URL. Please contact the system administrator.

If you are the system administrator please click here to find out more about this error.

When I click on the "click here" link it takes me to a general page about running ASP Classic sites on IIS 7, I couldn't find anything that seemed relevant to my error though. I can't seem to find where to get information about the error. As a last resort I checked in Event Viewer->Windows applications logs but there was no entry for my errors.

I've Googled and tried multiple permutations on the connection string but to no avail. If anyone had any advice on how to solve this problem, or even an idea of where to look for a solution I would be grateful. Thanks much!

**EDIT: **

Ok I changed IIS settings to show errors in the browser and now I am getting an error message that is a bit more useful, here it is:

Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot open database "rw-gp" requested by the login. The login failed.

Now when I Googled this I found that this can often be a permissions issue. As you can see I am using Windows authentication and I am running the site under an app pool that has permission to connect to the database. I have multiple ASP.NET sites set up locally that run in this app pool and they can connect using integrated security. I'm not sure what is going wrong, does anyone have any suggestions?

This is an ASP/IIS problem, not SQL Server. Also, have you ever heard of SQL Injection caused by string concatenation? Or use of schema qualifiers when referencing objects in SQL Server? — gbn

AnthonyWJones AnthonyWJones · Accepted Answer · 2012-06-05T11:26:01

An ASP Classic script always runs using impersonation (this is different from ASP.NET where impersonation is off by default).

Hence the user that needs access to the DB by virtue of your use of SSPI needs to be the user being impersonated.

The impersonated user is by default the the IUSR account specified as the anonymous user however if you have turned on windows integrated security then user may well by the user account the the client browser is running under. In either case you need to make sure that the user being impersonated has access to the DB or stop using SSPI.

If you are only using anonymous access you can specify via IIS manager that the account to use for anonymous access is the app pools identity.

Classic ASP - Ado Recordset Open()

1 Answers