Skip to content

sys-key-encryptions-transact-sql.md

Latest commit

 

History

History
52 lines (42 loc) · 2.83 KB

sys-key-encryptions-transact-sql.md

File metadata and controls

52 lines (42 loc) · 2.83 KB
title sys.key_encryptions (Transact-SQL)
description sys.key_encryptions (Transact-SQL)
author rwestMSFT
ms.author randolphwest
ms.reviewer vanto
ms.date 04/30/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
sys.key_encryptions
key_encryptions_TSQL
sys.key_encryptions_TSQL
key_encryptions
helpviewer_keywords
sys.key_encryptions catalog view
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

sys.key_encryptions (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

Returns a row for each symmetric key encryption specified by using the ENCRYPTION BY clause of the CREATE SYMMETRIC KEY statement.

To protect the key material of the symmetric key, SQL Server and Azure SQL store the key material in encrypted form. Historically, this encryption utilized PKCS#1 v1.5 padding mode; starting with database compatibility level 170, the encryption uses OAEP-256 padding mode.

Column names Data types Description
key_id int ID of the encrypted key.
thumbprint varbinary(32) SHA-1 hash of the certificate with which the key is encrypted, or the GUID of the symmetric key with which the key is encrypted.
crypt_type char(4) Type of encryption:

ESKS = Encrypted by symmetric key
ESKP, ESP2, or ESP3 = Encrypted by password
EPUC = Encrypted by certificate
EPUA = Encrypted by asymmetric key
ESKM = Encrypted by master key
C256 = Encrypted by certificate OAEP256
A256 = Encrypted by asymmetric key OAEP256
crypt_type_desc nvarchar(60) Description of encryption type:

ENCRYPTION BY SYMMETRIC KEY
ENCRYPTION BY PASSWORD
Beginning with [!INCLUDE sssql17-md]
ENCRYPTION BY CERTIFICATE
ENCRYPTION BY ASYMMETRIC KEY
ENCRYPTION BY MASTER KEY 1
ENCRYPTION BY CERTIFICATE OAEP256
ENCRYPTION BY ASYMMETRIC KEY OAEP256
crypt_property varbinary(max) Signed or encrypted bits.

1 Windows DPAPI is used to protect the SMK.

Permissions

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

Related content