This is a series of blog posts about how administrators can gain access to SQL Server, even if you try to impede them. This was inspired by a conversation with Brent Ozar (twitter | blog) about Argenis Fernandez's (twitter | blog) post about getting in as System. Basically, I cover a lot of this stuff in my security presentations, but have never put together a blog series talking about the various attack vectors. If you haven't read Argenis' post, start there.

Now if I'm an admin with rights in Active Directory, likely the easiest way for me to gain access to the SQL Server is through the Windows group used by the DBAs to allow them in. In organizations of any size whatsoever, using a security group in AD is a recommended practice. It simplifies security management tremendously. Having been an AD administrator/architect, I would highly recommend this practice. The pros far outweigh the cons. But what about the ability of an admin to change the group membership? I'm glad you asked.

You can't stop them. Even if you tried to tie down to individual Windows user accounts, that doesn't work, either. Then they could simply use the second attack I'll mention in tomorrow's blog post. So let's stick to today's. The case here is that you can't just trust them. Eventually someone is going to let you down. So the control you have to put in place here happens after the fact: you audit group membership changes and report any such changes to someone other than the admins. This means having the right security settings in the Default Domain Controller GPO (if your organization is following the Microsoft solution accelerators under Security Compliance Manager, this will be done). All such group changes will be delivered as events to the security event logs on the domain controllers. This is where things get tricky...

Your organization has to audit and audit frequently. Even with large security event log sizes, events can get overwritten in a hurry, especially with the number of events recorded in Windows Vista, Windows 7, and Windows Server 2008. It seems like 2 orders of magnitude over the number of events in previous OS versions. Not sure what to audit for? Check out Randy Franklin Smith's exhaustive list of events. Key in one the ones dealing with Group Member changes. Get those reports to the right people as soon as possible. While you may not prevent the admin from making the change, you can certainly catch said admin having done so.