This is part of a series of tips on how bad/rogue admins can get access to the data in your SQL Servers.

I have seen organizations put a lot of attention and focus on the database servers themselves. This is true whether we're talking SQL Server or another product. This is good. But I've seen some of those organizations ignore the backups of the databases on those servers. They might encrypt those files as they are written to tape because those tapes are going off-site, but they don't do anything about those files sitting on the server. This isn't good.

If you've ever tried auditing file access, and I mean "success" access, you know this isn't easy. There are so many events, so much noise, because there are processes with legitimate access. It makes trying to find out who accessed the files improperly difficult if not impossible. So this isn't a scenario where auditing will help. If I'm an admin and I have access to the backup such that I can use it, then I don't really need access to your SQL Server. I have the data, and that's what's important. By the way, setting file and folder permissions are a deterrent, but don't stop an admin who truly wants that data. After all, if I am an admin on the box, I can change the permissions. You could audit for this, too, but it too can suffer from an issue of too much noise to filter out.

The solution then is to encrypt the backup. If you're running SQL Server 2008/2008R2 Enterprise Edition, you're in luck. You can turn on Transparent Data Encryption for each database. TDE doesn't just encrypt the database files when they are "at rest," it also encrypts any backups made from those databases. But what if you don't have 2008/2008R2 Enterprise Edition? You could try and use Encrypting File System (EFS) which is built into the operating system. However, chances are the admins have the escrow keys to decrypt the backups anyway. EFS is not a solution I've seen a lot. What else is there?

Third party backup products are typically the best answer here. Most now can encrypt the backups. Look at the products wisely, though. Some will allow for an encrypted string of the key used to decrypt the backup should you need it. That means if I'm looking at the command to do the backup, I can't just pull the key. If this was the case, then the backups aren't really protected. Some backup products allow you to encrypt the backups they produce, but the key used to decrypt is plainly visible. If I can get that + the backup, I have the data, same as before. So you're looking for a third party backup product that doesn't reveal the key when you take the backup if you can't use TDE.