4

I am using SQL Server's Always Encrypted feature to encrypt a few columns in the database using a master key that is protected by a self-signed certificate. The certificate is created using SQL 2016's Management Studio and always defaults to an expiration date that is one year ahead of the issue date - it is stored in the Windows Certificate Store for the current user.

Is it possible to extend the validity of this certificate to a value greater than a year?

More specifically, can a certificate required by AE be scripted - from my understanding, this certificate is different from the sql certificate created by the CREATE CERTIFICATE command and needs to be exported to a file format like pfx to be accessible by an Azure web app.

Also, can the data still be encrypted/decrypted if the certificate has expired?

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
F.S.
  • 333
  • 1
  • 3
  • 13

3 Answers3

3

The create certificate SQL statement that SQLmojoe included in the answer is not intended for use with AE.

You could create certificates programmatically using a script (batch) and calling makecert, for example:

Makecert.exe -n "CN=Always Encrypted cert" -pe -sr CurrentUser -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky exchange -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -len 2048 -a sha256

Notice that if you want to create a certificate on the local machine store location, you will need admin privielges on teh box and you will need to change the -sr parameter.

I hope this helps.

Raul G
  • 279
  • 1
  • 4
  • Perfect - this is exactly what I needed. I was able to create the certificate using MakeCert and then reference that within SQL Management Studio to create an ALwaysEncrypted certificate. Thanks a lot for your help! – F.S. Dec 10 '15 at 22:52
1

No, you can't extend its validity period. Certs are basically immutable. Else, it'd be a lot more expensive (potentially impossible to do practically) to check for validity, maintain revocation lists, etc.... You can easily create a new cert to replace the existing one and set the expiration for the new cert to whatever works for you. E.g. 

CREATE CERTIFICATE [FSAECMKCert] WITH SUBJECT = 'FS AE CMK Cert',
START_DATE = '12/02/2015', EXPIRY_DATE = '12/31/2037'

Note that AE doesn't actually honor certificate expiration. Else lots of users will end up losing access to their own data - most organizations don't do a great job with renewals/rotations. However, it's a good general practice to have a "reasonable" expiration policy and rotation/renewal process to maintain the required/expected level of security.

whoan
  • 8,143
  • 4
  • 39
  • 48
SQLmojoe
  • 1,924
  • 1
  • 11
  • 15
  • Thanks for your response. If I'm not mistaken, the create certificate command that you are talking about will create a sql certificate that can be used for other sql operations like service broker/etc - it cannot, however, be used for AE. – F.S. Dec 03 '15 at 15:38
1

Actually, client drivers, supporting Always Encrypted, do not check the expiration date (and they do not verify the certificate chain) for certificates used as column master keys. A driver will be able to encrypt/decrypt data, even if the certificate has expired.

Jakub Szymaszek - Microsoft
  • 586
  • 3
  • 3