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?

Solution 1

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.

Solution 2

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!

Solution 3

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.

Comments

[Tim]

You have integrated security set to true in your connection string, which may be taking precedence over the user name and password. Try running the App Pool under your credentials (or better yet, create a service account with a login to the SQL server and run the App Pool under that service account, with integrated security set to true). The other option is to set integrated security to false.

[Tim]

These are good things to check, but in this case I believe the account running the App Pool does not have sufficient permissions to login to the SQL Server. Note the Integrated Security=True in the connection string and the error message: Message=Login failed for user 'MS\LH7U0CNU3369CKR$'.

[techspider]

I pasted the connection string while I was researching ways. Actually, I removed (and set to false) Integrated security. I have already added this user in SQL Server Logins and provide admin access with windows authentication. In connection string, I provided user id=domain\username;password=ntpassword. But, then I started getting the error that login failed for user domain\username.

[techspider]

When I add custom identity in app pool and open svc file in browser, i get a message "service isnot available". The only way I could connect to DB is by marking identity to LocalSystem, and providing integrated security =true; removing user id and password from connection string.

[techspider]

Yes, as long as I keep, Integrated Security=true, App Pool identity as Localsystem, the application works. When I change App Pool identity to Network Service, I get login failed error (whether I enable integrated security or provide a domain user credentials in connection string). Please advise me the on the configuration for network service, if I did wrong somewhere. I have provided admin rights to the user id (my windows login id) in sql server logins as well.

[Pantelis Natsiavas]

You should provide permissions for the specific account on the specific database. Not on the SQL Server as a whole. I hope it is clear now.