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

I had a similar issue with the SQL Server Express Edition on Windows Server 2003. I simply added the network service as a user in the database security.

Answer 2

I was also facing the same issue. Now I have done it by removing the user name and password from the connection string.

Answer 3

When you receive this exception, make sure to expand the detail and look at the inner exception details as it will provide details on why the login failed. In my case the connection string contained a user that did not have access to my database.

Regardless of whether you use Integrated Security (the context of the logged in Windows User) or an individual SQL account, make sure that the user has proper access under 'Security' for the database you are trying to access to prevent this issue.

Answer 4

I copied the database files (.mdf/.ldf) to the App_Data folder to get rid of this exception.

Answer 5

You should see innerException to see what the inner cause of throwing of error is.

In my case, the original error was:

Unable to open the physical file "D:\Projects2\xCU\xCU\App_Data\xCUData_log.ldf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file D:\Projects2\xCU\xCU\App_Data\xCUData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

which solved by giving full permission to current user for accessing related mdf and ldf files using files' properties.

Answer 6

Defining a new Windows Firewall rule for SQL Server (and for port 1433) on the server machine solves this error (if your servername, user login name or password is not wrong in your connection string...).