Cannot open database "our database" requested by the login. The login failed. Login failed for user 'ADUser'

Question

We are rolling out our first .net 4.0 entity framework application and are having an issue with security.

We have it working on our alpha site inside our development environment with the following setup:

1. SQL2005
2. IIS6
3. .NET 4.0
4. asp.net mvc 2
5. Entity Framework
6. NTLM

But when we moved it to our production environment for beta testing we are getting the following error via asp.net

SqlException (0x80131904): Cannot open database "our database name" requested by the login. The login failed. Login failed for user 'Domain\User'.

The only difference between the between the 2 environments is we are using Kerberos instead of NTLM in our production environment. We have several other .net 3.5 sites using LinqToSql that run on both environments.

We have already done the following:

1. Replaced an active user on another test site with the user that is failing to make sure it isn't an issue with the way the user is set up. - worked
2. Dropped and re-added the user from sql2005 - did nothing

The issue appears to be related to .net 4.0 as this is our first...

Answer 1

Based on the error message, 2 questions:

• Does the login have access to the database in your production site?
• Does the database that you specified in the connection string on the SQL server that is specified in the connection string?

These are the 2 most common causes of that issue. A Kerberos issue usually manifests itself in SQL Server a little differently.

Thanks, Eric

Answer 2

The difference between NTLM and Kerberos is huge. With NTLM, you can use pass-through authentication with Kerberos you cannot. With Kerberos and Windows Authentication, you need to setup a Service Principal Name (SPN for short) that tells SQL Server that it can be accessed by whatever IIS account you use.

Understanding Kerberos and NTLM authentication in SQL Server Connections

Here's another article on setting up Kerberos. Jump down to the section titled "Configure a service principal name for the domain user account"