ASP can't connect to SQL Server database
- by birdus
I'm trying to get a classic ASP application to connect to a local SQL Server 2008 database. The app and database were built by someone else. I'm just trying to get them installed and running on my machine (Windows 7).
I'm getting the following error when when the ASP app tries to connect to the database:
Could not connect to database:
Error Number: -2147467259
Error Message: [ConnectionOpen (Connect()).] does not exist or access denied.
I don't see any messages in the Windows Event Viewer. I'm looking at: Event Viewer-Windows Logs-Application.
It's a fresh database install using a simple restore. The SQL Server install uses the default instance. SQL Server and Windows authentication are both allowed. I left the existing connection string (in the ASP code) in tact and just tried adding that to my SQL Server installation. Here's the connection string:
strConn = "PROVIDER=SQLOLEDB;SERVER=localhost;UID=TheUser;PWD=ThePassword;DATABASE=TheDatabase;"
To add that user to SQL Server, I went to Security/Logins in SSMS and added the user and the password. I selected the database in question as the Default database. I thought that might do the trick, but it didn't. Then, I went into TheDatabase, then went into Security there. I added a new user there, referencing the new user I had already added in server Security. Under Owned Schemas, I clicked db_owner and under Role Members I checked db_accessadmin and db_owner. None of this gave the ASP application access to the database. The sid values match in sys.database_principals and sys.server_principals for the login in question. I am able to login to SSMS using this login.
The app needs to execute selects against the database like this:
oConn.Execute('select * from someTable')
I'm not a DBA and am sort of grasping at straws here. How do I get this thing connected?
Thanks,
Jay
