Encrypting Data In SQL Server

This article covers the use of Transparent Database Encryption (TDE) in SQL Server. First we need to create a database master key. This is a symmetric key created at the database level and used to encrypt certificates within the database.

IF NOT EXISTS

    (SELECT * FROM sys. symmetric_keys WHERE symmetric_key_id = 101 )

    CREATE MASTER KEY ENCRYPTION BY

    PASSWORD = 'Password1'

GO

Now create the certificate - by default the certificate will create a private key which is encrypted using the database master key.

CREATE CERTIFICATE <Certificate Name>

   WITH SUBJECT = 'Certificate Description';

GO

Now create the symmetric key which will be used to encrypt data. This will be encrypted using the certificate created above.

CREATE SYMMETRIC KEY <Symmetric Key Name>

    WITH ALGORITHM = AES_256

    ENCRYPTION BY CERTIFICATE <Certificate Name>;

GO

Once created users need to be assigned permissions to use the key. Minimum permissions are as follows:

GRANT CONTROL ON CERTIFICATE :: <Certificate Name> TO <Principal>

GRANT VIEW DEFINITION ON SYMMETRIC KEY :: <Symmetric Key Name> TO <Principal>

Now we can encrypt data by calling the EncryptByKey function:

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY <Symmetric Key Name>
   DECRYPTION BY CERTIFICATE <Certificate Name>;

-- Encrypt the value in column with symmetric key
UPDATE <table>
SET <Encrypted Column>
    = EncryptByKey(Key_GUID(<Unencrypted Value>));

To decrypt the column we need to perform a similar process:

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY <Symmetric Key Name>
   DECRYPTION BY CERTIFICATE <Certificate Name>;

SELECT    <Unencrypted Column>, 
          <Encrypted Column> AS 'Encrypted ID Number', 
          CONVERT(nvarchar, DecryptByKey(<Encrypted Column>)) AS 'Decrypted Column 
FROM <table>;

Key Concerns

Database users need CONTROL permission on the certificate - this provides not only access to the certificate but also the ability to DROP and ALTER the certificate. The users shouldn't be able to drop the certificate however because there is a dependency with the symmetric key that is created. Having said that there are other metadata issues in SQL Server and I wouldn't like to rely on this.

The consequence of dropping the certificate / symmetric key is that encrypted data cannot easily be recovered. Re-creating certificates and keys in the database with the same names will not allow data to be decrypted. To fix the encryption issues a backup of the database with a good copy of the key and certificate would need to be restored to the server. The key in this database could be used to decrypt the data in the original database. The encrypted data could then be re-written using regenerated certificate and key. This may take a long time to restore if there is a large amount of encrypted data to recover.