Howto use MSSQL 2005 Express database .mdf in app_data folder?

[Deltafox]

Many hours i have spend trying to get this work, how should i do it?

1) I download a starterkit for ASP.NET 2.0 that uses a MSSQL 2005 Express database: http://msdn.microsoft.com/vstudio/downloads/starterkits/

2) Open it with Visual Studio

3) Then compile and upload

4) I use this connection string:

Data Source=.\SQLExpress;
Integrated Security=True;
AttachDBFilename=|DataDirectory|TimeTracker.mdf;
User Instance=false

(do not use user instances in shared hosting environment)

5) So my database is in /httpdocs/app_data/TimeTracker.mdf, this database needs to be automatically attached to MSSQL 2005 Express database. But it won't, getting this error:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\domainname.nl\httpdocs\App_Data\TimeTracker.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

- there is no database with the same name

How should i do this right?

Thank you!

 

[Gris@]

MSSQL 2005 works under Network Service account by default.
Check that it has access to your file.
In any case you may use Filemon from Sysinternals

 

[Ultima Hosts]

Hi there,

This won't work and user instances are not meant to be used in a shared environment. The host has to put the domain in the local system security group (not good!) for user instances to work. You can attach your DB's straight onto a SQL server so ask your host to attach them for you.

Cheers,

Mark

 

[Deltafox]

Originally posted by Gris
MSSQL 2005 works under Network Service account by default.
Check that it has access to your file.
In any case you may use Filemon from Sysinternals

Hey Gris, when i give NETWORK SERVICE account access the error is the same. I tried filemon but there are no access denied messages.

Originally posted by Ultima Hosts
Hi there,

This won't work and user instances are not meant to be used in a shared environment. The host has to put the domain in the local system security group (not good!) for user instances to work. You can attach your DB's straight onto a SQL server so ask your host to attach them for you.

Cheers,

Mark

Hey Mark, i'm not using user instances because i don't want customers to see each others databases. I also don't want to manually attach databases for customers al the time. When they upload the mdf file it should automatically attach to the database (thats the cool new feature of sql2005).

Right now swsoft is on it...

 

[Ultima Hosts]

Hi there,

You will have to manually attach the databases if you are not using user instances. It is the user instances that makes the auto attach possible so I think its a bit of a catch 22 scenario. If you pop the websites application pool into the local system security setttings this will allow user instances to work but put your OS at risk.

MyLittleAdmin has a tool for attaching databases but I haven't had a chance to see if this could work in a shared host environment.

Its a great feature to have for clients I agree as we actually do it manually but it is time intensive.

Cheers,

Mark

 

[Deltafox]

Hey Mark / Ultima hosts,

What version of sql 2005 express are you using?

SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services?

see http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

Did you choose to install user instances?

Thnx

Paul

 

[Ultima Hosts]

Hi DeltaFox,

We are not currently running SQL Express. Initially we did run it and allowed user instances but decided that the security risk was too high. We then moved to the advanced services model and attached databases for awhile. Now we run a dedicated SQL Server box with the WG Edition. We could have run SQL Express AS edition but it doesn't allow for copy database which is very handy for a hoster.

Cheers,

Mark