Skip to content

sys-symmetric-keys-transact-sql.md

Latest commit

 

History

History
77 lines (65 loc) · 4.93 KB

sys-symmetric-keys-transact-sql.md

File metadata and controls

77 lines (65 loc) · 4.93 KB

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" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

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)