0 Replies Latest reply on Jun 4, 2015 5:46 PM by dtawater

    SQL Database Column Level Encryption Workbench (Part 1)


      Are you needing to encrypt a column of data in a database, yet, your environment does not allow encryption of the entire database?


      This post brought to you by the inspiration from this TechNet article ms179331.  I was tasked with a challenge six months ago to encrypt specific fields in a database where the entire database itself could not be encrypted due to the design the application owners required.  I proceeded to spend the next four months learning SQL encryption techniques and how to integrate the final solution into an already existing database without having any data loss.  While the entire integration code is much bigger than this article, I do want to share the proof of concept I made to explain the possibility of encryption to the application owners when I was showcasing how the integration would be performed.  This is a cut down version of that proof of concept that performs a basic key generation and encrypts and decrypts a column of data in a table.  Let’s dive into the important initial concepts of securing sensitive data.



      This concept describes the occurrence of any inactive data which is stored physically in digital form. These include mediums such as databases, backups, tapes, file systems etc.  The best method of protecting this type of data from unauthorized access is data encryption.  The objective is that encryption should remain intact even when usernames and passwords have been compromised.  Encryption keys are components of cryptography that protect data with an intrinsic intent of decrypting the data only under authorized circumstances.  To ensure this added level of encryption remains viable it is good practice to update encryption keys at regular intervals. While events such as the disk being removed from its originating environment would still be encrypted data, this is only successful while the medium is offline.  When the data is in an operational state the data is decrypted for use by the environment and the encryption does not remain in place for this data while it’s in motion.


      This concept describes the occurrence of any active data that is moving from one platform to another platform or one location within a single platform to another location within that same platform.  This would include technologies such as SSL/TLS encryption that is intended to prevent unauthorized individuals from monitoring the network traffic and recording plaintext data.  The key difference between data-at-rest and data-in-motion is while the former covers storage based security the latter covers real time data transport that happens when an event is processed by a platforms application.  An example would be a transaction to purchase coffee from a local coffee shop that involves swiping a credit card through the business’s point of sale would then transmit that credit card number to the payment processor where the response back to the merchant would be either an approved or denied message.  The encryption of the data while moving between the merchant and the payment processor would be in encrypted in the form of transport protocols such as TLS which would encrypt every packet of data that would then only be decrypted by the payment processor once the data reaches the destination server.

      This article will focus on Data-At-Rest software encryption options and how to implement one example of encrypting SQL data.  The operating system, the SQL server and the application itself are the three different software performers that perform the actual work of encrypting and decrypting data-at-rest. Hardware solutions that also perform encryption such as encrypted vaults, secure flash drives, and hardware security modules are beyond the scope of this article.  Let’s review a few software options to encrypt SQL server data.

      Volume Level Encryption

      BitLocker Drive Encryption, TrueCrypt, BestCrypt are all physical data protection solutions that are designed to protect data by encrypting the drives disk sectors.  This solution can work in conjunction with Microsoft EFS described later to provide protection once the operating system is running, yet, stand alone is designed to be effective when the system is offline.  There are numerous articles speaking for and against this type of encryption which would take many pages and many comments to thoroughly exhaust all points of the discussion.  Suffice it to say this entire type of encryption is still being debated.  For that reason, I decided not to take this route.

      Operating System Level Encryption

      The Encrypted File System (EFS) provides security for files and directories at the NTFS file system level.  The NTFS volume is protected by a public-key system cryptography solution. Typically, the windows ACL security is used to prevent unauthorized access to this sensitive information. This is overkill in terms of database applications and impacts performance in a myriad of ways.  I am happy to discuss if EFS has helped you in your database encryption solutions.

      Application Level Encryption

      In .NET, you can use cryptography to protect the privacy and integrity of your data stored or transmitted by your application. The .NET Framework has several classes for different types of cryptography.  These include:

      • Symmetric encryption
      • Asymmetric encryption
      • Hashing
      • Digital signatures

      With symmetric encryption, the cipher, is reversible and only one key is used to encrypt and decrypt the data.  With asymmetric algorithms one key encrypts the data and another decrypts. An md5 hash is not mathematically reversible used to compare different hashed results to verify if a submitted hash matches the hash in storage. Digital signatures themselves are not performers of encryption, yet, they provide verification of who officially performed an encryption.  Digital signatures are created by private keys and need to be guarded the same as backups of certificate passwords and SQL Service master keys.

      Physical Database Encryption

      Transparent Data Encryption (TDE) is designed to protect data at rest by encrypting database files at the page level rather than the individual data items themselves. TDE does not increase the size of an encrypted database.  This level of protection prevents the data and backup files from compromise by raw file inspection.  The transparency occurs when an authorized user attempts to access the data and the system decrypts the contents of the data with no explicit action on the user’s part to actually perform the decryption.  One key to this feature is that Enterprise Edition of SQL Server is required starting from SQL Server 2008.  Backups of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, these backups are restored, the certificate protecting the database encryption key must be available. Ensure that backups of the server certificates are readily available otherwise data loss will occur.

      Column-Level Encryption

      When attempting to compare Column-Level Encryption (CLE) with TDE, these features are not similar enough to draw corollaries.  First CLE is available on all editions of SQL server starting with 2005.  The first challenge to overcome is preparing your column to use a varbinary data type when being stored for encryption and converted back to its original data type when ready for use.  CLE data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate. The following permissions are necessary to perform column-level encryption.

      • 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.



      In part two we will go over each section of the attached Workbench.  (I'll upload and post tomorrow)