My name is Archana CM from Microsoft SQL Developer Support team, we support many data access technologies including Entity Framework, SSIS.

I had chance to work with developer who was having issues in his Entity Framework, one of the issue was while adding data to .mdf file which was on file system.

In today's blog I am sharing my experience on how we could resolve the issue for him and what issues he was facing.

It was Windows application and Entity Framework was used. As a backend SQLExpress was used in his application and he was saving data to .mdf file which was on file system.

When we executed the application and while trying to add data to .mdf file we could see below error message

 

Message: The underlying provider failed on Open.

Stack trace : at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)

at System.Data.EntityClient.EntityConnection.Open()

at System.Data.Objects.ObjectContext.EnsureConnection()

at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

at System.Data.Objects.ObjectContext.SaveChanges()

at EFLenoard.DataMgr.AddFacility(String name, String address, String city) in D:\Research\EFParentChildInsert\EFParentChildInsert2010\DataMgr.cs:line 35

Inner Exception : InnerException = {"An attempt to attach an auto-named database for file D:\\Research\\EFParentChildInsert\\EFParentChildInsert2010\\bin\\Debug\\SplDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC shared …

Here is the connection string that was used, while issue was occurring.

<connectionStrings>

<add name="SQLDBEntities" connectionString="metadata=res://*/SplDBModel.csdl|res://*/SplDBModel.ssdl|res://*/SplDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;attachdbfilename=|DataDirectory|\SQLDB.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>

In this case, Windows authentication with user instance was used to connect to SQL server which was mainly causing the issue.

In order to resolve the above issue, we had 2 solutions

Solution 1:

In the existing connection string to remove the “user Instance=true” and it works.

Probable cause of the issue could be as below:

• The user instance cannot attach the database because the user does not have the required permissions. The user instance executes in the context of the user who opened the connection—not the normal SQL Server service account. The user who opened the user instance connection must have write permissions on the .mdf and .ldf files that are specified in the AttachDbFilename option of the connection string.

• Another common issue is when you open a database file successfully when the database is attached to the SQL Server Express instance, but fails when you try to open it from the Visual Studio IDE. This might occur because the SQL Server Express instance is running as "NT AUTHORITY\NETWORK SERVICE," while the IDE is running as windows account. Therefore, the permissions may not work.

• A variation of this issue is when the user that opens the user instance connection has read permissions on the database files but does not have write permissions. If you get a message saying that the database is opened as read only, you need to change the permissions on the database file.

• 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. There are two common ways to close database files, as follows.
  • User instance databases have the Auto Close option set so that if there are no connections to a database for 8-10 minutes, the database shuts down and the file is closed. This happens automatically, but it can take a while, especially if connection pooling is enabled for your connections.
  • Detaching the database from the instance by calling sp_detach_db will close the file. This is the method Visual Studio uses to ensure that the database file is closed when the IDE switches between user instances. For example, you are using the IDE to design a data-enabled Web page. You press F5 to run the application. The IDE detaches the database so that ASP.NET can open the database files. If you leave the database attached to the IDE and try to run the ASP page from your browser, ASP.NET cannot open the database because the file is still in use by the IDE.

Solution 2:

We created new connection to database with SQL Authentication as a workaround.

Thus connection string turns out to be as below.

<connectionStrings>

<add name="SQLDBEntities" connectionString="metadata=res://*/SplDBModel.csdl|res://*/SplDBModel.ssdl|res://*/SplDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;attachdbfilename=D:\SQLDB.mdf;persist security info=True;

user id=saa;password=***;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>

Once applying either of these solutions, with below code we could save data to .mdf file which was on file system. By attached the .mdf file to SQL Server data can be confirmed.

 

public class DatatoMDFOnFileSystem

{

private SQLDBEntities _sqlDataContext = new SQLDBEntities();

private string _errorMessage;

public long AddDatatoMDFOnFileSystem(string name, string address, string city)

{

_errorMessage = String.Empty;

try

{

string connectString = ConfigurationManager.ConnectionStrings["SQLDBEntities"].ToString();

using (_sqlDataContext = new SQLDBEntities(connectString))

{

EFDatatoMDFOnFileSystem efDatatoMDFOnFileSystem = EFDatatoMDFOnFileSystem.CreateEFDatatoMDFOnFileSystem(0, name, address, city);

if (efDatatoMDFOnFileSystem != null)

{

_sqlDataContext.AddToEFDatatoMDFOnFileSystem(efDatatoMDFOnFileSystem);

_sqlDataContext.SaveChanges();

id = efDatatoMDFOnFileSystem.Id;

Console.WriteLine(String.Format("Record added with id, {0}.", id));

}

}

}

catch (Exception err)

{

_errorMessage = err.Message;

}

finally

{

Console.WriteLine("Error Message, {0}.", _errorMessage);

}

return id;

}

}

Happy Coding!!!!

 

Author : Archana , SQL Developer Engineer , Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft