CREATE CERTIFICATE (Transact-SQL)CREATE CERTIFICATE (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 中的数据库添加证书。Adds a certificate to a database in SQL ServerSQL Server.

此功能与使用数据层应用程序框架 (DACFx) 的数据库导出不兼容。This feature is incompatible with database export using Data Tier Application Framework (DACFx). 必须在导出之前删除所有证书。You must drop all certificates before exporting.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]   
    { FROM <existing_keys> | <generate_new_keys> }  
    [ ACTIVE FOR BEGIN_DIALOG =  { ON | OFF } ]  
  
<existing_keys> ::=   
    ASSEMBLY assembly_name  
    | {   
        [ EXECUTABLE ] FILE = 'path_to_file'  
        [ WITH PRIVATE KEY ( <private_key_options> ) ]   
      }  
    | {   
        BINARY = asn_encoded_certificate  
        [ WITH PRIVATE KEY ( <private_key_options> ) ]  
      }  
<generate_new_keys> ::=   
    [ ENCRYPTION BY PASSWORD = 'password' ]   
    WITH SUBJECT = 'certificate_subject_name'   
    [ , <date_options> [ ,...n ] ]   
  
<private_key_options> ::=  
      {   
        FILE = 'path_to_private_key'  
         [ , DECRYPTION BY PASSWORD = 'password' ]  
         [ , ENCRYPTION BY PASSWORD = 'password' ]    
      }  
    |  
      {   
        BINARY = private_key_bits  
         [ , DECRYPTION BY PASSWORD = 'password' ]  
         [ , ENCRYPTION BY PASSWORD = 'password' ]    
      }  
  
<date_options> ::=  
    START_DATE = 'datetime' | EXPIRY_DATE = 'datetime'  
-- Syntax for Parallel Data Warehouse  
  
CREATE CERTIFICATE certificate_name   
    { <generate_new_keys> | FROM <existing_keys> }  
    [ ; ]  
  
<generate_new_keys> ::=   
    WITH SUBJECT ='certificate_subject_name'   
    [ , <date_options> [ ,...n ] ]   
  
<existing_keys> ::=   
    {   
      FILE ='path_to_file'  
      WITH PRIVATE KEY   
         (   
           FILE ='path_to_private_key'  
           , DECRYPTION BY PASSWORD ='password'   
         )  
    }  
  
<date_options> ::=  
    START_DATE ='datetime' | EXPIRY_DATE ='datetime'  

参数Arguments

certificate_name certificate_name
数据库中证书的名称。Is the name for the certificate in the database.

AUTHORIZATION user_name AUTHORIZATION user_name
拥有该证书的用户的名称。Is the name of the user that owns this certificate.

ASSEMBLY assembly_name ASSEMBLY assembly_name
指定已经加载到数据库中的已签名的程序集。Specifies a signed assembly that has already been loaded into the database.

[ EXECUTABLE ] FILE ='path_to_file' [ EXECUTABLE ] FILE ='path_to_file'
指定包含证书的 DER 编码文件的完整路径(包括文件名)。Specifies the complete path, including file name, to a DER-encoded file that contains the certificate. 如果使用 EXECUTABLE 选项,则文件为已使用证书签名的 DLL。If the EXECUTABLE option is used, the file is a DLL that has been signed by the certificate. path_to_file 可以是本地路径,也可以是网络位置的 UNC 路径 。path_to_file can be a local path or a UNC path to a network location. SQL ServerSQL Server 服务帐户的安全上下文中访问该文件。The file is accessed in the security context of the SQL ServerSQL Server service account. 该帐户必须具有所需的文件系统权限。This account must have the required file-system permissions.

重要

Azure SQL 数据库不支持通过文件或使用私钥文件创建证书。Azure SQL Database does not support creating a certificate from a file or using private key files.

BINARY =asn_encoded_certificate BINARY =asn_encoded_certificate
指定为二进制常量的 ASN 编码证书字节数。ASN encoded certificate bytes specified as a binary constant.
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

WITH PRIVATE KEYWITH PRIVATE KEY
指定将证书的私钥加载到 SQL ServerSQL Server 中。Specifies that the private key of the certificate is loaded into SQL ServerSQL Server. 从程序集创建证书时,此子句无效。This clause is invalid when the certificate is being created from an assembly. 若要加载从程序集创建的证书的私钥,请使用 ALTER CERTIFICATETo load the private key of a certificate created from an assembly, use ALTER CERTIFICATE.

FILE ='path_to_private_key 'FILE ='path_to_private_key'
指定私钥的完整路径(包括文件名)。Specifies the complete path, including file name, to the private key. path_to_private_key 可以是本地路径,也可以是网络位置的 UNC 路径 。path_to_private_key can be a local path or a UNC path to a network location. SQL ServerSQL Server 服务帐户的安全上下文中访问该文件。The file is accessed in the security context of the SQL ServerSQL Server service account. 该帐户必须具有所需的文件系统权限。This account must have the necessary file-system permissions.

重要

这种方法不适用于包含的数据库或 Azure SQL 数据库。This option is not available in a contained database or in Azure SQL Database.

BINARY =private_key_bits BINARY =private_key_bits
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定为二进制常量的专有键位。Private key bits specified as binary constant. 这些位可采用加密形式。These bits can be in encrypted form. 如果加密,则用户必须提供解密密码。If encrypted, the user must provide a decryption password. 不会对此密码执行密码策略检查。Password policy checks are not performed on this password. 私钥位应该采用 PVK 文件格式。The private key bits should be in a PVK file format.

DECRYPTION BY PASSWORD ='key_password 'DECRYPTION BY PASSWORD ='key_password'
指定对从文件中检索的私钥进行解密所需的密码。Specifies the password required to decrypt a private key that is retrieved from a file. 如果私钥受空密码的保护,则该子句为可选项。This clause is optional if the private key is protected by a null password. 建议不要将私钥保存到无密码保护的文件中。Saving a private key to a file without password protection is not recommended. 如果需要密码,但是未指定密码,则该语句将失败。If a password is required but no password is specified, the statement fails.

ENCRYPTION BY PASSWORD ='password' ENCRYPTION BY PASSWORD ='password'
指定用于加密私钥的密码。Specifies the password used to encrypt the private key. 只有在需要使用密码对证书进行加密时,才使用该选项。Use this option only if you want to encrypt the certificate with a password. 如果省略该子句,则使用数据库主密钥对私钥进行加密。If this clause is omitted, the private key is encrypted using the database master key. password 必须符合运行 SQL ServerSQL Server 实例的计算机的 Windows 密码策略要求 。password must meet the Windows password policy requirements of the computer that is running the instance of SQL ServerSQL Server. 有关详细信息,请参阅 Password PolicyFor more information, see Password Policy.

SUBJECT = 'certificate_subject_name 'SUBJECT ='certificate_subject_name'
根据 X.509 标准中的定义,术语 subject 是指证书的元数据中的字段 。The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. 主题的长度应不超过 64 个字符,并且在 Linux 上的 SQL ServerSQL Server 中强制执行此限制。The subject should be no more than 64 characters long, and this limit is enforced for SQL ServerSQL Server on Linux. 对于 Windows 上的 SQL ServerSQL Server,主题的长度最多是 128 个字符。For SQL ServerSQL Server on Windows, the subject can be up to 128 characters long. 将主题存储到目录中时,如果主题的长度超过 128 个字节,则主题会被截断,但是包含证书的二进制大型对象 (BLOB) 将保留完整的主题名称。Subjects that exceed 128 characters are truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate retains the full subject name.

START_DATE ='datetime 'START_DATE ='datetime'
证书生效的日期。Is the date on which the certificate becomes valid. 如果未指定,则将 START_DATE 设置为当前日期。If not specified, START_DATE is set equal to the current date. START_DATE 采用 UTC 时间,并且可以通过可转换为日期和时间的任何格式指定。START_DATE is in UTC time and can be specified in any format that can be converted to a date and time.

EXPIRY_DATE = 'datetime 'EXPIRY_DATE ='datetime'
证书过期的日期。Is the date on which the certificate expires. 如果未指定,则将 EXPIRY_DATE 设置为 START_DATE 一年之后的日期。If not specified, EXPIRY_DATE is set to a date one year after START_DATE. EXPIRY_DATE 采用 UTC 时间,并且可以通过可转换为日期和时间的任何格式指定。EXPIRY_DATE is in UTC time and can be specified in any format that can be converted to a date and time. SQL ServerSQL Server Service Broker 会检查过期日期。Service Broker checks the expiration date. 使用证书进行加密的备份还将检查到期日期,且不会允许使用过期证书创建新备份,但会允许使用过期证书进行还原。Backup with Encryption using certificates also checks the expiration date and will not allow a new backup to be created with an expired certificate, but will allow restores with an expired certificate. 但是,在将证书用于数据库加密或 Always Encrypted 时,不会强制应用到期日期。However, expiration is not enforced when the certificate is used for database encryption or Always Encrypted.

ACTIVE FOR BEGIN_DIALOG = { ON | OFF }ACTIVE FOR BEGIN_DIALOG = { ON | OFF }
使证书可用于 Service BrokerService Broker 对话会话的发起方。Makes the certificate available to the initiator of a Service BrokerService Broker dialog conversation. 默认值为 ON。The default value is ON.

RemarksRemarks

证书是一个数据库级的安全对象,它遵循 X.509 标准并支持 X.509 V1 字段。A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE CERTIFICATE 可以从文件、二进制常量或程序集加载证书。CREATE CERTIFICATE can load a certificate from a file, a binary constant, or an assembly. 该语句也可生成密钥对并创建自我签名的证书。This statement can also generate a key pair and create a self-signed certificate.

私钥必须 <= 2500 个字节,并且为加密格式。The Private Key must be <= 2500 bytes in encrypted format. SQL ServerSQL Server 生成的私钥长度在 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及以前版本中是 1024 位,从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始是 2048 位。Private keys generated by SQL ServerSQL Server are 1024 bits long through SQL Server 2014 (12.x)SQL Server 2014 (12.x) and are 2048 bits long beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x). 从外部源导入的私钥的最小长度为 384 位,最大长度为 4,096 位。Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 4,096 bits. 导入的私钥的长度必须是 64 位的整数倍。The length of an imported private key must be an integer multiple of 64 bits. 用于 TDE 的证书限于专用密钥大小 3456 比特。Certificates used for TDE are limited to a private key size of 3456 bits.

存储证书的整个序列号,但只有前 16 个字节出现在 sys.certificates 目录视图中。The entire Serial Number of the certificate is stored but only the first 16 bytes appear in the sys.certificates catalog view.

存储证书的整个证书颁发者字段,但只有前 884 个字节出现在 sys.certificates 目录视图中。The entire Issuer field of the certificate is stored but only the first 884 bytes in the sys.certificates catalog view.

私钥必须与 certificate_name 指定的公钥相对应 。The private key must correspond to the public key specified by certificate_name.

当您通过容器创建证书时,可选择是否加载私钥。When you create a certificate from a container, loading the private key is optional. 但是当 SQL ServerSQL Server 生成自我签名的证书时,始终会创建私钥。But when SQL ServerSQL Server generates a self-signed certificate, the private key is always created. 默认情况下,私钥使用数据库主密钥进行加密。By default, the private key is encrypted using the database master key. 如果数据库主密钥不存在并且未指定密码,则该语句将失败。If the database master key does not exist and no password is specified, the statement fails.

当使用数据库主密钥对私钥进行加密时,不需要 ENCRYPTION BY PASSWORD 选项。The ENCRYPTION BY PASSWORD option is not required when the private key is encrypted with the database master key. 只有在使用密码对私钥进行加密时,才使用该选项。Use this option only when the private key is encrypted with a password. 如果未指定密码,则使用数据库主密钥对证书的私钥进行加密。If no password is specified, the private key of the certificate will be encrypted using the database master key. 如果数据库主密钥无法打开,则省略该子句会导致错误。If the master key of the database cannot be opened, omitting this clause causes an error.

如果使用数据库主密钥对私钥进行加密,则不一定必须指定解密密码。You do not have to specify a decryption password when the private key is encrypted with the database master key.

备注

内置的加密和签名功能不会检查证书的过期日期。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.

可以使用 CERTENCODED (Transact-SQL)CERTPRIVATEKEY (Transact-SQL) 函数创建证书的二进制说明。A binary description of a certificate can be created by using the CERTENCODED (Transact-SQL) and CERTPRIVATEKEY (Transact-SQL) functions. 有关使用 CERTPRIVATEKEY 和 CERTENCODED 将证书复制到其他数据库中的示例,请参阅文章 CERTENCODED (Transact-SQL) 中的示例 B 。For an example that uses CERTPRIVATEKEY and CERTENCODED to copy a certificate to another database, see example B in the article CERTENCODED (Transact-SQL).

权限Permissions

要求对数据库具有 CREATE CERTIFICATE 权限。Requires CREATE CERTIFICATE permission on the database. 只有 Windows 登录名、SQL ServerSQL Server 登录名和应用程序角色才能拥有证书。Only Windows logins, SQL ServerSQL Server logins, and application roles can own certificates. 其他组和角色不能拥有证书。Groups and roles cannot own certificates.

示例Examples

A.A. 创建自我签名的证书Creating a self-signed certificate

下面的示例创建名为 Shipping04 的证书。The following example creates a certificate called Shipping04. 该证书的私钥是使用一个密码来保护的。The private key of this certificate is protected using a password.

CREATE CERTIFICATE Shipping04   
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
   WITH SUBJECT = 'Sammamish Shipping Records',   
   EXPIRY_DATE = '20201031';  
GO  

B.B. 通过文件创建证书Creating a certificate from a file

以下示例在数据库中创建证书,并从文件加载密钥对。The following example creates a certificate in the database, loading the key pair from files.

CREATE CERTIFICATE Shipping11   
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'   
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',   
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');  
GO   

重要

Azure SQL 数据库不支持通过文件创建证书。Azure SQL Database does not support creating a certificate from a file.

C.C. 通过已签名的可执行文件创建证书Creating a certificate from a signed executable file

CREATE CERTIFICATE Shipping19   
    FROM EXECUTABLE FILE = 'c:\Shipping\Certs\Shipping19.dll';  
GO  

或者,您还可以先通过 dll 文件创建程序集,然后通过程序集创建证书。Alternatively, you can create an assembly from the dll file, and then create a certificate from the assembly.

CREATE ASSEMBLY Shipping19   
    FROM ' c:\Shipping\Certs\Shipping19.dll'   
    WITH PERMISSION_SET = SAFE;  
GO  
CREATE CERTIFICATE Shipping19 FROM ASSEMBLY Shipping19;  
GO  

重要

Azure SQL 数据库不支持通过文件创建证书。Azure SQL Database does not support creating a certificate from a file.

重要

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,CLR 严格安全性服务器配置选项可防止在未先为其设置安全性的情况下加载程序集。Starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x), the 'CLR strict security' server configuration option prevents loading assemblies without first setting up the security for them. 加载证书,从证书创建登录名,向该登录名授予 UNSAFE ASSEMBLY,然后加载程序集。Load the certificate, create a login from it, grant UNSAFE ASSEMBLY to that login, and then load the assembly.

D.D. 创建自我签名的证书Creating a self-signed certificate

下面的示例在不指定加密密码的情况下创建名为 Shipping04 的证书。The following example creates a certificate called Shipping04 without specifying an encryption password. 此示例可与 并行数据仓库Parallel Data Warehouse 一起使用。This example can be used with 并行数据仓库Parallel Data Warehouse.

CREATE CERTIFICATE Shipping04   
   WITH SUBJECT = 'Sammamish Shipping Records';  
GO  

另请参阅See Also

ALTER CERTIFICATE (Transact-SQL) ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL) DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL) BACKUP CERTIFICATE (Transact-SQL)
加密层次结构 Encryption Hierarchy
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
CERTENCODED (Transact-SQL) CERTENCODED (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)CERTPRIVATEKEY (Transact-SQL)
CERT_ID (Transact-SQL)CERT_ID (Transact-SQL)
CERTPROPERTY (Transact-SQL)CERTPROPERTY (Transact-SQL)