Jasmin Azemovic

SQL Server Confidential – Part II – SQL Server Cryptographic Features

January 29, 2015 by

Transparent Data Encryption (TDE)

SQL Server has two ways of encrypting data. One way is by protecting data on the table, record or column level, and the other way is by protecting data “at the rest”. One of the best crypto features in the database world today is known as a Transparent Data Encryption.

Imagine the following scenario. Someone has an unauthorized access to your database system environment. That person finds a way to get the last database backup file, copies it and takes it in an unsecured environment. In this moment, the security mechanism just fell apart.

This scenario illustrates what can happen when someone illegally copies, detaches, and restores your database. The consequences for such activity can be substantial, depending on the sensitivity of your data environment.

TDE performs real-time I/O encryption and decryption of the database files (data and log). The encryption uses a Database Encryption Key (DEK) which is stored in the database boot record for availability during recovery. Backup of databases that have implemented TDE are also encrypted by using the DEK.

DPAPI encrypts the SMK (created at the time of a SQL Server setup). SMK encrypts the DMK of the master database. DMK of the master database creates a certificate in the master database. The certificate encrypts the DMK in the user database. The entire user database is secured by DEK of the user database by using TDE.

To implement TDE, we need to follow these steps:

  • Create a master key
  • Create or obtain a certificate protected by using the master key
  • Create a database encryption key and protect it by using the certificate
  • Set the database to use encryption

Key point of TDE is that an entire database is encrypted on the fly. Data in an encrypted database are encrypted before they are written to a disk and decrypted when read into memory. In this case you should pay attention on the performance issue on heavily transaction load system.

Encrypted backup

In some case scenarios you only want to encrypt backup files to protect data “at the rest”. SQL Server 2014 have one new crypto feature and that is encrypted backup. In a nutshell almost same implementation like TDE, but major difference that database is not on the crypto “pressure” during regular transaction processes. The idea is that you have a business need to keep your backup secured. In this case there is no performance issues because the only encryption/decryption is during the backup/restore operation.

Or you can do it through SSMS GUI:

Before you can access the Encrypted backup option on this dialog window, you need to check (under the Medio Options tab) this option:

Symmetric encryption

Symmetric encryption is the type of encryption that uses the same key for encryption and decryption. SQL Server allows you to choose from several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

When a symmetric key is created, it must be encrypted by using at least one of the following:

  • Certificate
  • Password
  • Symmetric key
  • Asymmetric key

Example of creating a symmetric key encrypted by certificate:

Implementing Symmetric Encryption

In this exercise, you will pass all necessary steps for implementing symmetric encryption in a user-created sample database. Also, you will encrypt data in a user table.

Asymetric encryption

Asymmetric key pair is made up of a private key and a public key. Each key can decrypt data encrypted by the other key pair. Asymmetric encryption/decryption are relatively resource-intensive, but they provide a higher level of security than the symmetric encryption.

Asymmetric Algorithms
KeywordAlgorithmKey Length (Bits)

A asymmetric key is a database object protected at the database level. When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly.

By default, the private key is protected by the DMK. If no DMK has been created, a password is required to protect the private key. If a DMK does exist, the password is optional.

The private key can be 512, 1024, or 2048 bits long.

Symmetric vs. Asymmetric

Is one type of encryption key recommended over the other? Yes, but as always, performance is an issue. Symmetric key algorithms are mathematically simpler, and as a result, faster. The difference in speed can be significant even into the 100x faster range. Therefore, symmetric key algorithms are the way to go when encrypting data.

In moust cases asymmetric encryption in SQL Server is used to defend a symmetric key


Sometimes business requirements will demand to hide some data without using encryption/decryption. There are plenty of mechanisms to make some data/information unreadable. Hashing is one of those mechanisms. Hash functions are powerful, fast and efficient ways to hide data and to check data integrity.

A cryptographic hash function is a function that implements an algorithm that takes some data and returns a fixed-size bit string.

The hash function has the following main properties:

  • Easy to compute the hash value for any given message
  • Impossible to generate a message that has a given hash
  • Impossible to modify a message without changing the hash
  • Impossible to find two different messages with the same hash.


In next example we will just add an extra space after name:

As you see, hash output is totally different

Hashing and encryption are not the same function and each have a totally different usage in practice. The basic point to remember is that hashing is used when there is no need to make a reversible operation, whereas encryption is used when you need to decrypt data at some later point in time.


As you have seen so far, protecting data is the most important thing in database environments. When all security elements fail (i.e. installation errors, authentication, authorization, bad access policy, etc.), there is no more protection. This two articles taught you how to implement advanced techniques for protecting data such as cryptography.

Jasmin Azemovic
Security, Transparent Data Encryption (TDE)

About Jasmin Azemovic

Jasmin Azemović is a researcher at the Faculty of Information Technology in Mostar. He is active in the area of databases and information security. He has an academic degree Ph.D. in the field of modeling, design and development an environment for the preservation of privacy beyond traditional SQL limitations. Jasmin regularly publishes research papers in indexed databases as include are: IEEE, Inspec, EI Compendex and ACM. He is also a very active member in the professional IT world: Microsoft MVP (SQL Server), MCT, and author of several books in English: Writing T-SQL Queries for Beginners Using Microsoft SQL Server 2012 (MVP Press 2012), Securing Microsoft SQL Server 2012, MVP Press. View all posts by Jasmin Azemovic