Sharing a SQLExpress database between EF and SqlRoleProvider

Question

I have a database in SQLExpress 2008 that I am accessing via an EF4 connection. It also contains the ASP.Net role provider tables for the website. In the web.config there are two separate connection strings pointing to the same database. When it comes to debugging the database I get the following error:

Unable to open the physical file "D:\TFS\Main\Source\TestWeb\TestWeb\App_Data\TestDB.mdf". Operating system error 32: "32(failed to retrieve text for this error. Reason: 15105)". An attempt to attach an auto-named database for file D:\TFS\Main\Source\TestWeb\TestWeb\App_Data\TestDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

What I assume this means is that the two different connection strings cannot both attach to the SQLExpress database at the same time?

Here are the connection strings:

<add name="SqlRoleManagerConnection"
     connectionString="data source=.\SQLEXPRESS;
     Integrated Security=true;AttachDBFilename=|DataDirectory|TestDB.mdf;
     User Instance=false;MultipleActiveResultSets=True"/>

<add name="TestDBEntities"
     connectionString="metadata=res://*/TestDB.csdl|res://*/TestDB.ssdl|res://*/TestDB.msl;
     provider=System.Data.SqlClient;
     provider connection string=&quot;Data Source=.\SQLEXPRESS;
     AttachDbFilename=|DataDirectory|\TestDB.mdf;
     Integrated Security=True;User Instance=false;
     MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Missing open angle bracket on each connection string is deliberate here and is because of a limitation of SO.

Any ideas how I can get around this?

Answer 1

I have the same situation: EF and Role provider connected to the same database and two connection strings as well. But I don't have "AttachDBFilename..." in my ConnectionStrings but instead in the RoleManager ConnectionString Database=MyDb and in the EF ConnectionString Initial Catalog=MyDb. In SQL Server Management Studio I have attached my physical Db to the object explorer and named MyDb. This works. Don't ask me why. Regarding connection strings I am on the level of "let the wizards do the stuff". They have created the strings this way.