I want to add an AD security group to my MS SQL 2005, which will contain the personal allowed to fully manage all aspects of the server.

I have created the group (DOMAIN\SQL Admins) and added it to the Security -> Logins list, on the server. Yet, a user, member of that group, can't connected. It gets:

Error number 18456, Severity 14, State 1

I have granted everything available on the Logins list. I have added the domain group to the local Administrators group on the server. No dice. If I add the user to the Logins list specifically (DOMAIN\user), it works.

Could anyone shed some light? I will update the questions if more information is needed.

  • Is it possible that you use Windows Authentication? That should conveniently resolve the issue. – MarlonRibunal May 13 '09 at 15:33

It seems there was an AD propagation issue. The AD Group wasn't fully propagated yet, and hence the SQL server wasn't recognizing the user as an authorized one.

  • Ah, yeah that would do it :) Good point to keep in mind. – squillman May 13 '09 at 15:22
  • Make sure they're not in another role that specifically denies access to something.
  • Make sure their account is not locked

EDIT Yeah, scratch those. Just noticed it works if adding them to logins. What version of AD are you using? It could be the evil Kerberos Ticket Size problem... Here's more on AD limits which talks about the Kerberos ticket size. Basically being a member of many groups whacks Kerberos authentication. As a test remove the user from a group (few groups) and see if the SQL auth works.

  • Right, neither. – user1797 May 13 '09 at 15:02

Is the SQL Server and the user group in the same domain? If not, is that security group a domain local group?

  • Both on the same domain, correct. – user1797 May 13 '09 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy