CREATE ASYMMETRIC KEY (Transact-SQL)CREATE ASYMMETRIC KEY (Transact-SQL)

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

在数据库中创建非对称密钥。Creates an asymmetric key in the database.

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

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

语法Syntax

CREATE ASYMMETRIC KEY asym_key_name   
   [ AUTHORIZATION database_principal_name ]  
   [ FROM <asym_key_source> ]  
   [ WITH <key_option> ] 
   [ ENCRYPTION BY <encrypting_mechanism> ] 
   [ ; ]
  
<asym_key_source>::=  
     FILE = 'path_to_strong-name_file'  
   | EXECUTABLE FILE = 'path_to_executable_file'  
   | ASSEMBLY assembly_name  
   | PROVIDER provider_name  
  
<key_option> ::=  
   ALGORITHM = <algorithm>  
      |  
   PROVIDER_KEY_NAME = 'key_name_in_provider'  
      |  
      CREATION_DISPOSITION = { CREATE_NEW | OPEN_EXISTING }  
  
<algorithm> ::=  
      { RSA_4096 | RSA_3072 | RSA_2048 | RSA_1024 | RSA_512 }   
  
<encrypting_mechanism> ::=  
    PASSWORD = 'password'   

参数Arguments

asym_key_nameasym_key_name
数据库中非对称密钥的名称。Is the name for the asymmetric key in the database. 非对称密钥名称必须遵循标识符相关规则,并且在数据库中必须唯一。Asymmetric key names must comply with the rules for identifiers and must be unique within the database.

AUTHORIZATION database_principal_name AUTHORIZATION database_principal_name
指定非对称密钥的所有者。Specifies the owner of the asymmetric key. 所有者不能是角色或组。The owner cannot be a role or a group. 如果省略该选项,则所有者为当前用户。If this option is omitted, the owner will be the current user.

FROM asym_key_source FROM asym_key_source
指定从中加载非对称密钥对的源。Specifies the source from which to load the asymmetric key pair.

FILE = 'path_to_strong-name_file' FILE = 'path_to_strong-name_file'
指定从中加载密钥对的强名称文件所在的路径。Specifies the path of a strong-name file from which to load the key pair. 由 MAX_PATH 根据 Windows API 限制为 260 个字符。Limited to 260 characters by MAX_PATH from the Windows API.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

EXECUTABLE FILE = 'path_to_executable_file' EXECUTABLE FILE = 'path_to_executable_file'
指定要从中加载公钥的程序集文件的路径。Specifies the path of an assembly file from which to load the public key. 由 MAX_PATH 根据 Windows API 限制为 260 个字符。Limited to 260 characters by MAX_PATH from the Windows API.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

ASSEMBLY assembly_name ASSEMBLY assembly_name
指定已加载到数据库(要从中加载公钥)中的已签名程序集的名称。Specifies the name of a signed assembly that has already been loaded into the database from which to load the public key.

PROVIDER provider_name PROVIDER provider_name
指定可扩展密钥管理 (EKM) 提供程序的名称。Specifies the name of an Extensible Key Management (EKM) provider. 必须先使用 CREATE PROVIDER 语句定义此提供程序。The provider must be defined first using the CREATE PROVIDER statement. 有关外部密钥管理的详细信息,请参阅可扩展密钥管理 (EKM)For more information about external key management, see Extensible Key Management (EKM).

ALGORITHM = <algorithm>ALGORITHM = <algorithm>
可以提供五种算法;RSA_4096、RSA_3072、RSA_2048、RSA_1024 和 RSA_512。Five algorithms can be provided; RSA_4096, RSA_3072, RSA_2048, RSA_1024, and RSA_512.

RSA_1024 和 RSA_512 已弃用。RSA_1024 and RSA_512 are deprecated. 若要使用 RSA_1024 和 RSA_512(不推荐),必须将数据库设置为兼容级别 120 或更低。To use RSA_1024 or RSA_512 (not recommended) you must set the database to database compatibility level 120 or lower.

PROVIDER_KEY_NAME = 'key_name_in_provider' PROVIDER_KEY_NAME = 'key_name_in_provider'
指定来自外部提供程序的密钥名称。Specifies the key name from the external provider.

CREATION_DISPOSITION = CREATE_NEWCREATION_DISPOSITION = CREATE_NEW
在可扩展的密钥管理设备上创建新密钥。Creates a new key on the Extensible Key Management device. PROVIDER_KEY_NAME 必须用于指定设备上的密钥名称。PROVIDER_KEY_NAME must be used to specify key name on the device. 如果密钥已存在于设备上,此语句将失败,并显示错误。If a key already exists on the device the statement fails with error.

CREATION_DISPOSITION = OPEN_EXISTINGCREATION_DISPOSITION = OPEN_EXISTING
SQL ServerSQL Server 非对称密钥映射到现有可扩展的密钥管理密钥。Maps a SQL ServerSQL Server asymmetric key to an existing Extensible Key Management key. PROVIDER_KEY_NAME 必须用于指定设备上的密钥名称。PROVIDER_KEY_NAME must be used to specify key name on the device. 如果未提供 CREATION_DISPOSITION = OPEN_EXISTING,则默认值为 CREATE_NEW。If CREATION_DISPOSITION = OPEN_EXISTING is not provided, the default is CREATE_NEW.

ENCRYPTION BY PASSWORD = 'password' ENCRYPTION BY PASSWORD = 'password'
指定用于对私钥进行加密的密码。Specifies the password with which to encrypt the private key. 如果未提供该子句,则使用数据库主密钥对私钥进行加密。If this clause is not present, the private key will be encrypted with the database master key. password 最多为 128 个字符 。password is a maximum of 128 characters. 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.

RemarksRemarks

“非对称密钥”是数据库级的安全对象实体 。An asymmetric key is a securable entity at the database level. 该实体的默认格式包含公钥和私钥。In its default form, this entity contains both a public key and a private key. 当未使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会生成新的密钥对。When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. 使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会从文件中导入密钥对,或者从程序集或 DLL 文件中导入公钥。When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file, or imports a public key from an assembly or DLL file.

默认情况下,私钥受数据库主密钥保护。By default, the private key is protected by the database master key. 如果尚未创建任何数据库主密钥,则需要使用密码保护私钥。If no database master key has been created, a password is required to protect the private key.

私钥的长度可以为 512、1024 或 2048 位。The private key can be 512, 1024, or 2048 bits long.

权限Permissions

需要对数据库拥有 CREATE ASYMMETRIC KEY 权限。Requires CREATE ASYMMETRIC KEY permission on the database. 如果指定了 AUTHORIZATION 子句,则需要对数据库主体具有 IMPERSONATE 权限,或者对应用程序角色具有 ALTER 权限。If the AUTHORIZATION clause is specified, requires IMPERSONATE permission on the database principal, or ALTER permission on the application role. 只有 Windows 登录名、SQL ServerSQL Server 登录名和应用程序角色能拥有非对称密钥。Only Windows logins, SQL ServerSQL Server logins, and application roles can own asymmetric keys. 其他组和角色不能拥有非对称密钥。Groups and roles cannot own asymmetric keys.

示例Examples

A.A. 创建非对称密钥Creating an asymmetric key

下面的示例使用 RSA_2048 算法创建名为 PacificSales09 的非对称密钥,并使用密码保护私钥。The following example creates an asymmetric key named PacificSales09 by using the RSA_2048 algorithm, and protects the private key with a password.

CREATE ASYMMETRIC KEY PacificSales09   
    WITH ALGORITHM = RSA_2048   
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>';   
GO  

B.B. 通过文件创建非对称密钥,为用户提供授权Creating an asymmetric key from a file, giving authorization to a user

以下示例通过文件中存储的密钥对创建非对称密钥 PacificSales19,然后将非对称密钥的所有权授予用户 ChristinaThe following example creates the asymmetric key PacificSales19 from a key pair stored in a file, and assigns ownership of the asymmetric key to user Christina. 私钥由数据库主密钥保护,而后者必须在创建非对称密钥之前创建。The private key is protected by the database master key, which must be created prior to creating the asymmetric key.

CREATE ASYMMETRIC KEY PacificSales19  
    AUTHORIZATION Christina  
    FROM FILE = 'c:\PacSales\Managers\ChristinaCerts.tmp';  
GO  

C.C. 通过 EKM 提供程序创建非对称密钥Creating an asymmetric key from an EKM provider

以下示例通过名为 EKM_Provider1 的可扩展密钥管理提供程序中存储的密钥对,或者名为 key10_user1 的可扩展密钥管理提供程序上的密钥创建非对称密钥 EKM_askey1The following example creates the asymmetric key EKM_askey1 from a key pair stored in an Extensible Key Management provider called EKM_Provider1, and a key on that provider called key10_user1.

CREATE ASYMMETRIC KEY EKM_askey1   
    FROM PROVIDER EKM_Provider1  
    WITH   
        ALGORITHM = RSA_2048,   
        CREATION_DISPOSITION = CREATE_NEW  
        , PROVIDER_KEY_NAME  = 'key10_user1' ;  
GO  

另请参阅See Also

ALTER ASYMMETRIC KEY (Transact-SQL)ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)DROP ASYMMETRIC KEY (Transact-SQL)
ASYMKEYPROPERTY (Transact-SQL)ASYMKEYPROPERTY (Transact-SQL)
ASYMKEY_ID (Transact-SQL)ASYMKEY_ID (Transact-SQL)
选择加密算法Choose an Encryption Algorithm
加密层次结构Encryption Hierarchy
使用 Azure 密钥保管库的可扩展密钥管理 (SQL Server)Extensible Key Management Using Azure Key Vault (SQL Server)