Sql Server 2008, Active Directory Groups, and Failed Logins

Question

I keep getting a Login Failed error in my ASP.net application when connecting to my SQL Server 2008 database. I am trying to login with the user domain\foo.

When I grant a database login (server and database level) for domain\foo, my application can connect.

When I put domain\foo in a group called domain/goo and give domain\goo a database login, the user domain\foo cannot authenticate.

This does not make any sense. Am I doing something wrong? domain\foo and domain\goo are configured identically. The only difference is that on is a user and one is a group containing a user. Adding active directory groups as users to SQL Server 2008 is supposed to work.

Answer 1

You can't use groups. I wasn't aware that SQL 2008 had changed this. I think you have to use individual user accts. I know... it sucks.

Answer 2

It sounds like you encountered a scenario similar to one I did when setting up my first SQL Server 2008. In my particular scenario I was using Windows authentication only, and had granted local administrators sysadmin rights. Yet the only person who could successfully log into SQL Server Management Studio was the Local Administrator of the server. (The errors I saw are at the bottom of this posting.)

After locating the SQL Server debug logs, I discovered that the authentication was succeeding, but access was being denied (State: 11). After further digging in TechNet, I found that UAC masks most group memberships, and the workaround is to run SQL Studio as an administrator.

---

SQL Server Management Studio

Cannot Connect to {servername}.
Additional information
->   Login failed for user '{username}'. (Microsoft SQL Server, Error: 18456)

Windows Event Log

Login failed for user '{username}'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

SQL Server Debug logs

(Located at: {SQL_Server_Root}\MSSQL10.{instance}\MSSQL\Log\ERRORLOG)

{Datestamp} Logon Error: 18456, Severity: 14, State: 11.
{Datestamp} Logon Login failed for user '{username}'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

Answer 3

Are these Active Directory Security Groups or Distribution Groups?

I can tell you for a fact that we use AD Security groups to assign permissions in SQL Server version 2000, 2005, and 2008.