|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
Figure: Low Level ADO.NET Code
|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.
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.
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.
Figure Internet Information Manager (inetmgr.exe)
Start SQL Server Management Studio and grant privileges to the newly added “Network Service” account.
Figure: SQL Server Management Studio