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?
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.
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.
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.
