CREATE SYMMETRIC KEY (Transact-SQL)
Generates a symmetric key and specifies its properties.
Transact-SQL Syntax Conventions
CREATE SYMMETRIC KEY key_name [ AUTHORIZATION owner_name ] WITH <key_options> [ , ... n ] ENCRYPTION BY <encrypting_mechanism> [ , ... n ] <encrypting_mechanism> ::= CERTIFICATE certificate_name | PASSWORD = 'password' | SYMMETRIC KEY symmetric_key_name | ASYMMETRIC KEY asym_key_name <key_options> ::= KEY_SOURCE = 'pass_phrase' | ALGORITHM = <algorithm> | IDENTITY_VALUE = 'identity_phrase' <algorithm> ::= DES | TRIPLE_DES | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256
Is the unique name by which the symmetric key is known in the database. The names of temporary keys should begin with one number (#) sign. For example, #temporaryKey900007. You cannot create a symmetric key that has a name that starts with more than one #.
- AUTHORIZATION owner_name
Specifies the name of the database user or application role that will own this key.
Specifies the name of the certificate that will be used to encrypt the symmetric key. The certificate must already exist in the database.
Specifies a password from which to derive a TRIPLE_DES key with which to secure the symmetric key. Password complexity will be checked. You should always use strong passwords.
Specifies a symmetric key to be used to encrypt the key that is being created. The specified key must already exist in the database, and the key must be open.
Specifies an asymmetric key to be used to encrypt the key that is being created. This asymmetric key must already exist in the database.
- KEY_SOURCE ='pass_phrase'
Specifies a pass phrase from which to derive the key.
- IDENTITY_VALUE ='identity_phrase'
Specifies an identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key.
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. 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.
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 in the current session unless the associated symmetric key has been dropped. 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.
We do not recommend using the RC4 and RC4_128 stream ciphers to protect sensitive data. SQL Server 2005 does not salt the encryption performed with such keys.
Information about symmetric keys is visible in the sys.symmetric_keys catalog view.
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.
- Symmetric keys created with ALGORITHM = TRIPLE_DES use TRIPLE DES with a 128-bit key.
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.
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
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
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
ALTER SYMMETRIC KEY (Transact-SQL)
DROP SYMMETRIC KEY (Transact-SQL)
Choosing an Encryption Algorithm
Help and Information
Getting SQL Server 2005 Assistance
5 December 2005
17 November 2008