Intro

We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspects of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs.

A database is the spine of every information system and is the specific target of potential data thieves. SQL Server has one of the best cryptographic set of features that we can use to create a state of the art security and privacy “aware” systems.

Cryptology is an art and science based in mathematics theory with the purpose of creating secret codes.  It has two major components:

• Cryptography
• Cryptanalysis

  Figure 1. Cryptology scheme

Cryptography is about creating a secret codes, while cryptanalysis is the process of breaking secret codes.

Cryptography has two major parts:

• Encryption – process of creating an obfuscated message from a plain text using a key.
• Decryption – process of returning plain text from an obfuscated message using a key.

Cryptanalysis is based on two components:

• Backdoor – a cryptosystem has a backdoor for accessing plain text from encrypted messages without the regular process of decryption.
• Brute force – the only way to decrypt a message, aside from the regular decryption process, is by testing all possible combinations.

This article will explain cryptography from the SQL Server perspective.

Types of Cryptography

There is a two types of cryptography:

• Symmetric cryptography
• Asymmetric cryptography

Symmetric Cryptography

In symmetric cryptography cases, the sender and recipient share a key that is used to perform encryption and decryption. Symmetric cryptography is the most popular way for encryption in modern IT.

Figure 2. Symmetric cryptography

Some of the most common symmetric algorithms are:  Rijndael (AES) and Triple DES (3DES).

Symmetric cryptography is simple because the same key that is used for encryption and decryption. But, before communication can occur, the sender and the recipient must exchange a secret key. The exchange of the shared secret key is the only weakness of symmetric cryptography.

Asymmetric Cryptography

With asymmetric cryptography (also known as public key cryptography), the sender encrypts data with one key, and the recipient uses another key for decryption. The encryption and decryption key are known to us as a public/private key pair.

Figure 3. Asymmetric cryptography

The most commonly used asymmetric algorithm is the RSA algorithm.

Asymmetric encryption requires more processing power than symmetric encryption. Because of this, asymmetric encryption is usually optimized by adding a symmetric key to encrypt a message and then asymmetrically encrypting the shared key. This can reduce the amount of data that is asymmetrically encrypted and also improves performance.

What is a Key?

A cipher or cryptosystem is used to encrypt data. A key is used to configure a cryptosystem for encryption and decryption. A fundamental principle of cryptography is that the inner workings of a cryptosystem are completely known to everyone. However, the key is the only secret. This principle is known as the Kerckhoffs’ Principle.

A key is the product of a specific cryptosystem and is based on randomly collected information, such as random numbers, the temperature of the CPU, sample data in RAM, etc. Both symmetric and asymmetric keys are measured in bits. Despite this similarity, symmetric and asymmetric keys are different. For example, a symmetric key using AES can be 256 bits long, while an asymmetric key using RSA can be as long as 2048 bits. Although 2048 bits may appear more secure than 256 bits, it does not mean that RSA is more secure than AES. Both RSA and AES are different and not comparable. For example, the security available with a 1024-bit key using asymmetric RSA is considered approximately equal in security to an 80-bit key using a symmetric algorithm.

SQL Server Cryptographic Features

SQL Server provides the following mechanisms for encryption:

• Transact-SQL functions
• Asymmetric keys
• Symmetric keys
• Certificates
• Transparent Data Encryption
• Backup encryption

Encryption Hierarchy

SQL Server encrypts data with a hierarchical encryption. Each layer encrypts the layer below it using certificates, asymmetric keys, and symmetric keys.

Figure 4. Encryption hierarchy

Service Master Key

SQL Server has two primary applications for keys: a Service Master Key (SMK) generated on and for a SQL Server instance, and a Database Master Key (DMK) used for a database.

The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the DMK. The SMK is encrypted by using the local computer key which uses the Windows Data Protection API (DPAPI).

The SMK should be backed up and stored in a secure, off-site location.

Restore process can be done as following:

When you are playing with SMK (it is not recommended if it is not REALY necessary) keep in mind that the SMK is the foundation of the SQL Server encryption hierarchy. The SMK directly or indirectly protects all other keys and data in the encryption hierarchy tree. If a dependent key cannot be decrypted during a forced regeneration, the data that the key secures will be lost.

Database Master Key

The Database Master Key (DMK) is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a password. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the SMK and stored in both the database user and in the master database.

We can create a database master with the CREATE MASTER KEY statement: (This example use sample AdventureWorks2012 database)

SQL Server can generate, for each user database, a different DMK. This is a good feature because it makes the database more secure. If you write quick SELECT on system catalog view: sys.symmetric_keys

You will get something like this:

Figure 5. Result of the SELECT * FROM sys.symmetric_keys statement on AdventureWorks2012

You should backup the master key by using BACKUP MASTER KEY and store the backup in a secure and off-site location.

Following operations can be done with DMK, after you create one.

• ALTER
• OPEN
• CLOSE
• BACKUP
• RESTORE
• DROP

We can easily create a new DMK for AdventureWorks2012 and re-encrypt the keys below it in the encryption hierarchy. Opening the DMK for use: Closing the DMK after use: The master key must be open before it is backed up. Backing up the DMK: When the master key is restored, SQL Server decrypts all the keys that are encrypted with the currently active master key, and then encrypts these keys with the restored master key. Restoring the DMK: Dropping the DMK:

Next time it will be even more confidential. We will cover in details: TDE, Backup encryption and practice examples of data encryption using symmetric and asymmetric keys.