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

適用於: 是SQL Server 是Azure SQL Database 否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_nameAUTHORIZATION 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_sourceFROM 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. 根據 Windows API 的 MAX_PATH,限制為 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. 根據 Windows API 的 MAX_PATH,限制為 260 個字元。Limited to 260 characters by MAX_PATH from the Windows API.

注意

自主資料庫無法使用這個選項。This option is not available in a contained database.

ASSEMBLY assembly_nameASSEMBLY 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_namePROVIDER 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

下列範例會利用 PacificSales09 演算法建立一個名稱為 RSA_2048 的非對稱金鑰,並利用密碼保護私密金鑰。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 的可延伸金鑰管理提供者中所儲存的金鑰組來建立非對稱金鑰 EKM_askey1,並在稱為 key10_user1 的提供者上建立金鑰。The 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)