Question - DB Certificate expiration (Column level encryption)

Question

Question - DB Certificate expiration (Column level encryption)

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
October 26, 2017 at 12:18 pm

#333421

Details:
-----------
For a Prod DB
* There is a Master key encrypted by password.
* Certificate named 'DBCertificate' expiration 12/30/2018
* Symmetric key named 'DBSymmetricKey' (With Algorithm = AES_256) which is encrypted by 'DBCertificate' .
Symmetric key is used to encrypt/decrypt credit card column.
Since the certificate is expiring on Dec 2018,
Do I need to update the certificate? If so how can I do this?
Will this affect the data after certificate expiration date?
Please share your thoughts on this.
Many thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SQL!$@w$0ME - Thursday, October 26, 2017 12:18 PM
Details:
-----------
For a Prod DB
* There is a Master key encrypted by password.
* Certificate named 'DBCertificate' expiration 12/30/2018
* Symmetric key named 'DBSymmetricKey' (With Algorithm = AES_256) which is encrypted by 'DBCertificate' .
Symmetric key is used to encrypt/decrypt credit card column.
Since the certificate is expiring on Dec 2018,
Do I need to update the certificate? If so how can I do this?
Will this affect the data after certificate expiration date?
Please share your thoughts on this.
Many thanks!

Certificate expiration dates aren't enforced when used with encryption.

Sue

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Sue_H - Thursday, October 26, 2017 3:20 PM
SQL!$@w$0ME - Thursday, October 26, 2017 12:18 PM
Details:
-----------
For a Prod DB
* There is a Master key encrypted by password.
* Certificate named 'DBCertificate' expiration 12/30/2018
* Symmetric key named 'DBSymmetricKey' (With Algorithm = AES_256) which is encrypted by 'DBCertificate' .
Symmetric key is used to encrypt/decrypt credit card column.
Since the certificate is expiring on Dec 2018,
Do I need to update the certificate? If so how can I do this?
Will this affect the data after certificate expiration date?
Please share your thoughts on this.
Many thanks!
Certificate expiration dates aren't enforced when used with encryption.
Sue

Is it only for TDE(transparent data encryption ) certificate as per ms article not for column encryption.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql

EXPIRY_DATE =' datetime'
Is the date on which the certificate expires. If not specified, EXPIRY_DATE is set to a date one year after START_DATE. EXPIRY_DATE is in UTC time and can be specified in any format that can be converted to a date and time. SQL Server Service Broker checks the expiration date. However, expiration is not enforced when the certificate is used for encryption.

Built-in functions for encryption and signing do not check the expiration dates of certificates. Users of these functions must decide when to check certificate expiration.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

GilaMonster - Thursday, October 26, 2017 4:01 PM
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql
EXPIRY_DATE =' datetime'
Is the date on which the certificate expires. If not specified, EXPIRY_DATE is set to a date one year after START_DATE. EXPIRY_DATE is in UTC time and can be specified in any format that can be converted to a date and time. SQL Server Service Broker checks the expiration date. However, expiration is not enforced when the certificate is used for encryption.
Built-in functions for encryption and signing do not check the expiration dates of certificates. Users of these functions must decide when to check certificate expiration.

Thanks Gail! Does that mean I don't have to worry about cert expiration?

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 5

Sue_H - Thursday, October 26, 2017 3:20 PM
SQL!$@w$0ME - Thursday, October 26, 2017 12:18 PM
Details:
-----------
For a Prod DB
* There is a Master key encrypted by password.
* Certificate named 'DBCertificate' expiration 12/30/2018
* Symmetric key named 'DBSymmetricKey' (With Algorithm = AES_256) which is encrypted by 'DBCertificate' .
Symmetric key is used to encrypt/decrypt credit card column.
Since the certificate is expiring on Dec 2018,
Do I need to update the certificate? If so how can I do this?
Will this affect the data after certificate expiration date?
Please share your thoughts on this.
Many thanks!
Certificate expiration dates aren't enforced when used with encryption.
Sue

Thanks Sue!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQL!$@w$0ME - Friday, October 27, 2017 6:46 AM
Thanks Gail! Does that mean I don't have to worry about cert expiration?

Um...

expiration is not enforced when the certificate is used for encryption.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass