| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.symmetric_keys (Transact-SQL) |
sys.symmetric_keys (Transact-SQL) |
rwestMSFT |
randolphwest |
03/07/2017 |
sql |
system-objects |
reference |
|
|
|
|
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]
Returns one row for every symmetric key created with the CREATE SYMMETRIC KEY statement.
| Column name | Data type | Description |
|---|---|---|
| name | sysname | Name of the key. Unique within the database. |
| principal_id | int | ID of the database principal who owns the key. |
| symmetric_key_id | int | ID of the key. Unique within the database. |
| key_length | int | Length of the key in bits. |
| key_algorithm | char(2) | Algorithm used with the key: R2 = RC2 R4 = RC4 D = DES D3 = Triple DES DT = TRIPLE_DES_3KEY DX = DESX A1 = AES 128 A2 = AES 192 A3 = AES 256 NA = EKM Key |
| algorithm_desc | nvarchar(60) | Description of the algorithm used with the key: RC2 RC4 DES Triple_DES TRIPLE_DES_3KEY DESX AES_128 AES_192 AES_256 NULL (Extensible Key Management algorithms only) |
| create_date | datetime | Date the key was created. |
| modify_date | datetime | Date the key was modified. |
| key_guid | uniqueidentifier | Globally unique identifier (GUID) associated with the key. It is auto-generated for persisted keys. GUIDs for temporary keys are derived from the user-supplied pass phrase. |
| key_thumbprint | sql_variant | SHA-1 hash of the key. The hash is globally unique. For non-Extensible Key Management keys this value will be NULL. |
| provider_type | nvarchar(120) | Type of cryptographic provider: CRYPTOGRAPHIC PROVIDER = Extensible Key Management keys NULL = Non-Extensible Key Management keys |
| cryptographic_provider_guid | uniqueidentifier | GUID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL. |
| cryptographic_provider_algid | sql_variant | Algorithm ID for the cryptographic provider. For non-Extensible Key Management keys this value will be NULL. |
[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.
The RC4 algorithm is deprecated. [!INCLUDEssNoteDepFutureAvoid]
Note
The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In [!INCLUDEssSQL11] material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
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. [!INCLUDEssNoteDepFutureAvoid]
-
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.
Catalog Views (Transact-SQL)
Extensible Key Management (EKM)
Security Catalog Views (Transact-SQL)
Encryption Hierarchy
CREATE SYMMETRIC KEY (Transact-SQL)