Encrypt a Column of Data

This topic describes how to encrypt a column of data by using symmetric encryption in SQL Server 2012 using Transact-SQL.

In This Topic

  • Before you begin:

    Security

  • To encrypt a column of data, using Transact-SQL

Before You Begin

Security

Permissions

The following permissions are necessary to perform the steps below:

  • CONTROL permission on the database.

  • CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

  • ALTER permission on the table.

  • Some permission on the key and must not have been denied VIEW DEFINITION permission.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To encrypt a column of data using a simple symmetric encryption

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;
    --If there is no master key, create one now. 
    IF NOT EXISTS 
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY 
        PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO
    
    CREATE CERTIFICATE Sales09
       WITH SUBJECT = 'Customer Credit Card Numbers';
    GO
    
    CREATE SYMMETRIC KEY CreditCards_Key11
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE Sales09;
    GO
    
    -- Create a column in which to store the encrypted data.
    ALTER TABLE Sales.CreditCard 
        ADD CardNumber_Encrypted varbinary(128); 
    GO
    
    -- Open the symmetric key with which to encrypt the data.
    OPEN SYMMETRIC KEY CreditCards_Key11
       DECRYPTION BY CERTIFICATE Sales09;
    
    -- Encrypt the value in column CardNumber using the
    -- symmetric key CreditCards_Key11.
    -- Save the result in column CardNumber_Encrypted.  
    UPDATE Sales.CreditCard
    SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
        , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
        , CreditCardID)));
    GO
    
    -- Verify the encryption.
    -- 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
    

To encrypt a column of data using symmetric encryption that includes an authenticator

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;
    GO
    
    --If there is no master key, create one now. 
    IF NOT EXISTS 
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY 
        PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
    GO
    
    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO
    
    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO
    
    USE [AdventureWorks2012];
    GO
    
    -- Create a column in which to store the encrypted data.
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128); 
    GO
    
    -- Open the symmetric key with which to encrypt the data.
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    
    -- Encrypt the value in column NationalIDNumber with symmetric 
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO
    
    -- Verify the encryption.
    -- 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, EncryptedNationalIDNumber 
        AS 'Encrypted ID Number',
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
        AS 'Decrypted ID Number'
        FROM HumanResources.Employee;
    GO
    

For more information, see the following:

Arrow icon used with Back to Top link [Top]