ADO.NET Entity Framework – Troubleshooting steps for “The underlying provider failed on Open”

  • Article

 

  Finding root cause for data connectivity issues
  The ADO.NET Entity Framework shields you from many problems. But to troubleshoot connection errors, this can slow down progress. I developed a technique that can very accurately tell you the root cause. The technique is very simple – use the SQLConnection() and SqlCommand() objects because they often provide the most direct route to the underlying root cause.
  You will need to put your own connection string and select statement.

Description: Some ADO.NET code to test a connection 

   string conn = "Data Source=.;Initial Catalog=MarketIndexData;Integrated Security=True;";  using (SqlConnection connection = new SqlConnection(conn))  {    try    {      connection.Open();      SqlCommand cmd = new SqlCommand();      cmd.CommandText = "select * from AssetPrices";      cmd.Connection = connection;      SqlDataReader rdr = cmd.ExecuteReader();      while (rdr.Read())      {      }      catch (Exception ex)      {       string s = ex.Message;      }  }

Figure: Low Level ADO.NET Code

Place the code in your code behind of your webform or mvc app
 
Just simple ADO.NET code
image
  Most common error is insufficient privileges
  In my case, the problem turned out to be an issue with the [NT AUTHORITY\NETWORK SERVICE] account. When I ran my MVC application, the application pool identity runs as [NT AUTHORITY\NETWORK SERVICE] and it is that account that talks to SQL Server. The steps below show how I solved that problem.
 

Make sure you have [NT AUTHORITY\NETWORK SERVICE] as an account. If you don’t, right mouse click on “Logins” and add it.

clip_image002

Figure Logins in SSMS

Go to “User Mapping.” Notice that I was way too liberal. I chose all my databases and gave multiple roles to each database. But then again, there is no production data here anywhere.

clip_image004

Figure: User Mapping – SQL Server

Type “inetmgr” in the run window. Click on “Application Pools.” Notice that the identity is “Network Service.”

What is the funky app pool identity running as “NetworkService?”

Looks to me that that when I installed Azure SDK 1.3, the system added a new app pool for my cloud apps to run in in the local development fabric.

clip_image006

Figure Internet Information Manager (inetmgr.exe)

Start SQL Server Management Studio and grant privileges to the newly added “Network Service” account.

clip_image002[4]

Figure: SQL Server Management Studio