Error: "The underlying provider failed on Open" in Entity Framework application
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="data source=.\SQLEXPRESS;attachdbfilename=|DataDirectory|\SQLDB.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework"" 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.
![clip_image002[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/22/55/metablogapi/1157.clip_image0024_284FC6A4.jpg "clip_image002[4]")
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="data source=.\SQLEXPRESS;attachdbfilename=D:\SQLDB.mdf;persist security info=True;
user id=saa;password=***;multipleactiveresultsets=True;App=EntityFramework"" 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
Comments
Anonymous
March 07, 2013
I am having different issue. In a load balanced environment on node A app is working fine but on node B I am getting following error.The underlying provider failed on Open. Exception: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) Source: System.Data.Entity Please help.Anonymous
March 07, 2013
This looks like a delegation issue in a double hop scenario. though not sure but I guess this is your architecture-: client===>IIS===>SQL And probably your IIS is load balanced here. If that is the case follow the following steps.
- Domain users, who is logging into the client and running the browser, must not have their domain user accounts marked as "Account is sensitive and cannot be delegated"
- The service account under which the Middle tire SQL instance is running or the iis app pool is running must be “Trust this user for delegation”, configured in Active Directory.
- If the middle tire service(app pool) is running as “NT AUTHORITYSYSTEM” or “NT AUTHORITYNETWORK SERVICE”, the Middle Tire computer must have “trust computer for delegation” checked in Active Directory.
- Middle tier service account (app pool account) should be in the following groups. i. Act as part of operating system. ii. Impersonate a client after authentication.
Anonymous
July 18, 2014
Well I faced the same problem. Open the command prompt as administrator and type the following command - netsh Winsock reset It worked for me.Anonymous
September 01, 2014
Well I faced the same problem. Open the command prompt as administrator and type the following command - netsh Winsock reset It did not for me.Anonymous
April 01, 2016
Thank you your support...It Actually WorksAnonymous
July 11, 2016
I am having the same issue on Windows 7 machine. When I use IIS express there are no issues, however when I use IIS Local I get this problem. Any idea why?Anonymous
April 18, 2017
i am facing same problem, it does not work for me .Anonymous
May 02, 2017
error during system tree load![TbrillantpackageManager.E01] The underlying provider failed to openAnonymous
September 18, 2017
Hi great article.I am using vs 2017 and localbd as backend, I want to know whether localdb database will work on client pc without installing SQL server? Because I also getting same error at client side after installing my Windows forms application.