THIS TOPIC APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
Decrypts data by using a symmetric key.
Is data that has been encrypted with the key. ciphertext is varbinary.
Is a variable of type varbinary that contains data that has been encrypted with the key.
Indicates whether an authenticator was encrypted together with the plaintext. Must be the same value passed to EncryptByKey when encrypting the data. add_authenticator is int.
Is data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey. authenticator is sysname.
Is a variable that contains data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey.
varbinary with a maximum size of 8,000 bytes.
DecryptByKey uses a symmetric key. This symmetric key must already be open in the database. There can be multiple keys open at the same time. You do not have to open the key immediately before decrypting the cipher text.
Symmetric encryption and decryption is relatively fast, and is suitable for working with large amounts of data.
Requires the symmetric key to have been opened in the current session. For more information, see OPEN SYMMETRIC KEY (Transact-SQL).
The following example decrypts ciphertext by using a symmetric key.
-- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO -- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match. SELECT NationalIDNumber, EncryptedNationalID AS 'Encrypted ID Number', CONVERT(nvarchar, DecryptByKey(EncryptedNationalID)) AS 'Decrypted ID Number' FROM HumanResources.Employee; GO
The following example decrypts data that was encrypted together with an authenticator.
-- First, open the symmetric key with which to decrypt the data OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; GO -- Now list the original card number, the encrypted card number, -- and the decrypted ciphertext. If the decryption worked, -- the original number will match the decrypted number. SELECT CardNumber, CardNumber_Encrypted AS 'Encrypted card number', CONVERT(nvarchar, DecryptByKey(CardNumber_Encrypted, 1 , HashBytes('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'Decrypted card number' FROM Sales.CreditCard; GO