[Fix]-MSSQL Error The underlying provider failed on Open
Scenario:
A Windows application with Entity Framework and SQL Express backend was trying to save data to .mdf file on the file system.
When the application is executed by adding data to the .mdf file, the below error occurs:
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 …
The connection string used is indicated 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=|DataDirectory|\SQLDB.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
Reason:
Windows Authentication with user instance is used to connect to SQL server is the main cause of the issue.
Fix 1:
Try enabling "Allow Remote Clients" in DTC config.
In windows 7 the DTC config can be opened by running dcomcnfg. Under Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Right click to Local DTC -> Properties -> Security -> select the option Network DTC Access -> select the option Allow Remote clients as shown below:
Fix 2:
Database service is in Stopped state, go to services list and start it.
Fix 3:
Data Source attributes with SQL authentication may have invalid credentials. Check and enter the correct username and password could be wrong.
Fix 4:
In either case of Windows or SQL Authentication ensure that the user has proper access under 'Security' for the database.
Fix 5:
Simply add the network service as a user in the database security.
Fix 6:
Enabling DTC security settings.
Go to Properties of DTC as mentioned in Fix1. Under Security Tab, select the below options:
- Network DTC Access
- Transaction Manager Communication
- Allow Inbound
- Allow Outbound
Fix 7:
Reset IIS, even in case of using Integrated Security in the connection string.
Fix 8:
Remove the following from the Connection String:
persist security info=True
Fix 9:
With "Integrated Security=True;" in the connection string run the application pool identity under "localsystem". This surely is not recommended but for testing purposes give it a try.
Follow the below link to change the identity in IIS:
http://www.iis.net/learn/manage/configuring-security/application-pool-identities
In IIS set the App Pool Identity as Service Account user or Administrator Account or ant account which has permission to do the operation on that DataBase.
Check the connection string in web.config where Data Source=localhost is present. This could be the cause for the error, change it as “DOMAIN_Name\MACHINE_Name”.
Fix 10:
The connection string has a value “user Instance=true”. Removing it clears the error.
- The user instance executes in the SQL context and won’t have permissions on the .mdf and .ldf files.
- Also the user instance to databases have an Auto Close option set. If there are no connections to a database for about 8-10 minutes, this Auto Close automatically shuts downs the database.
Hence it is advisable to remove “user Instance=true” settings from connection string.
Fix 11:
- Open the command prompt with “Run as administrator” and type the command as netsh Winsock reset
- Restart your system and try again.
Fix 12:
Step 1: Open Internet Information Service Manager.
Step 2: Click on Application Pools in the navigation tree.
Step 3: Select the version Pool for your ASP .Net version. Or else select DefaultAppPool which is common for all versions.
Step 4: Right click AppPool selected in previous step and select advanced settings.
Step 5 :In the properties window select Identity and click the button to change the value.
Step 6: Set Local System for built-in accounts and click ok.
