Export (0) Print
Expand All

KEY_NAME (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the name of the symmetric key from either a symmetric key GUID or cipher text.

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12 (Preview in some regions).

Topic link icon Transact-SQL Syntax Conventions

KEY_NAME ( ciphertext | key_guid )

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.

varchar(128)

Beginning in SQL Server 2005, 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.

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] ;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft