In the last week I’ve been investigating SQL Server’s Transparent Data Encryption (TDE) in preparation for a 10 minute presentation at the next Kent SQL Social/User Group meeting.
TDE isn’t something I’d ever had any exposure, experience and opinion on until now, I’d always thought of it as a capability statement to help it match up to Oracle’s list of features. However, it does help solve a requirement that I’m sure will begin landing on people’s desks more and more in the next 5 years – how do we stop our data physically falling into the wrong hands?
Logical access controls, physical access controls and application level encryption have been around for years. Until recently they’ve all pretty much met the security requirements of the business. Now we’re seeing more and more media stories about media going missing – backup tapes lost in the post, laptops left on the train etc. This poses the current security challenge for data managers – how do I stop someone reading my data when they find my MDF/LDF/BAK files on a CD in the bin?
SQL Server attempts to address this through the introduction of TDE. It’s totally transparent to the application, no new connection strings, no client side certificates, no code changes, nothing. Great, simple out the box data encryption? Well the encryption might be simple to implement but it does raise the question of managing my encryption infrastructure.
Enabling TDE isn’t just a tick box, you need at least 2 keys to be generated and held within the SQL Server instance as well as a certificate to actually encrypt data with (SQL Server cleverly takes advantage of the fact that asymmetric encryption is really secure but symmetric encryption is much faster). These keys and certs are all managed from within SQL Server, through T-SQL if you prefer. The DBA can begin creating his own security domain (read empire!) and easily stop anyone else from enforcing their security standards/policies or even keeping a copy of his keys.
This to me is a worry. SQL Server’s internal encryption infrastructure is powerful, complex, highly configurable but all exposed internally within SQL Server. It’s unlikely the security department’s scanning or key management tools will be able to easily access SQL Server to ensure compliance in the same way they’re used to with Active Directory etc. We also have to make sure that someone somewhere backs up the service master keys and database master keys we encrypt our databases with. We also have to have secure methods of transferring them and certificates to other servers where we want to restore database backups too. It starts getting quite complex and while the theory is simple your data will pay the ultimate price if someone didn’t quite do it right.
Is this something the DBA will have the time to learn, implement properly and manage correctly?
The answer for some deployments is the external key management services which SQL Server can have plugged into it. These can be hardware devices offering certified encryption services which is probably what high end users will implement, it’d just be nice to not have the DBA solely responsible for ever getting your hands on the data again if the keys get lost.
If I later find out I was wrong I’ll update this post!