MSSQL Error 'The underlying provider failed on

Question

I was using an .mdf for connecting to a database and entityClient. Now I want to change the connection string so that there will be no .mdf file.

Is the following connectionString correct?

<connectionStrings>
   <!--<add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;AttachDbFilename=|DataDirectory|\NData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />-->
   <add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;Initial Catalog=NData;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

Because I always get the error:

The underlying provider failed on Open

Answer 1

This is common issue only. Even I have faced this issue. On the development machine, configured with Windows authentication, it is worked perfectly:

<add name="ShoppingCartAdminEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQlExpress;initial catalog=ShoppingCartAdmin;Integrated Security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Once hosted in IIS with the same configuration, I got this error:

The underlying provider failed on Open

It was solved changing connectionString in the configuration file:

<add name="MyEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MACHINE_Name\SQlExpress;initial catalog=ShoppingCartAdmin;persist security info=True;user id=sa;password=notmyrealpassword;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Other common mistakes could be:

1. Database service could be stopped
2. Data Source attributes pointing to a local database with Windows authentication and hosted in IIS
3. Username and password could be wrong.

Answer 2

I had a similar issue with exceptions due to the connection state, then I realized I had my domain service class variable marked as static (by mistake).

My guess is that once the service library is loaded into memory, each new call ends up using the same static variable value (domain service instance), causing conflicts via the connection state.

I think also that each client call resulted in a new thread, so multiple threads accessing the same domain service instance equated to a train wreck.

Answer 3

I posted a similar issue here, working with a SQL 2012 db hosted on Amazon RDS. The problem was in the connection string - I had "Application Name" and "App" properties in there. Once I removed those, it worked.

Entity Framework 5 and Amazon RDS - "The underlying provider failed on Open."

Answer 4

I got rid of this by resetting IIS, but still using Integrated Authentication in the connection string.

Answer 5

I had this error suddenly happen out of the blue on one of our sites. In my case, it turned out that the SQL user's password had expired! Unticking the password expiration box in SQL Server Management Studio did the trick!

Answer 6

I had a similar error with the inner exception as below:

operation is not valid for the state of the transaction

I could resolve it by enabling DTC security settings.

Go To Properties of DTC, under Security Tab, check the below

• Network DTC Access
• Allow RemoteClients
• Transaction Manager Communication
• Allow Inbound
• Allow Outbound