Home Library Learn Downloads Support Community
Sign in | United States - English | |
5 out of 8 rated this helpful - Rate this topic

DecryptByKey (Transact-SQL)

SQL Server 2005

Decrypts data by using a symmetric key.

Topic link icon Transact-SQL Syntax Conventions

Copy 

DecryptByKey ( { 'ciphertext' | @ciphertext } 
              [ , add_authenticator 
                                , { authenticator | @authenticator } 
              ]
                          )
ciphertext

Is data that has been encrypted with the key. varbinary.

@ciphertext

Is a variable of type varbinary that contains data that has been encrypted with the key.

add_authenticator

Indicates whether an authenticator was encrypted together with the plaintext. Must be the same value passed to EncryptByKey when encrypting the data. int.

authenticator

Is data from which to generate an authenticator. Must match the value that was supplied to EncryptByKey. sysname.

@authenticator

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.

A. Decrypting by using a symmetric key

The following example decrypts ciphertext by using a symmetric key.

Copy 
-- 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

B. Decrypting by using a symmetric key and an authenticating hash

The following example decrypts data that was encrypted together with an authenticator.

Copy 
-- 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
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
To permanently decrypt a previously encrypted column...

Using EncryptByKey to encrypt a column is a one-time solution that requires you to use DecryptByKey each and every time you wish to gain access to the underlying encrypted data. If, however, your business logic no longer requires you to maintain encryption on the column and you wish to return the column to its previous plaintext state, you must create an extra column in the target table to hold the data.

Following the example provided on the EncryptByKey BOL page, suppose you encrypted the NationalIDNumber column in the following manner:

UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO

To view the NationalIDNumber column in a decrypted state, you would run DecryptByKey on the column. However, supposing you wished to return this column to a permanently unencrypted state, you could add a new column with the target data type, decrypt the data, transfer it to the column, and then either delete the old column and rename the new to match the old, or keep both columns depending on your organization's business logic. This would look as follows:

ALTER TABLE HumanResources.Employee
ADD NationalIDNumber_unencrypted varchar(100)
GO


UPDATE HumanResources.Employee


SET NationalIDNumber_unencrypted = CONVERT(varchar(100), DecryptByKey(NationalIDNumber))


GO




-- Optional depending on your business logic and your columns' naming needs.


ALTER TABLE HumanResources.Employee


DROP COLUMN NationalIDNumber


GO


EXEC sp_rename 'HumanResources.Employee.NationalIDNumber_unencrypted', 'NationalIDNumber', 'COLUMN';






For a temp table solution to the permanent decryption problem, and more discussion of this issue, check out this post: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3019745&SiteID=1

        

        

            History
        

        
        

        
        

        

             

             
               
                
            

            
            
        

        
    

    




    
    

      
      

              You don't have to specify the GUID because... 
                
        
 
        

            
You don't have to specify key's GUID because it is prepended to the encrypted data. Even if you use multiple symmetric key, different key's GUID is prepended each time. I checked it by using symmetric keys encrypted with a self-signed certificate. Great resource on this here: http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx. 


By the way, if you do DecryptByKey and the symmetric key (I only tried it with symmetric keys, sorry!) is not opened, then the function returns a NULL.

        

        

            History