In SQL Server 2008, how can I secure the data so that it is not deciphered unless you are 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!

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.