The conversation on local administrators having rights in SQL Server has proven to be interesting and at times entertaining. My two most recent posts to the discussion went in the reverse fashion, which is, "Should DBAs have local Administrator rights on the server?" Of course, the magical answer is, "It depends." It depends on job functions, on size of the organization, on the skill sets of the personnel, and things like that which can't be summed up nicely in one pat answer. The discussion has spurred some off-Internet as well. Today I was having a discussion with another DBA about the merits of pulling BUILTIN\Administrators from the SQL Servers. In the past I've pointed out that if an administrator wants in, you can't stop him or her. Seriously, you can't. The question is, do you know he or she did it? Here's the classic setup:

• All DBAs are placed into a Windows security group.
• Server administrators are not members of this Windows security group.
• This Windows security group is granted sysadmin rights within the SQL Servers.
• BUILTIN\Administrators is not added, or removed where it exists.

We're all good right? Administrators are out, DBAs are in. We're using Windows authentication and we're using Windows security groups. So we're following all the practices we're supposed to be. Is this enough? This answer I can give you, "No." This isn't enough. There's got to be more if you want to know an administrator has done something he or she shouldn't have.

Attack Vector: Using Single User Mode

Let's start with the first point of weakness that bypasses all of this: single user mode. If I can restart SQL Server in single user mode (SQL Server 2005/2008), then I can log on. If I am in the local Administrators group, then SQL Server will treat me as a member of sysadmin, even if I'm not. This does require me to stop and start SQL Server. And this potentially means a short outage period. Here's a valid attack scenario:

• Stop SQL Server
• Restart SQL Server in single-user mode
• Log on to SQL Server
• Add a group I'm a member of as a login and make it a member of the sysadmin fixed server role.
• Stop SQL Server
• Restart SQL Server normally
• Do my damage
• Remove the security changes I made

If you're not auditing for the SQL Server service to stop, you likely won't catch the first shutdown. So what does that mean? It means you need to comb the application event log and SQL Server's error log for evidence that a restart occured. If you're not scanning the application event log or not checking SQL Server for evidence that it has been restarted, you might not know unless some user happens to hit an app that requires a database connection. By the way, there's likely a period of at least once a month where an outage is expected: when servers are being patched. That represents a perfect window of opportunity to perform the attack. Therefore you can't rely on a user or process catching the fact that the service was stopped and SQL Server was down.You've got to audit. And you should be auditing for changes to the fixed roles, too, especially ones like sysadmin. Unfortunately, changes to them don't get recorded to the SQL Server error log so you'll need to look at additional mechanisms (traces, DDL triggers, etc.) and supplement with just good ol' fashion querying membership and running comparisons on a regular basis.

Attack Vector: Changing Windows Security Group Membership

Is your organization auditing membership changes in key Windows security groups? Provided you've got proper audit settings turned on, an event is written in the Security event log on the domain controller where the change occurs. However, if you're not retrieving these events on a regular and frequent basis, it's entirely possible for them to roll off, thereby losing the fact that a membership change occurred. So what's to stop someone with the appropriate rights, who is not a DBA, from dropping his or her own account into the DBAs' Windows security group (or creating a new account and temporarily dropping it in... meaning you now need to audit for that, too)? The answer: nothing. And here's the thing, you could be auditing for the information, but if no one is reviewing it on a regular basis, then it does no good. Sure, you've got the records in case you finally notice there's an issue, but if you never notice there's one, you never review, and they get away with it. Because at some point, when that administrator is done raiding your information stores, that same administrator will pull the membership (and delete the account, if one was created), thereby attempting to cover his or her tracks.

The Bottom Line: Audit, Audit Well, and Check Your Audit Reports

So you have to audit. You can't rely on removing BUILTIN\Administrators as an effective control. It's not. It must be supplemented. If that's what your organization's auditors and security folks say to do and they stop there, then they've spurred on some extra work for folks, but without much gain to the organization. And, by the way, I'm going to go somewhere DBAs don't like security folks going. The auditing needs to be done on the DBAs, too. No, I don't like Big Brother looking over my shoulder. I'm no different than anyone else. So although Andy Leonard has pointed out that his empirical evidence is that DBAs tend to be a pretty ethical lot, there are always exceptions. A person doesn't have to be unethical at his or her core, either. It could be a life situation that creates a weakness. That's something that was beat into our heads in the military. Be careful of who you associate with. Be careful of what activities you are into. Be careful of your finances. All of those are possible ways for someone to get control over you. And that's true of DBAs, too.