KEY_NAME (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Returns the name of the symmetric key from either a symmetric key GUID or cipher text.
Transact-SQL syntax conventions
Syntax
KEY_NAME ( ciphertext | key_guid )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
ciphertext
Is the text encrypted by the symmetric key. cyphertext is type varbinary(8000).
key_guid
Is the GUID of the symmetric key. key_guid is type uniqueidentifier.
Returned Types
varchar(128)
Permissions
Beginning in SQL Server 2005 (9.x), the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
Examples
A. Displaying the name of a symmetric key using the key_guid
The master database contains a symmetric key named ##MS_ServiceMasterKey##. The following example gets the GUID of that key from the sys.symmetric_keys dynamic management view, assigns it to a variable, and then passes that variable to the KEY_NAME function to demonstrate how to return the name that corresponds to the GUID.
USE master;
GO
DECLARE @guid uniqueidentifier ;
SELECT @guid = key_guid FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##' ;
-- Demonstration of passing a GUID to KEY_NAME to receive a name
SELECT KEY_NAME(@guid) AS [Name of Key];
B. Displaying the name of a symmetric key using the cipher text
The following example demonstrates the entire process of creating a symmetric key and populating data into a table. The example then shows how KEY_NAME returns the name of the key when passed the encrypted text.
-- Create a symmetric key
CREATE SYMMETRIC KEY TestSymKey
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 PASSWORD = 'pGFD4bb925DGvbd2439587y' ;
GO
-- Create a table for the demonstration
CREATE TABLE DemoKey
(IDCol INT IDENTITY PRIMARY KEY,
SecretCol VARBINARY(256) NOT NULL)
GO
-- Open the symmetric key if not already open
OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO
-- Insert a row into the DemoKey table
DECLARE @key_GUID uniqueidentifier;
SELECT @key_GUID = key_guid FROM sys.symmetric_keys
WHERE name LIKE 'TestSymKey' ;
INSERT INTO DemoKey(SecretCol)
VALUES ( ENCRYPTBYKEY (@key_GUID, 'EncryptedText'))
GO
-- Verify the DemoKey data
SELECT * FROM DemoKey;
GO
-- Decrypt the data
DECLARE @ciphertext VARBINARY(256);
SELECT @ciphertext = SecretCol
FROM DemoKey WHERE IDCol = 1 ;
SELECT CAST (
DECRYPTBYKEY( @ciphertext)
AS VARCHAR(100) ) AS SecretText ;
-- Use KEY_NAME to view the name of the key
SELECT KEY_NAME(@ciphertext) AS [Name of Key] ;
See Also
sys.symmetric_keys (Transact-SQL)
ENCRYPTBYKEY (Transact-SQL)
DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho