0

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.

Improve this question
1
  • Is it possible that you use Windows Authentication? That should conveniently resolve the issue.
    – MarlonRibunal
    May 13, 2009 at 15:33

3 Answers 3

Reset to default
2

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.

Improve this answer
1
  • Ah, yeah that would do it :) Good point to keep in mind.
    – squillman
    May 13, 2009 at 15:22
0
  • 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.

Improve this answer
1
  • Right, neither.
    – user1797
    May 13, 2009 at 15:02
0

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

Improve this answer
1
  • Both on the same domain, correct.
    – user1797
    May 13, 2009 at 15:11

You must log in to answer this question.