This is something that hit me as I was presenting to the Charlotte SQL Server User Group last night.

Back in the Windows 2000 days I wrote an article on Encrypting File System and explaining how it could be used to protect the database files at rest. In my Fortress SQL Server presentations I've been briefly mentioned that Transparent Data Encryption in SQL Server 2008 gives similar protection, but it can be used to impede the administrators a bit more than with EFS. With EFS you should properly define recovery agents so that if something were to happen to the service account SQL Server is running under, you could still recover the database files. So there's always that backdoor through EFS. There's another one, and that's the SQL Server service account. If you know it, you can get to the files.

Of course, if you have the SQL Server service account, you can connect to SQL Server as a sysadmin level account. And that means TDE is not an obstacle, either. Really, TDE doesn't stop an enterprising attacker who has administrative rights over where the SQL Server is installed. Because of the ability to start up the SQL Server in single user mode, any member of the local Administrators group can force himself or herself in. And this can't be stopped. It can be audited for, but that's an after the fact type of security control. With the SQL Server service account, though, you don't even need to do this. You can access SQL Server using the service account and you have access to the TDE-enabled database. There is no stopping and starting of SQL Server. There is no outage reported. Plus, no one knows it was you. After all, the security event logs showed that the SQL Server service account was used. Uh-oh.

Well, what if you've taken solid precautions and no one knows the SQL Server service account? The problem with that is if your SQL Server is running on Windows 2000 Server or Windows Server 2003, any member of the local Administrator can grab it at any time. The key is to go after LSA Secrets. Tools like Cain use a DLL injection attack and are able to dump the username/password every service is set to run under in plaintext. In a matter of seconds. Depending on how your antivirus (AV) is configured (are you running antivirus on your SQL Servers?), it may detect the Cain executable and quarantine it or it may not. Of course if you're not running AV, and you don't have some other sort of HIPS product installed, you can't stop Cain. And once those username/password combinations are dumped, how do you know who the attacker is? And how do you stop them from accessing a TDE-enabled database or taking an EFS encrypted database offline using ALTER DATABASE and then, under the context of the SQL Server service account, copying the database files off, say, to a USB drive (since most modern servers are sporting USB slots instead of floppies nowadays) or to a network share to be retrieved at a later time? This sort of attack really limits the usefulness of EFS or TDE.

I did say Windows 2000/2003. With Vista/Windows 2008, Microsoft took some steps to make accessing LSA Secrets a might bit harder. So far, no one has been able to put together all the pieces. So for right now, you're okay if your SQL Server is on Windows Server 2008 (yet another reason to upgrade). But if it gets cracked, too, then EFS and TDE become just as futile as on Windows Server 2008 platforms as they are on Windows 2000/2003 (and XP, of course). Now, there's a proviso there and I'd be guilty of FUD if I didn't point it out. The merely curious will be stopped by EFS and TDE. The focused attacker will not. But then again, the focused attacker will try social engineering, brute force attempts, and other OS, application, or database platform weaknesses to get in. If you don't catch said attacker through solid auditing and a well-trained and security conscious staff, the attacker is going to eventually get in.