Problems attaching to database from VWD

Hi Mike,

I hope you can help me with my SQL Server Express problem. I have a database that was created in VWD Express 2005, that is currently in C:\web projects\project name\App data...etc. I have been able to connect to and use the database with an application that I am building as a learning exercise, ( I'm a complete newbie to .NET and SQL Server) but now I can't connect to the database from SQL Server manager.

I have tried attaching the file which has worked a few times, but now that won't work either. I keep getting a message that the file can't be found or I do not have sufficient permissions to access it. I can connnect to the file fine from within VWD, but because of the above problem I can't now run the application (it throws an exception).

I suspect that I've got some of the basic configuration settings wrong, but I really don't know where to start looking. Below is an example of one of the messages I've been getting

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
CREATE FILE encountered operating system error 32(error not found) while attempting to open or create the physical file 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf'. (.Net SqlClient Data Provider)

Any help would be very gratefully recieved

regards

jon

Hi Jon,

I split this into a new thread, you added it to the bottom of an unrelated question where it would have gotten lost.

The first problem I see is that you're said your file was located at C:\web projects\project name\App data, but the error says it's looking for the file at 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf', these are two different directories. I'm not sure where the difference comes from.

The real problem is probably related to User Instances. When you create a database in VWD, it's going to set it up to use a User Instance, which is a special process of SQL Server that is started just for the application calling your database. Since a database file can only be attached to a single instance of SQL Server at a time, you can't attach the database to the parent instance, which is what you are looking at in Management Studio, while it is being used by the User Instance that is started by your application.

The second likely issue is that SQL Express is running as Network Service, which is a low privelelge account that does not have permissions to the directory where your database is located. SQL Express can not attach to database files in folders that it doesn't have permissions for. You would need to set the folder permissions to allow tje Network Service account access to the database file.

For the record, the way the VS Express products, such as VWD Express, are designed to work with SQL Express pretty much assumes that you will do all your database manipulation using the VWD interface. Trying to use Managment Studio in combination with VWD Express can cause some odd behavior because of this. You should read the white paper on User Instances to gain a better understanding about what this is as it is what's going to cause you the most confusion if you try to use these two products together.

Mike

|||

Thank you very much for your help with this issue, I will read the White paper that you suggested and hopefully that will clear a few things up. I didn't realise that VWD and VS Express work so independantly of each other, and you are right, the behavior has been odd and inconsistent, which added to my confusion.

Thanks again for your help,

Jon

|||Hello Mike,

> The second likely issue is that SQL Express is running as Network Service, which is a low privelelge
> account that does not have permissions to the directory where your database is located. SQL Express
> can not attach to database files in folders that it doesn't have permissions for. You would need to set
> the folder permissions to allow tje Network Service account access to the database file.

have found this:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx
See headline Detaching and Attaching a Database.

I have written a little visual basic 2005 test program and found the following.

There is a SQL Server login for the standard user which I used mapped to the sysadmin server role.

1.
The .mdf file is in a subdirectory of the all users profile directory.
Network service has no permissions on the .mdf file:

Windows authentication:

Logged on as administrator:
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

Logged on as standard user:
The standard user himself did copy the file in the directory.
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file read only.

Logged on as standard user.
The standard user himseld did copy the file in the directory.
sp_attach_db attaches the .mdf file read only.

2.
The .mdf file is in the Data Directory of SQL Server.
Network service has full permissions on the .mdf file:

Windows authentication:

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
sp_attach_db ends with the message Access denied.

So it seems that a standard user can't run a program which calls an attach.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
There is a login for the user mapped to the sysadmin server role.
sp_attach_db attaches the .mdf file correctly.

Here this worked what did not with Windows Authentication.

|||Supplement:

To Point 1.
After attaching a .mdf file which is in i a subdirectory of the all users profile directory:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

After detaching the Database with the Management Studio i tried again:

Windows authentication:
Logged on as standard user:
Access denied.

SQL Server server authentication:
(SA was the user in the connection string.)
Logged on as standard user.
sp_attach_db attaches the .mdf correctly.

|||

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop (name of database)
EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.