Entity Framework Login Failed Error

Question

I have hosted my WCF application in IIS and set an app pool with 4.0 integrated. I configured the pool identity as network service. I have already checked other posts related to this issue but could not resolve it.

I get the below exception

System.Data.Entity.Core.EntityException was unhandled by user code


HResult=-2146233087
  Message=The underlying provider failed on Open.
  Source=EntityFramework

I tried modifying app pool to localsystem from network service, it works fine. Any guess why it takes my system name as login in the earlier case?

Answer 1

The problem has nothing to do with the Entities Framework. You cannot connect to your SQL Server. I would check these things:

• Ensure that SQL Server is up and running. Check your running services to see if SQL Server service is up.

• I would check the SQL Server Configuration Manager to see if the TCP/IP communication is enabled.

• I would check my firewall settings. Perhaps something is in the way between SQL Server and the client.

• I would check if another application from the same client can connect to the same server. Then I would check the connection string differences.

• If all of the above were playing correctly, then I would check the privileges on the specific SQL Server, on the specific database, for the account I am trying to connect with. Allow everyone to use this database as a first step to check if it is an account-permissions problem. Using this specific configuration, (Integrated Security=true) means that your username and password are ignored. If you want to access the SQL server using the specific username and password, you should omit the specific statement in the connection string, or set it to false.

You could check this connection strings example post.

Hope I helped!

Answer 2

In your connection string, you have Integrated Security set to "true". Most likely this is resulting in the user name and password being ignored. The identity running the App Pool will be used to attempt the login.

Two ways to resolve this:

1. Set Integrated Security to "false".

2. Use an account (the one in the connection string or one created) to run the App Pool, and leave Integrated Security to "true". For this to work, the account running the App Pool will need login permissions (plus any other permissions like EXECUTE or SELECT, if needed) to the SQL server instance.

We do this at work with both EF and good plain old ADO.NET. We create service accounts that we use to run the App Pool, and the service account is granted the necessary login and other permissions. You must also enable SQL Server to accept Windows Authentication.

Answer 3

It was stupid mistake!! There was no NT AUTHORITY\NETWORK SERVICE account in my SQL server logins. I have added it with required permissions. It now works with my configuration of app pool to Network service and Integrated security=true in connection string.