I'm teaching a class where the curriculum uses ASP 3.0, I thought it would be a good idea to introduce the students to VSNET 2005 web express as an editor and SQL2005 Express for a database. I'm having second thoughts since I can't seem to get ASP to successfully access the database (No problems with ASP.NET)
OS: XP pro
I've created the following in the Database:
- A simple database using managment studio
- ASQL login for the IUSR_ (annonymous) account and the IWAM_ (IIS) account
- Added (IUSR, and IWAM) as database users
- granted datareader and datawriter permissions
- created a simple stored procedure
I've created the following in ASP vbscript:
Page 1
- A connection object
- A command object
- A recordset object
Page 2
- A connection object
- A Recordset object with an adhoc query (not trying to access the SProc here)
IIS
- Enable anonymous (IUSR account)
- Enable Integrated Security
SQL 2005 Surface Area Configuration
- Enabled Local and Remote connections
- using both TCP/IP and named pipes
the code for both is classic ASP/ADO 2.5 (ish)
I've tried several different variations on the connection string with errors varying. Here's a few that have failed:
- CREATE DATABASE permission denied in database 'master'.
"Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WebTest;Data Source=.\SQLEXPRESS;Initial File Name=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WebTest.mdf"
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=database;Data Source=.\SQLEXPRESS;Initial File Name=C:\Inetpub\wwwroot\ClassicASP301\App_Data\Database.mdf"
Log File - Could not attach file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WebTest.mdf' as database 'database'. [CLIENT: 128.222.224.102]
- Response object, ASP 0104 (0x80070057) Operation not Allodwed
server=(local)\SQLExpress;Integrated Security=SSPI;database=WebTest;Provider=SQLNCLI
- Microsoft OLE DB Service Components (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\ClassicASP301\App_Data\Database.mdf;Integrated Security=True;User Instance=True"
I would hate to have to resort to an Access database, any help would be greatly appreciated.
thanks,
pcoelho
SQLExpress ASP3.0 Permissions and Connectivity
ZezeLazo
I tried an Access Data base and actually got the same error. That convinces me that the error is probably related to IIS or folder ACL security. At this point I give up. The class is over and hopefully I will not need to use classic ASP again. My appologies to those of you that are experiencing this issue and looking for a viable resolution.
-pc
Dinesh.s
Colin Reid
If you want to use user instances you have to use SQLNCLI.
You are using a regular database connection and a user instance database connection in the same string. Try something like the below;
"Provider=SQLNCLI;Integrated Security=SSPI;Data Source=.\SQLEXPRESS;Initial File Name=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WebTest.mdf;User Instance=True"
Although I am not sure if user instances are supported in native code.
Javierm
Have you tried using the SQL Express Management Studio to create the databases and then you the connection strings... with out the attach file names. You should be able to test the connections through the ODBC Setup in Control Panel....
Steven Benjamin
I've actually tried both types of databases with all the variations I've listed and few I haven't.
I gave the connection string you suggested a try:
got the following error.
Microsoft SQL Native Client (0x80004005)
Invalid connection string attribute
Thanks for trying.