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

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

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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.

share|improve this answer
    
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?

share|improve this answer
    
Both on the same domain, correct. –  user1797 May 13 '09 at 15:11

Your Answer

 
discard

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