In SQL Server 2008 how can I secure data in a way that it cannot be decrypted unless connected to a network?

We have recently implemented Transparent Data Encryption in SQL Server 2008 for local databases on our developers laptops to keep them protected in the case a laptop is stolen or lost. This works fine.

Now we are trying to figure out a way to have the certificate expire everyday, forcing an automated process (a script at logon maybe) to go out to a network path and grab a new certificate with an expiration for a day later. This would ensure that if something unforeseen happened, the data would not be usable the next day.

I also looked into using a Cryptographic provider but there doesn't appear to be any "providers" out there. Maybe I'm wrong.

I am open to suggestions. If there is a better way please let me know. Thanks!

From stackoverflow Ryan Montgomery

• Short answer: No

  Long answer: Once a message (piece of data) is encrypted, that same key will decrypt the same encrypted message, regardless of what time the decryption algorithm is applied. If the key is changed every day, the data must be decrypted with the old key and re-encrypted with the new. If this process doesn't occur (i.e. someone stops the piece of code that performs the re encryption from running), the old key will still work. Even if you do create a cryptographic provider to check the date, someone else can create a new provider to perform the decryption without first checking the date.

  From Josh

• Without additional detail I fail to understand how your TDE setup will protect data in case it is lost or stolen.

  If you are not using full disk encryption (via Bitlocker, Truecrypt, etc) then I as an attacker in physical possession of your hardware can easily reset the local admin password, boot up the laptop and access the SQL Server instance with the local admin credentials. At that point I am a sysadmin on the database server and am able to extract any data I want or to turn off TDE.

  In addition since all of the encryption keys and certificates are stored locally it is relatively easy for an attacker in physical possession of the device to gain access to them. TDE is only meaningful for data protection when you physically separate the Database Encryption Key protectors (stored in the master database) from the encrypted database.

  If you are using full disk encryption than the usage of TDE is not providing any additional deterrent to an attacker and is only adversely affecting system performance of your developers laptops.

  From Joe Kuemerle

• T address the question rather than the motivation. If you set up a Microsoft CA with a derived template (Set to expire for a day) and also allow autoenrollment on that certificate template. You could then set your SQL machine to be part of a OU within the Directory that uses autoenrolment (Technet will give you resources on this requires the use of goup policy). That way when the certificate expires the machine will automagically request a new one.

  http://windowsitpro.com/article/articleid/40948/windows-server-2003-pki-certificate-autoenrollment.html

  Mark

  From Mark Sutton

• You're right - what you want is a cryptographic provider, and you're right that there's none out there yet.

  If you're going to the PASS Summit in November, talk to JC Cannon from Microsoft. He's doing a session on compliance, and he's the head of the SQL Server Compliance group. He's tied into the vendors that are currently working on building cryptographic providers, and he may be able to talk to you about vendor names. Right now they haven't come out publicly to announce who's doing it yet.

  From Brent Ozar