Encryption Fundamentals

Database security is like security in general: every step you take offers a degree of protection, but no single step nor combination of steps can ever offer “complete” or “guaranteed” security. The goal is simply to make it sufficiently difficult to extract sensitive information so as to deter intrusion or delay it long enough to detect and actively counter it.

Think of securing your house: first you install doors, then you add a lock and key, then you might add a second lock with a second key, then a security system, then a guard, then an armed guard, and so on until you are satisfied with the security measures. However, even with all of that in place, all it takes is someone with two keys, the security system code, and enough force to overpower your armed guard and then your house is compromised. Bottom line: no security system is without weakness, but the more measures you have in place the more it deters and prevents would-be attackers, hence the more secure it is.

Of course, the balance is the cost and, in the case of databases, the performance overhead. Adding highly secure encryption to every aspect and byte of data in a database can be done, but it results in a correspondingly high amount of time and resources (CPU processing, etc.) to interface with the database with authorized credentials. Sometimes that level of security is warranted; and sometimes it is not.

SQL Server has built-in security measures that are robust, configurable, and offer multiple layers of security. One of the most basic implementations is securing a column of data with a symmetric key. In a future post I will discuss more details on the different types of security, but, as a basic example, let’s walk through this common implementation.

Demonstration Preparation: For demonstration purposes, let’s create a table to store personnel information such as name and social security number (national id). We will store the social security number in plain-text as well as in encrypted form in this example to make it easier to confirm our underlying data. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- Set context to the DB database

-- If the personnel table already exists, drop it
IF OBJECT_ID('DB.dbo.tblPersonnel') IS NOT NULL
    DROP TABLE DB.dbo.tblPersonnel

-- Create the personnel table
CREATE TABLE DB.dbo.tblPersonnel (
    , Name NVARCHAR(50) NOT NULL
    , SocialSecurityNumber CHAR(11) NOT NULL
    , SocialSecurityNumberEncrypted VARBINARY(128) NULL

-- Insert a record into the table
INSERT INTO DB.dbo.tblPersonnel (
    , SocialSecurityNumber)
    , '123-45-6789')

-- Select the records from the table to confirm values
    , Name
    , SocialSecurityNumber
    , SocialSecurityNumberEncrypted
FROM DB.dbo.tblPersonnel

Solution: The basic encryption hierarchy that we will use goes from the service master key to the database master key to a database security certificate to a database symmetric key to the encrypted data column itself. If it sounds complicated, that is good. Recall that security measures are (and should be!) multi-level, with each level providing one more hurdle to overcome for those that want unauthorized access.

Let’s walk through the levels one at a time.

The service master key is created automatically by SQL Server and is created for the operating system account under which the SQL Server service runs. This is an important point because, if the system account for SQL Server is changed, the existing service master key will not work. There are, of course, ways to correct that, but just be aware that once the service master key is created, changes to the SQL Server service account will have an impact on security. Run the following query to see your server’s service master key.

-- View the symmetric keys for the master database
FROM master.sys.symmetric_keys

You’ll see the service master key listed with the name ##MS_ServiceMasterKey## and a symmetric_key_id of 102. If your server does not have a service master key, don’t worry. It will be created automatically after we create a database master key in the next step.

The database master key is unique to each database on a server and will not exist until it is specifically created. The database master key is encrypted by the service master key. Thus, as mentioned previously, if the service master key doesn’t already exist, creating a database master key will automatically result in a service master key being created. Let’s take a look and see if there is a database master key in our database. Run the following query to see all of the symmetric keys for a database and look for a name of ##MS_DatabaseMasterKey## and a symmetric_key_id of 101.

-- View the symmetric keys for the DB database
FROM DB.sys.symmetric_keys

Now let’s create a database master key if one does not already exist.

-- If there is no database master key, create one
--   Please use a more secure password than in the
--   example; remember, this is just a SAMPLE!

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

One of the most often overlooked tasks is to backup your encryption keys. If you lose your credentials or need to perform disaster recovery, you’ll be glad you have it. But don’t just create a backup and leave it on the production server. Archive your backup keys on a separate server, preferably under equal or greater security and in a geographically separate area. Remember — this is part of good disaster recovery and security practice and should be treated with appropriate seriousness! In the example below we will export the database master key to a folder on our M: drive and the backup will be encrypted with a password distinct from the password on the key itself. More levels of security! Do you see a pattern developing here? 😉

-- Backup your database master key!
BACKUP MASTER KEY TO FILE = 'M:\MasterKeys\DB_MasterKey'

Next comes our certificate. A certificate exists at the database level and can be encrypted by either the database master key or a password. For this example we will encrypt with the database master key.

-- Create a certificate for the data to be encrypted
--   By not specifying a password, SQL knows to use the
--   database master key to encrypt the certificate
    WITH SUBJECT = 'Personnel Records'

-- Backup your certificate!
    TO FILE = 'M:\Certificates\DB_certPersonnel'

Now we’ll use our certificate to encrypt a symmetric key. A symmetric key can be used to encrypt as well as decrypt data, compared to an asymmetric key pair in which one key (private) can encrypt and decrypt and a second key (public) can only decrypt. The primary benefit of using a symmetric key is speed: it is a relatively quick process to encrypt/decrypt with a symmetric key and thus it is suitable for use in high-volume data of moderate sensitivity.

-- Create a symmetric key using the certificate for encryption

Finally, the setup is complete and we are ready to encrypt a column of data. We’ll start by opening our symmetric key. When a key is opened it stays open for the session unless it is specifically closed. The following statement will open our symmetric key; it could be wrapped in an IF statement to check if the key is already open, but that is not necessary as, if it is already open, an additional OPEN statement will have no effect.

-- Open our personnel symmetric key

Now for the fun part… let’s encrypt some data! We’ll use the ENCRYPTBYKEY function and, for this example, we will NOT use the optional authenticator. Generally I would always use an additional authenticator to prevent a whole-value substitution attack; but, for the sake of simplicity in this example, we will omit the authenticator. However, refer to a future post to understand why the optional authentictor is so important.

-- Encrypt the data
--   Note: this encryption is unsecured against whole-value
--   substitution; see follow-up post for more information

UPDATE DB.dbo.tblPersonnel
SET SocialSecurityNumberEncrypted = ENCRYPTBYKEY(
    , SocialSecurityNumber)

Now let’s see what the data looks like. In the following query, we’ll also include the syntax to decrypt the encrypted column.

-- Select the data and decrypt the encrypted column
    , Name
    , SocialSecurityNumber
    , SocialSecurityNumberEncrypted
    , SocialSecurityNumberDecrypted =
        CONVERT(CHAR, DECRYPTBYKEY(SocialSecurityNumberEncrypted))
FROM DB.dbo.tblPersonnel

From the query results you can see both the original social security number, the encrypted social security number, and the decrypted version of the encrypted social security number. If all went well, you’ll find the decrypted and un-encrypted values are the same. If the encrypted value or decrypted value is null, we missed something along the way.

Also note that if the symmetric key is not open within the session, the decryption will silently fail (in other words, the decryption will return a NULL value, but no error message).

-- Close the symmetric key; it will close automatically
--   when the session ends, but cleaning up after yourself
--   is a good habit to have!


Post a comment or leave a trackback: Trackback URL.


  • atul sharma  On 07 Mar 2014 at 0431

    hello. In DecryptByKey with authenticator, even when the Userid is used as an authenticator, the CEO salary is copied in DBA data instead of pasting Null. Pls suggest. Thanks in advance

    • lextonr  On 07 Mar 2014 at 0829

      Great question! You are correct, the CEO’s encrypted salary is copied to the DBA salary. However, that salary cannot be decrypted with the DBA’s key. Because the decryption fails, we know the salary has been tampered with. screenshot. If that is not sufficient, you could also add a trigger to the table to validate encrypted salaries and not allow them to be saved if they cannot be decrypted. For example:

      ON tbl
      UPDATE tbl
      SET Salary = NULL

      Does that solution work for your use case?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: