SQL Server mdf file cannot be connected by both ASP.NET application and SQL Server Management Studio

Question

I have a simple ASP.NET website application. Using the new Entity Framework model-first approach, I created a database (.mdf file). However, whenever the application is running, SSMS cannot connect to the database (error 5123 or 5120), and vice versa.

Is this normal? Is there a way to get around this?

I have tried to run SSMS as Administrator, but that did not seem to help.

I am using VS 2012 Professional and SQL Server 2012 Express.

Here is the connection string of the app.

<connectionStrings>
   <add name="MarketingContainer" 
        connectionString="metadata=res://*/App_Code.Marketing.csdl|res://*/App_Code.Marketing.ssdl|res://*/App_Code.Marketing.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012ADV\MSSQL\DATA\MarketingDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
        providerName="System.Data.EntityClient"/>
  </connectionStrings>

<connectionStrings> <add name="MarketingContainer" connectionString="metadata=res://*/App_Code.Marketing.csdl|res://*/App_Code.Marketing.ssdl|res://*/App_Code.Marketing.msl;provider=System.Data.SqlClient;provider connection string="data source=(LocalDB)\v11.0;attachdbfilename=C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012ADV\MSSQL\DATA\MarketingDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"/> </connectionStrings> — delta2006
– delta2006, Commented Jan 10, 2013 at 10:50

Max · Accepted Answer · 2013-01-10 11:55:17Z

2

When you use AttachDbFileName you place a lock to the file and use as single user mode and only one connection can use the database.

For use the database between Visual Studio and SQL Server you must: 1. Attach the database (not the file) to SQL Server 2. Use the connection string to point to SQL Server

answered Jan 10, 2013 at 11:55

Max

7,1503 gold badges51 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

delta2006 Over a year ago

i understand what you said. however, can you give a concrete recommendation of how you would implement what you said? thanks,

delta2006 · Accepted Answer · 2013-01-16 22:47:22Z

Max answer is in principle correct. I have now implemented his answer, using this connection string

add name="MarketingContainer" connectionString="metadata=res://*/App_Code.Marketing.csdl|res://*/App_Code.Marketing.ssdl|res://*/App_Code.Marketing.msl;provider=System.Data.SqlClient;provider connection string="data source=SQLSERVERNAME;initial catalog=DATABASENAME;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"

I should note that the reason that my problem arose in the first place was this. I use VS2012 to create an entity data model (edmx file). after I get the model to where I want it, I generate the database from model. at that point, the Generate Database Wizard shows up. New Connection. Change Data Source. I choose Microsoft SQL Server Database File (because I only had a mdf file at that point). If you do it this way, you end up with the application using LocalDB, and Attachdbfilename locks the file as Max said. However, what I should have done is first use SSMS to attach to the mdf file, and then rename the database as needed. and then in VS2012, Change Data Source, select Microsoft SQL Server instead. and now, both VS2012 and SSMS can access the database.

Collectives™ on Stack Overflow

SQL Server mdf file cannot be connected by both ASP.NET application and SQL Server Management Studio

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related