SqlServer 2008 R2 Error 18456 with AD groups

Question

We successfully performed a fresh install of Windows Server 2008 R2 and Windows SQL 2008 R2 Enterprise x64. 
However, I'm seeing the following issue when logging into the server via AD group (for our developers) using Windows Authentication.
Error: 18456, Severity: 14, State: 11
What does work:
1. Logging in directly with the admin (sa) account.
2. Adding a domain account directly (e.g. domain01\jsmith) in SqlServer.
3. Adding a domain account into a local group on the server, then adding the local group to SqlServer.
 
What does NOT work:
1. Adding a domain group (e.g. domain01\group1) in SqlServer.  It accepts the group fine, but we get the 18456 error when logging in.
2. Adding a domain group to a local group.  Then adding that local group to SqlServer.  It accepts the group, but we get the 18456 error when logging in.
3. Running SSMS as admin.  Our users don’t have permissions to run as Administrator.

I've been reading some pages/threads.
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/043898a3-eed8-4175-9be1-40bf1a2bdbaa
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
http://www.cleverworkarounds.com/2010/08/01/sql-server-oddities/

This setup works fine on our Windows 2003 and SqlServer 2005 machine.  However, upgrading to Windows 2008/SqlServer 2008 is causing the situation.
Any advice would be appreaciated.

Answer 1

 

Hello,

11 means server access failure. Please see the following post:

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

Try using a domain account to start SQL Server service.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Hi Magilla,

From the link, States 11 means that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin).

Hope this helps,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 3

Thanks for everyone's response. 

I did run the service as a domain account but it still didn't work.   I can get individual people into the DB using their domain\userid.  However, having them inside a group (on the AD or local) doesn't work.  It works fine with SqlServer 2005, but that's also on Windows 2003 server.

Unfortunately we can't disable the UAC or run SSMS as admin.   The machine are locked down for the users.

If I find the solution, I'll make sure to post it!



Answer 4

Anything on this issue, I've got a weird example of this.

AD Group is sysadmin on SQL2008R2SP1 Instance. All AD Accounts in the group can authenticate but one of them can not perform sysadmin functions like DBCC while the others can.

When I add that AD account specifically as a user/sysadmin in the Instance....it can do everything.

I think it has something to do with GUIDs out of sync or something...