sys-symmetric-keys-transact-sql.md

title	sys.symmetric_keys (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/07/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.service
ms.component	system-catalog-views
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	symmetric_keys sys.symmetric_keys sys.symmetric_keys_TSQL symmetric_keys_TSQL
dev_langs	TSQL
helpviewer_keywords	sys.symmetric_keys catalog view
ms.assetid	d410eae1-3a52-45de-b9a1-52d2bd93a8eb
caps.latest.revision	41
author	edmacauley
ms.author	edmaca
manager	craigg
ms.workload	Inactive

sys.symmetric_keys (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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 c4database.
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.

Permissions

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Remarks

The RC4 algorithm is deprecated. [!INCLUDEssNoteDepFutureDontUse]

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.

See Also

Catalog Views (Transact-SQL)
Extensible Key Management (EKM)
Security Catalog Views (Transact-SQL)
Encryption Hierarchy
CREATE SYMMETRIC KEY (Transact-SQL)