CREATE SYMMETRIC KEY (Transact-SQL)
Generates a symmetric key and specifies its properties.
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
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.
 Caution |
|---|
When a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used. 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 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. The GUID generated by a specific phrase will always be the same. 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 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.
| Important |
|---|
We do not recommend using the RC4 and RC4_128 stream ciphers to protect sensitive data. SQL Server does not further encode the encryption performed with such 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.
Clarification regarding DES algorithms:
DESX was incorrectly named. Symmetric keys created with ALGORITHM = DESX actually use the TRIPLE DES cipher with a 192-bit key. The DESX algorithm is not provided. This feature will 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.
Symmetric keys created with ALGORITHM = TRIPLE_DES_3KEY use TRIPLE DES with a 192-bit key.
Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.
Deprecation of the RC4 algorithm:
Repeated use of the same RC4 or RC4_128 KEY_GUID on different blocks of data will result in the same RC4 key because SQL Server does not provide a salt automatically. Using the same RC4 key repeatedly is a well known error that will result in very weak encryption. Therefore we have deprecated the RC4 and RC4_128 keywords. 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.
Requires ALTER ANY SYMMETRIC KEY permission on the database. If AUTHORIZATION is specified, requires IMPERSONATE permission on the database user or ALTER permission on the application role. If encryption is by certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. Only Windows logins, SQL Server logins, and application roles can own symmetric keys. Groups and roles cannot own symmetric keys.
A. Creating a symmetric key
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. Creating a temporary symmetric key
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. 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. Creating a symmetric key using an Extensible Key Management (EKM) device
The following example creates a symmetric key called MySymKey by using a provider called MyEKMProvider and a key name of KeyForSensitiveData. It assigns authorization to User1 and assumes that the system administrator has already registered the provider called MyEKMProvider in SQL Server.
CREATE SYMMETRIC KEY MySymKey AUTHORIZATION User1 FROM PROVIDER EKMProvider WITH PROVIDER_KEY_NAME='KeyForSensitiveData', CREATION_DISPOSITION=OPEN_EXISTING; GO
