CREATE SYMMETRIC KEY (Transact-SQL)CREATE SYMMETRIC 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

SQL ServerSQL Server 中生成对称密钥并指定其属性。Generates a symmetric key and specifies its properties in SQL ServerSQL Server.

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

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

语法Syntax

CREATE SYMMETRIC KEY key_name   
    [ AUTHORIZATION owner_name ]  
    [ FROM PROVIDER provider_name ]  
    WITH 
      [
          <key_options> [ , ... n ]  
        | ENCRYPTION BY <encrypting_mechanism> [ , ... n ] 
      ]
  
<key_options> ::=  
      KEY_SOURCE = 'pass_phrase'  
    | ALGORITHM = <algorithm>  
    | IDENTITY_VALUE = 'identity_phrase'  
    | PROVIDER_KEY_NAME = 'key_name_in_provider'   
    | CREATION_DISPOSITION = {CREATE_NEW | OPEN_EXISTING }  
  
<algorithm> ::=  
    DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128  
    | DESX | AES_128 | AES_192 | AES_256   
  
<encrypting_mechanism> ::=  
      CERTIFICATE certificate_name   
    | PASSWORD = 'password'   
    | SYMMETRIC KEY symmetric_key_name   
    | ASYMMETRIC KEY asym_key_name  

参数Arguments

Key_name Key_name
指定在数据库中识别该对称密钥的唯一名称。Specifies the unique name by which the symmetric key is known in the database. 当 key_name 以一个数字符号 (#) 为开头时指定临时密钥。Temporary keys are designated when the key_name begins with one number (#) sign. 例如,#temporaryKey900007。 For example, #temporaryKey900007. 您不能创建名称以多个 # 开头的对称密钥。You cannot create a symmetric key that has a name that starts with more than one #. 您不能使用 EKM 提供程序创建临时对称密钥。You cannot create a temporary symmetric key using an EKM provider.

AUTHORIZATION owner_name AUTHORIZATION owner_name
指定将拥有此密钥的数据库用户或应用程序角色的名称。Specifies the name of the database user or application role that will own this key.

FROM PROVIDER provider_name FROM PROVIDER provider_name
指定可扩展密钥管理 (EKM) 提供程序和名称。Specifies an Extensible Key Management (EKM) provider and name. 该密钥不是从 EKM 设备中导出的。The key is not exported from the EKM device. 必须先使用 CREATE PROVIDER 语句定义此提供程序。The provider must be defined first using the CREATE PROVIDER statement. 有关创建外部密钥提供程序的详细信息,请参阅可扩展密钥管理 (EKM)For more information about creating external key providers, see Extensible Key Management (EKM).

备注

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

KEY_SOURCE =' pass_phrase 'KEY_SOURCE ='pass_phrase'
指定从中派生密钥的通行短语。Specifies a pass phrase from which to derive the key.

IDENTITY_VALUE =' identity_phrase 'IDENTITY_VALUE ='identity_phrase'
指定一个标识短语,将根据该短语生成 GUID 以标记使用临时密钥加密的数据。Specifies an identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key.

PROVIDER_KEY_NAME =' key_name_in_provider 'PROVIDER_KEY_NAME ='key_name_in_provider'
指定在可扩展密钥管理提供程序中引用的名称。Specifies the name referenced in the Extensible Key Management provider.

备注

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

CREATION_DISPOSITION = CREATE_NEW CREATION_DISPOSITION = CREATE_NEW
在可扩展的密钥管理设备上创建新密钥。Creates a new key on the Extensible Key Management device. 如果密钥已存在于设备上,该语句将失败,并显示错误。If a key already exists on the device, the statement fails with error.

CREATION_DISPOSITION = OPEN_EXISTING CREATION_DISPOSITION = OPEN_EXISTING
将一个 SQL ServerSQL Server 对称密钥映射到现有的可扩展密钥管理密钥。Maps a SQL ServerSQL Server symmetric key to an existing Extensible Key Management key. 如果未提供 CREATION_DISPOSITION = OPEN_EXISTING,则此参数默认为 CREATE_NEW。If CREATION_DISPOSITION = OPEN_EXISTING is not provided, this defaults to CREATE_NEW.

certificate_name certificate_name
指定将用于对对称密钥进行加密的证书的名称。Specifies the name of the certificate that will be used to encrypt the symmetric key. 该证书必须已存在于数据库中。The certificate must already exist in the database.

' password ' ' password '
指定一个密码,从该密码派生出用来保护对称密钥的 TRIPLE_DES 密钥。Specifies a password from which to derive a TRIPLE_DES key with which to secure the symmetric 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. 始终使用强密码。Always use strong passwords.

symmetric_key_namesymmetric_key_name
指定用于对要创建的密钥进行加密的对称密钥。Specifies a symmetric key, used to encrypt the key that is being created. 指定的密钥必须已存在于数据库中,并且必须打开。The specified key must already exist in the database, and the key must be open.

asym_key_nameasym_key_name
指定用于对要创建的密钥进行加密的非对称密钥。Specifies an asymmetric key, used to encrypt the key that is being created. 此非对称密钥必须已经存在于数据库中。This asymmetric key must already exist in the database.

<algorithm><algorithm>
指定加密算法。Specify the encrypting algorithm.

警告

SQL Server 2016 (13.x)SQL Server 2016 (13.x)开始,除 AES_128、AES_192 和 AES_256 以外的所有算法都已过时。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), all algorithms other than AES_128, AES_192, and AES_256 are deprecated. 若要使用旧算法(不推荐),必须将数据库的数据库兼容级别设置为 120 或更低。To use older algorithms (not recommended), you must set the database to database compatibility level 120 or lower.

备注Remarks

创建对称密钥时,必须至少使用以下项之一来对该对称密钥进行加密:证书、密码、对称密钥、非对称密钥或 PROVIDER。When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. 可使用上述每种类型中的多项对密钥进行加密。The key can have more than one encryption of each type. 换言之,可以同时使用多个证书、密码、对称密钥以及非对称密钥对单个对称密钥进行加密。In other words, a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time.

注意

使用密码而非证书(或另一个密钥)对对称密钥进行加密时,使用 TRIPLE DES 加密算法来加密密码。When a symmetric key is encrypted with a password instead of a certificate (or another key), the TRIPLE DES encryption algorithm is used to encrypt the password. 因此,用强加密算法(如 AES)创建的密钥本身受较弱算法的保护。Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm.

在将对称密钥分发给多个用户之前,可以使用可选的密码对该密钥进行加密。The optional password can be used to encrypt the symmetric key before distributing the key to multiple users.

临时密钥由创建它们的用户所拥有。Temporary keys are owned by the user that creates them. 临时密钥只对当前会话有效。Temporary keys are only valid for the current session.

IDENTITY_VALUE 生成一个 GUID,使用该 GUID 来标记使用新对称密钥加密的数据。IDENTITY_VALUE generates a GUID with which to tag data that is encrypted with the new symmetric key. 该标记可用于将密钥与加密数据进行匹配。This tagging can be used to match keys to encrypted data. 由某个特定短语生成的 GUID 始终相同。The GUID generated by a specific phrase is always the same. 在使用短语生成了 GUID 之后,只要存在至少一个正在使用该短语的会话,就不能再次使用该短语。After a phrase has been used to generate a GUID, the phrase cannot be reused as long as there is at least one session that is actively using the phrase. IDENTITY_VALUE 是一个可选的子句;但是,我们建议在存储使用临时密钥加密的数据时使用该子句。IDENTITY_VALUE is an optional clause; however, we recommend using it when you are storing data encrypted with a temporary key.

没有默认的加密算法。There is no default encryption algorithm.

重要

建议不要使用 RC4 和 RC4_128 序列密码保护敏感数据。We do not recommend using the RC4 and RC4_128 stream ciphers to protect sensitive data. SQL ServerSQL Server 对利用此类密钥执行的加密不会进行进一步的编码。does not further encode the encryption performed with such keys.

可以在 sys.symmetric_keys 目录视图中查看对称密钥的有关信息。Information about symmetric keys is visible in the sys.symmetric_keys catalog view.

使用通过加密提供程序创建的对称密钥无法加密对称密钥。Symmetric keys cannot be encrypted by symmetric keys created from the encryption provider.

关于 DES 算法的说明:Clarification regarding DES algorithms:

  • DESX 的命名不正确。DESX was incorrectly named. 使用 ALGORITHM = DESX 创建的对称密钥实际上使用的是具有 192 位密钥的 TRIPLE DES 密码。Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. 不提供 DESX 算法。The DESX algorithm is not provided. 此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
  • 使用 ALGORITHM = TRIPLE_DES_3KEY 创建的对称密钥使用的是具有 192 位密钥的 TRIPLE DES。Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key.
  • 使用 ALGORITHM = TRIPLE_DES 创建的对称密钥使用的是具有 128 位密钥的 TRIPLE DES。Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.

不推荐使用 RC4 算法: Deprecation of the RC4 algorithm:

对不同数据块重复使用相同的 RC4 或 RC4_128 KEY_GUID 会产生相同的 RC4 密钥,因为 SQL ServerSQL Server 不会自动提供 salt。Repeated use of the same RC4 or RC4_128 KEY_GUID on different blocks of data, results in the same RC4 key because SQL ServerSQL Server does not provide a salt automatically. 重复使用相同的 RC4 密钥是已知错误,将导致加密非常不可靠。Using the same RC4 key repeatedly is a well known error that will result in very weak encryption. 因此,不推荐使用 RC4 和 RC4_128 关键字。Therefore we have deprecated the RC4 and RC4_128 keywords. 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请不要在新的开发工作中使用该功能,并尽快修改当前还在使用该功能的应用程序。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

警告

RC4 算法仅用于支持向后兼容性。The RC4 algorithm is only supported for backward compatibility. 仅当数据库兼容级别为 90 或 100 时,才能使用 RC4 或 RC4_128 对新材料进行加密。New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (建议不要使用。)而是使用一种较新的算法,如 AES 算法之一。(Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,可以通过任何兼容级别对使用 RC4 或 RC4_128 加密的材料进行解密。In SQL Server 2019 (15.x)SQL Server 2019 (15.x) material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

权限Permissions

要求对数据库具有 ALTER ANY SYMMETRIC KEY 权限。Requires ALTER ANY SYMMETRIC KEY permission on the database. 如果指定了 AUTHORIZATION,则要求对数据库用户具有 IMPERSONATE 权限,或者对应用程序角色具有 ALTER 权限。If AUTHORIZATION is specified, requires IMPERSONATE permission on the database user or ALTER permission on the application role. 如果使用证书或非对称密钥进行加密,则要求对证书或非对称密钥具有 VIEW DEFINITION 权限。If encryption is by certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. 只有 Windows 登录名、 SQL ServerSQL Server 登录名和应用程序角色才能拥有对称密钥。Only Windows logins, SQL ServerSQL Server logins, and application roles can own symmetric keys. 其他组和角色不能拥有对称密钥。Groups and roles cannot own symmetric keys.

示例Examples

A.A. 创建对称密钥Creating a symmetric key

下面的示例使用 JanainaKey09 算法创建名为 AES 256 的对称密钥,然后使用证书 Shipping04 对新密钥进行加密。The following example creates a symmetric key called JanainaKey09 by using the AES 256 algorithm, and then encrypts the new key with certificate Shipping04.

CREATE SYMMETRIC KEY JanainaKey09   
WITH ALGORITHM = AES_256  
ENCRYPTION BY CERTIFICATE Shipping04;  
GO  

B.B. 创建临时对称密钥Creating a temporary symmetric key

下面的示例用通行短语 #MarketingXXV 创建了一个名为 The square of the hypotenuse is equal to the sum of the squares of the sides 的临时对称密钥。The following example creates a temporary symmetric key called #MarketingXXV from the pass phrase: The square of the hypotenuse is equal to the sum of the squares of the sides. 为该密钥提供了通过字符串 Pythagoras 生成的 GUID,并使用证书 Marketing25 对该密钥进行加密。The key is provisioned with a GUID that is generated from the string Pythagoras and encrypted with certificate Marketing25.

  
CREATE SYMMETRIC KEY #MarketingXXV   
WITH ALGORITHM = AES_128,  
KEY_SOURCE   
     = 'The square of the hypotenuse is equal to the sum of the squares of the sides',  
IDENTITY_VALUE = 'Pythagoras'  
ENCRYPTION BY CERTIFICATE Marketing25;  
GO  

C.C. 使用可扩展密钥管理 (EKM) 设备创建对称密钥Creating a symmetric key using an Extensible Key Management (EKM) device

下面的示例使用一个名为 MySymKey 的提供程序和一个密钥名称 MyEKMProvider 创建一个名为 KeyForSensitiveData 的对称密钥。The following example creates a symmetric key called MySymKey by using a provider called MyEKMProvider and a key name of KeyForSensitiveData. 该示例向 User1 授权,并假定系统管理员已在 SQL ServerSQL Server 中注册了名为 MyEKMProvider 的提供程序。It assigns authorization to User1 and assumes that the system administrator has already registered the provider called MyEKMProvider in SQL ServerSQL Server.

CREATE SYMMETRIC KEY MySymKey  
AUTHORIZATION User1  
FROM PROVIDER EKMProvider  
WITH  
PROVIDER_KEY_NAME='KeyForSensitiveData',  
CREATION_DISPOSITION=OPEN_EXISTING;  
GO  

另请参阅See Also

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