DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)
SQL Server 2012
Decrypts using a symmetric key that is automatically decrypted using an asymmetric key.
The following example shows how DecryptByKeyAutoAsymKey can be used to simplify code that performs a decryption. This code should be run on an AdventureWorks2012 database that does not already have a database master key.
--Create the keys and certificate. USE AdventureWorks2012; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^'; OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^'; CREATE ASYMMETRIC KEY SSN_AKey WITH ALGORITHM = RSA_2048 ; GO CREATE SYMMETRIC KEY SSN_Key_02 WITH ALGORITHM = DES ENCRYPTION BY ASYMMETRIC KEY SSN_AKey; GO -- --Add a column of encrypted data. ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber2 varbinary(128); OPEN SYMMETRIC KEY SSN_Key_02 DECRYPTION BY ASYMMETRIC KEY SSN_AKey; UPDATE HumanResources.Employee SET EncryptedNationalIDNumber2 = EncryptByKey(Key_GUID('SSN_Key_02'), NationalIDNumber); GO --Close the key used to encrypt the data. CLOSE SYMMETRIC KEY SSN_Key_02; -- --There are two ways to decrypt the stored data. -- --OPTION ONE, using DecryptByKey() --1. Open the symmetric key. --2. Decrypt the data. --3. Close the symmetric key. OPEN SYMMETRIC KEY SSN_Key_02 DECRYPTION BY ASYMMETRIC KEY SSN_AKey; SELECT NationalIDNumber, EncryptedNationalIDNumber2 AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber2)) AS 'Decrypted ID Number' FROM HumanResources.Employee; CLOSE SYMMETRIC KEY SSN_Key_02; -- --OPTION TWO, using DecryptByKeyAutoAsymKey() SELECT NationalIDNumber, EncryptedNationalIDNumber2 AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('SSN_AKey') , NULL ,EncryptedNationalIDNumber2)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO
Show: