Using a Security Group to Manage MS SQL 2005

Question

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.

Answer 1

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.

Answer 2

• 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.

Answer 3

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