ASP.NET app wont share database with Sql Server Management Studio Express

Question

I have an ASP.NET 3.5 app with a SQL 2005 Express .mdf database file in its App_Data folder. Lets call that Foo.mdf. On its own the ASP.NET app can connect to the database with no problems. So far so good.

Then, if I install Sql Server Management Studio Express on the server and start it, it finds the local SQLEXPRESS instance and connects to it, but doesn't show Foo.mdf in the list of databases.

So I try and Attach Foo.mdf by right-clicking on databases and selecting Attach.. and finding the .MDF. This fails with a vague error:

CREATE FILE encountered operating system error 32(error not found)  
while attempting to open or create the physical file
'C:\inetpub\wwwroot\FooApp\App_Data\Foo.mdf'. (Microsoft SQL Server, Error: 5123)

If I stop my ASP.NET site in IIS, then the attach does work, but then when I restart my ASP.NET app, it can't connect to Foo.mdf.

So it looks like SQL Express 2005 will only let either my app or Management Studio connect. This seems really crap - surely Sql Express should allow more than one connection? I was expecting it to behave like regular Sql Server.

Hopefully I'm doing it wrong. Please advise.

Answer 1

Can you check your application connection string and see if it has User Instance=true

eg :

Data Source=.\SQLEXPRESS;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;

From MSDN : The other main issue with user instances occurs because SQL Server opens database files with exclusive access. This is necessary because SQL Server manages the locking of the database data in its memory. Thus, if more than one SQL Server instance has the same file open, there is the potential for data corruption. If two different user instances use the same database file, one instance must close the file before the other instance can open it.

If that is the issue, then you can attach the database in SQL Server Management Studio and modify the connection strings as follows so that multiple clients can connect to the same database.

Server=.\SQLEXPRESS;Database=myDataBase;Trusted_Connection=True;

If this isnt the issue in your case, also check out the "Common Issues" section at the link above. It could be related to permissions as per points 1 / 2 in the "Common Issues" section

Answer 2

I don't have any experience using the default ASP.NET database, but have you tried moving the MDF to your SQL Express DATA folder? (default: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA) Once in the folder you can use the Management Studio to attach the file to your SQL Express instance. You'll want to modify your connection string in your web.config file to look like this:

<connectionStrings>
    <add name="ApplicationServices"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=aspnetdb"
     providerName="System.Data.SqlClient" />
</connectionStrings>