Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Did this used to work 2003? –  Ryan Michela Nov 19 '09 at 3:28
    
I've never been able to give groups permission in mssql, it doesn't support that. However, I'd need to see some specific documentation for sql 2008 showing it supports this to believe it. If I get some time today I'll look around. –  Tatas Nov 19 '09 at 17:31

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: ]

share|improve this answer
    
I had an instance where run as administrator was not fixing this issue. Once I logged out of the server and back in, I could run as administrator and log in to the Database Engine. BTW, I could connect to Analysis and Reporting services without the run as fix. –  Keith Sirmons Apr 19 '10 at 14:51
1  
@keith: Group memberships /seem/ to be assigned at log-on. This would explain your symptoms if you added the user to the group in the same session as the one you were running when you tried to log into the DB. –  exhuma Feb 2 '11 at 13:41
    
@bitzbyte363: Thanks for this. I had the same problem and this fixed it. +1 –  exhuma Feb 2 '11 at 13:42

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.