After some recent talks with security folks and auditors, one of the things I have had a hard time getting across is that you must trust those folks responsible for account and server management when it comes to securing your data. Yes, you can put in a lot of deterrents, but at the end of the day most of them can be beaten by a wily admin. So if you're not hiring trustworthy people, that's the first and most important weak link in your chain. Now this isn't to say that you won't have to deal with the case of someone who has gotten disgruntled or who has gotten in a situation where they can be manipulated. Though rare, those things happen. But at the end of the day, you must trust your server and account administrators.

Let's look at an attack vector to explain why. Best practices with Windows security says to do permissions management using security groups. So typically you'll have a Windows security group for your DBAs' elevated accounts (if you aren't using elevated accounts, here's why you should). What's to stop said account administrator from creating an account and dropping that account in said group? The account only needs to stay there long enough to get the data and get out. So the account admin does just that. Creates a new account (to try and avoid non-repudiation), adds it to the security group, and then logs on to the SQL Server with sysadmin rights. Now everything on that server is accessible. Yes, even the database you encrypted using Transparent Data Encryption (see previous post about how this doesn't stop an admin). So you've got to trust the folks working account administration.

What about server administrators? You've got running SQL Server in single-user mode, as I described last post, but there's another trick possible, too. It used to be the recommended advice for handling the permissions was to do the following:

Windows User Account -> member of Domain Global Group -> member of Server Local Group -> Access to Resource (file share, SQL Server, etc.)

If you're still following that model, the server admin can simply make a new account a member of that local group on the server. And the account could even be a newly created account on said server, for the same reason the account administrator might do so. So the admin now has access to SQL Server and the data. If you're not following that model, and you're only using domain security groups, then there are the attacks I gave before still work. In addition, for SQL Server 2005 installs which aren't on a cluster, there are the MSSQLUser and SQLAgentUser local groups which have sysadmin rights within SQL Server as well.

So it sounds like you can't do anything about it. And to a certain point that's true. If someone is bound and determined to get the data and has those types of rights, they're going to be able to create a way. By following best practices you will cause those who aren't hell bent on getting in to stop, especially after they see any sort of resistance. But what about that 0.1 or 0.01% that will do whatever it takes? The catch then is to go from prevention to detection. Solid auditing is required. There are a few event IDs you'll need to watch for in the Security event logs:

• Account Management event ID 624 - User Account Created (Windows 2003 and below) 

• Account Management event ID 632 - Global Group member added or removed (Windows 2003 and below)

• Account Management event ID 636 - Local Group member added or removed (Windows 2003 and below)

• Account Management event ID 4720 - User Account Created (Windows Vista and above)

• Account Management event ID 4728 - Global Group member added (Windows Vista and above)

• Account Management event ID 4732 - Local Group member added (Windows Vista and above)

These event IDs need to be monitored on the domain controllers as well as any servers where SQL Server is running. The events themselves will need to be investigated to ensure that the the appropriate groups aren't being changed. And if you're talking about the local to the server groups, you'll also need to be getting the events off of the server and into a secure repository in as timely a manner as possible, because the admin could always purge the security event log. And if you've been thinking along the attack vectors I've given already, you've thought of the idea of creating a local account, making it a member of the local Administrators group, and flushing the Security event log under that user account, thus erasing who created the account in the first place.

Now back to my original premise, you can automate all of this and have a great auditing system, but at the end of the day you must be able to trust the people you hire into these positions. Even if the person gets caught, they've still gotten the data. You may be able to prosecute them and recover your losses from a financial perspective (to a certain extent), but you've still taken a reputation hit and if it's privacy information, you've got a bunch of folks who are now at risk. If it was intellectual property, then it could be quickly sold or just given away to another entity. Trust them, but check up on them with sound auditing.