5

Some maintenance is being worked on in our Active Directory here and my supervisor asked me if SQL Server cared about the location of security and service accounts.

I told him I didn't think it did but I wasn't 100% sure.

So, for service accounts and security groups used to control access to the database, does SQL Server care where they are in Active Directory?

Improve this question
2
  • 1
    Do you mean where they reside within the OU structure? If that's the case then no, SQL doesn't care.
    – Nic
    Feb 6, 2013 at 21:29
  • @nic yes, that is what i mean.
    – DForck42
    Feb 6, 2013 at 22:13

2 Answers 2

Reset to default
3

No SQL Server doesn't care where in the OU the account and/or groups are. Everything is done via SIDs, or in the case of the service accounts with the actual username and password to startup the service.

Improve this answer
1

AFAIK, everything in SQL Server related to Windows security is based on SIDs, so the location in Active Directory is irrelevant.

As I discovered experimentally while writing this answer, Windows login SIDs are cached internally in SQL Server, so the authentication process never even hits AD after the login is created.

So long as objects aren't themselves modified (deleted, group members changed, etc. -- i.e., the obvious stuff which won't be happening), you can move the users and groups around between OUs at any time.

Improve this answer
6
  • 2
    Jon, that's not actually true. While yes the users SID is stored within the SQL Server every time the user connects to the SQL Server instance that account is verified against AD to ensure that the user account is still active and to see what groups the account is a member of. Kerberos handles most of this, but there is plenty of talking between SQL and AD.
    – mrdenny
    Feb 7, 2013 at 4:12
  • @mrdenny: I ran the following test: created a local user, created a login in SQL for that user, ran Management Studio as the local user, disabled the user account, connected to the SQL instance using Windows authentication. The connection succeeded. Your thoughts? Group membership is the same deal -- it only takes effect the next time the user logs in; in fact, that's exactly what the Group Membership dialog box says: "Changes to a user's group membership are not effective until the next time the user logs on." I can even physically delete the group and it's still effective until next log on.
    – Jon Seigel
    Feb 7, 2013 at 19:17
  • hum, SQL is supposed to verify that the Kerberos ticket is still valid, which is shouldn't be because the account is not disabled. I'm going to need to dig into what's going on. You are correct about the groups not being refreshed. In my config I'm usually not on the domain, so things work differently and I forget that sometimes. I'm quite surprised that a domain account that once disabled still allowed the connection to SQL.
    – mrdenny
    Feb 7, 2013 at 22:54
  • @mrdenny: I just re-ran the same test in a domain scenario (was at home before) with the same result. I was even able to delete the user account itself and still connect to the instance after I had initially logged on. I'm not sure if it's time-limited, so I'll keep Management Studio open (run as the deleted user account) and try again in a little while and report back.
    – Jon Seigel
    Feb 8, 2013 at 14:48
  • Still works ~1.5 hours later. I'm not an expert in this area, but it seems that once you get the token on the client, there's pretty much free reign until you log off and try to get a new token.
    – Jon Seigel
    Feb 8, 2013 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.