4 Encryption options for your SQL Server

In today’s tutorial, we will learn about cryptographic elements like T-SQL functions, service master key, and more.

SQL Server cryptographic elements

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed.

For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

SQL Server provides the following building blocks for the encryption; based on them you can implement all supported features, such as backup encryption, Transparent Data Encryption, column encryption and so on.

We already know what the symmetric and asymmetric keys are. The basic concept is the same in SQL Server implementation. Later in the chapter you will practice how to create and implement all elements from the Figure 9-3. Let me explain the rest of the items.

T-SQL functions

SQL Server has built in support for handling encryption elements and features in the forms of T-SQL functions. You don’t need any third-party software to do that, as you do with other database platforms.

Certificates

A public key certificate is a digitally-signed statement that connects the data of a public key to the identity of the person, device, or service that holds the private key. Certificates are issued and signed by a certification authority (CA). You can work with self-signed certificates, but you should be careful here. This can be misused for the large set of network attacks.

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

In a nutshell, the previous image means that any key in a hierarchy is guarded (encrypted) with the key above it. In practice, if you miss just one element from the chain, decryption will be impossible. This is an important security feature, because it is really hard for an attacker to compromise all levels of security. Let me explain the most important elements in the 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 during installation and the first time the SQL Server instance is started. It is used to encrypt the next first key in the chain.

The SMK should be backed up and stored in a secure, off-site location. This is an important step, because this is the first key in the hierarchy. Any damage at this level can prevent access to all encrypted data in the layers below. When the SMK is restored, the SQL Server decrypts all the keys and data that have been encrypted with the current SMK, and then encrypts them with the SMK from the backup.

Service Master Key can be viewed with the following system catalog view:

1> SELECT name, create_date

2> FROM sys.symmetric_keys

3> GO

name create_date

------------------------- -----------------------

##MS_ServiceMasterKey## 2017-04-17 17:56:20.793

(1 row(s) affected)

Here is an example of how you can back up your SMK to the /var/opt/mssql/backup folder.

Note: In the case that you don’t have /var/opt/mssql/backup folder execute all 5 bash lines. In the case you don’t have permissions to /var/opt/mssql/backup folder execute all lines without first one.

# sudo mkdir /var/opt/mssql/backup
# sudo chown mssql /var/opt/mssql/backup/
# sudo chgrp mssql /var/opt/mssql/backup/
# sudo /opt/mssql/bin/mssql-conf set
filelocation.defaultbackupdir /var/opt/mssql/backup/
# sudo systemctl restart mssql-server

1> USE master
2> GO
Changed database context to 'master'.
1> BACKUP SERVICE MASTER KEY TO FILE = '/var/opt/mssql/backup/smk'
2> ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd'
3> --In the real scenarios your password should be more complicated
4> GO
exit

The next example is how to restore SMK from the backup location:

1> USE master
2> GO
Changed database context to 'master'.
1> RESTORE SERVICE MASTER KEY
2> FROM FILE = '/var/opt/mssql/backup/smk'
3> DECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd'
4> GO

You can examine the contents of your SMK with the ls command or some internal Linux file views, such is in Midnight Commander (MC). Basically there is not much to see, but that is the power of encryption.

The SMK is the foundation of the SQL Server encryption hierarchy. You should keep a copy at an offsite location.

Database master key

The 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 user-supplied 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). The copy stored in the master is always updated whenever the master key is changed. The next T-SQL code show how to create DMK in the Sandbox database:

1> CREATE DATABASE Sandbox

2> GO

1> USE Sandbox

2> GO

3> CREATE MASTER KEY

4> ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rd'

5> GO

Let’s check where the DMK is with the sys.sysmmetric_keys system catalog view:

1> SELECT name, algorithm_desc

2> FROM sys.symmetric_keys

3> GO

name algorithm_desc

-------------------------- ---------------

##MS_DatabaseMasterKey## AES_256

(1 row(s) affected)

This default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of ALTER MASTER KEY. A master key that is not encrypted by the SMK must be opened by using the OPEN MASTER KEY statement and a password.

Now that we know why the DMK is important and how to create one, we will continue with the following DMK operations:

• ALTER

• OPEN

• CLOSE

• BACKUP

• RESTORE

• DROP

These operations are important because all other encryption keys, on database-level, are dependent on the DMK.

We can easily create a new DMK for Sandbox and re-encrypt the keys below it in the encryption hierarchy, assuming that we have the DMK created in the previous steps:

1> ALTER MASTER KEY REGENERATE

2> WITH ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforN3wK3y'

3> GO

Opening the DMK for use:

1> OPEN MASTER KEY

2> DECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforN3wK3y'

3> GO

Note: If the DMK was encrypted with the SMK, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

Closing the DMK after use:

1> CLOSE MASTER KEY

2> GO

Backing up the DMK:

1> USE Sandbox

2> GO

1> OPEN MASTER KEY

2> DECRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforN3wK3y';

3> BACKUP MASTER KEY TO FILE = '/var/opt/mssql/backup/Snadbox-dmk'

4> ENCRYPTION BY PASSWORD = 'fk58smk@sw0h%as2'

5> GO

Restoring the DMK:

1> USE Sandbox

2> GO

1> RESTORE MASTER KEY

2> FROM FILE = '/var/opt/mssql/backup/Snadbox-dmk'

3> DECRYPTION BY PASSWORD = 'fk58smk@sw0h%as2'

4> ENCRYPTION BY PASSWORD = 'S0m3C00lp4sw00rdforN3wK3y';

5> GO

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.

Dropping the DMK:

1> USE Sandbox

2> GO

1> DROP MASTER KEY

2> GO

You read an excerpt  from the book SQL Server on Linux, written by Jasmin Azemović.  From this book, you will learn to configure and administer database solutions on Linux.

Read Next 

How SQL Server handles data under the hood

SQL Server basics

Creating reports using SQL Server 2016 Reporting Services