提高始终加密证书的有效性 [英] Increase validity of Always Encrypted Certificate

问题描述

我正在使用SQL Server的始终加密功能，使用受自签名证书保护的主密钥对数据库中的几列进行加密。证书是使用SQL 2016的Management Studio创建的，始终默认为过期日期，该日期是颁发日期之前的一年-它存储在Windows证书存储中，供当前用户使用。

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?

更具体地说，可以编写AE所需的证书的脚本吗？-据我了解，此证书与CREATE CERTIFICATE命令创建的sql证书不同，需要导出

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?

推荐答案

答案中包含的SQLmojoe不适用于AE的创建证书SQL语句。

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

您可以使用脚本（批处理）并调用makecert以编程方式创建证书，例如：

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

请注意，如果要创建一个证明书如果是本地计算机存储位置，则需要在框上具有管理权限，并且需要更改-sr参数。

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.

我希望这会有所帮助。

这篇关于提高始终加密证书的有效性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！