1

I am trying to encrypt a column in sql server 2005 using Certificate and Symmetric Key

what happens to data after certificate expires?

I read in some Blog that sql server does not validate the expire date of certificate and user will still be able to encrypt and decrypt using the expired certificate

Is this true,If yes,Can someone point me to a Microsoft KB Article (or) Books online page, Which states that Sql Server does not validate the Certificate Expire Data and user will still be able to Encrypt and Decrypt the Data using the expired certificate

Thanks Ram

Improve this question

2 Answers 2

Reset to default
3

You can view the official documentation for the CREATE CERTIFICATE statement in Books Online, excerpt here:

EXPIRY_DATE = 'mm/dd/yyyy'
Is the date on which the certificate expires. If not specified, EXPIRY_DATE will be set to a date one year after START_DATE. EXPIRY_DATE is in UTC time and can be set to the granularity of the second. SQL Server Service Broker checks the expiration date; however, expiration is not enforced when the certificate is used for encryption.

Improve this answer
2
  • Hi Chad, I did look at the link you provided,But it is for Sql server 2008 when we click Sql Server 2005 beside it and it takes us to Create Certificate statement in books online for sql server 2005, we dont see the statement you have highlited (this is where i am confused) Thanks for the reply Thanks Ram
    – Ram
    Dec 1, 2009 at 17:31
  • Well, I can't seem to find anything official that states it, but you can do a quick web search and find many authoritative responses indicating the expiry_date isn't enforced for encryption/decryption (including Remus and Laurentiu Cristofor, both of whom are/were developers on the Sql Server team at Microsoft). Next best thing I could tell you would be to simply create a script and repro the behavior - you'll notice that under no circumstance does the expiration date impact the ability to encrypt/decrypt...as Remus says, it simply would be a terrible design if that were the case.
    – boydc7
    Dec 1, 2009 at 21:42
1

In addtion to what chad said, most certificate based encryptions schemes work similarly:

  • data is encrypted using a symmetric key
  • the certificate is to encrypt the symmetric key
  • when certificate is compromised or near expiration, it is replaced with and symmetric key is re-encrypted with a new certificate

So the certificate can always be replaced with minimal impact since only the symmetric key encrypted with this certificate needs to be re-encrypted with the new certificate.

Also, even on systems that do check the expiration date, the general rules are that:

  • for signature verification the certificate had to be valid at the time it was used not at the time it is verified
  • for data encryption the certificate has to be valid at the time data is encrypted, not at the time is decrypted

A scheme that enforces the certificate expiration when checking signatures or decrypting data is very bad design as past the expirationd date all data would never be decryptable.

Improve this answer
3
  • +1 - good detail on the general design behind the use/methodology/pattern
    – boydc7
    Nov 30, 2009 at 23:43
  • Remus thanks for the reply When a certificate expires how do we replace or renew it (How does the Symmetric key gets in sync with the new certificarte or the renewed Certificate and does the data get re-encryted too along with the Symmetric Key) Thanks Ram
    – Ram
    Dec 1, 2009 at 17:35
  • @Ram: when the certificate is near expiration you add the new certificate, then you encrypt the symmetric key using the new certificate ALTER SYMMETRIC KEY ... ADD ENCRYPTION BY ..., then you drop the old encryption ALTER SYMMETRIC KEY ... DROP ENCRYPTION BY ... and finally drop the old certificate.
    – Remus Rusanu
    Dec 1, 2009 at 21:41

You must log in to answer this question.