Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

SQL Server: How to Perform Data Encryption

This article, through contextualization and simple examples, discusses best practices regarding encryption for sensitive data protection inside and outside the organization.

Every passing day companies have considered the information as one of its most important assets. Confidential formulas, the financial situation of the company, a new business strategy or even the list of employees are good examples of sensitive information that, if compromised and are made public, can generate a strong negative impact on the company.

With this in mind, some companies try to improve their IT processes in order to protect your data, especially the most critical of possible unauthorized access, both internal and external. Precautions to protect the data are the most diverse and can range from physical access to the data center to the implementation of firewalls in the network, security tokens, separation of primary accounts to admin accounts, among other possibilities.

Another factor to consider is the rapid growth in Internet use to facilitate all types of services provided by various companies. The Internet today is a major point of concern when working with data security.

Thus, many improvements in the information security area are internal to each company, but there are also regulations that must be implemented on request by an external organ. In these cases the level of security and the method may vary if the company is in any particular segment (eg the pharmaceutical industry, which is regulated by a professional organ) or if you are looking for a specific regulation that sets the rules for security data. Some examples of these regulations are: SOX, PII, PCI.

One way to protect the data that companies have been using with increasing frequency is data encryption. Basically, the encryption is to convert a plain text into something unreadable. Only authorized individuals will have the resources and the necessary keys to achieve reverse the process and restore the original text.

In this article we will present some ways to protect and ensure the integrity of critical data using encryption. We will see different methods that can be applied to SQL Server to encrypt the data and also the ways to protect the encryption keys that are central parts of the process.

Initial concepts about data encryption

First, we must understand that encryption should not be implemented for all databases. The database administrator must, along with his business partner, do the analysis of what kind of data will be saved in the bank and what encryption is required to implement. Depending on the type of encryption to be implemented, changes will be needed in the application. The fact that the encryption once implemented, there may be performance issues, since now been added a new factor in reading and writing of data in the database must also be considered.

Another factor to consider is the network where the data will be sent out. Often the encryption is implemented in the database, but these data will be transmitted over a network without encryption. For this reason, it is necessary that the data is also protected while being transferred over the network. This can be a serious problem especially if the information is travelling through a public network.

A final factor to be considered in encryption of choice is the version and edition of SQL Server. We will see later that encryption types can be applied depending on the version and edition of SQL Server.

In the context of encryption, SQL Server uses a hierarchical encryption model. In this, a level protects the next level from a combination of keys and certificates.

The illustration in Figure 1 outlines the different levels of the hierarchical encryption and how each level is protected by the earlier.

Hierarchical encryption.

Figure 1. Hierarchical encryption.

Service Master Key (SMK)

The first level of protection in SQL Server that we have seen in the figure is the Service Master Key (SMK). The SMK is automatically generated the first time that SQL Server starts and is encrypted by DPAPI. The key that DPAPI uses to protect the SMK is derived from a combination of the account used to start the SQL Server and the name of the computer on the network service.

Beginning with SQL Server 2012, the SMK is encrypted using the AES algorithm with a 256-bit key, unlike previous versions that used the 3DES algorithm with a 128-bit key.

Database Master Key (DMK)

The second level of protection in SQL Server is the Database Master Key (DMK). The DMK is used to protect the certificates and keys used to encrypt databases. It is unique for each database within the instance.

Like the SMK, since SQL Server 2012 DMK is encrypted using the AES algorithm with a 256-bit key. To improve the protection of the DMK, it can also be protected by a user-defined password.

Encryption Keys (Keys)

Encryption keys are used to both encrypt and to decrypt the data. After being made the analysis and the decision was taken that the database needs to be encrypted, you must first choose the data encryption method. The options are:

  • Certificate: Issued and signed by a certifying entity;
  • Password: Defined by the use that is implementing the encryption;
  • Symmetric Key: When you use the same key to encrypt and decrypt;
  • Asymmetric Key: When different keys are used to encrypt and to decrypt;
  • Provider: When the key is in an external device to SQL Server (we will see this option in detail in the topic Extensible Key Management).

Once defined as the data is encrypted, the next step is to analyze what level of protection is necessary to apply to the database, and with that, which encryption algorithm is used. One factor to consider is the CPU resource consumption. The use of a stronger encryption algorithm with a 256-bit key, for example, possibly consume more CPU resources, but data will be safer.

Extensible Key Management (EKM)

An additional feature that SQL Server offers to encrypt the data is the Extensible Key Management (EKM). When the EKM is implemented, the encryption keys are exported to an external source to SQL Server, which can be a software or hardware before being stored in the database.

Once installed the external encryption key, SQL Server uses the Microsoft Cryptographic API (MSCAPI), which provides a secure interface between the encryption modules within the operating system, to accept this new encryption key.

The great advantage in using the EKM is that besides the native SQL Server encryption, it also uses additional software or hardware external to SQL Server in data protection.

Currently, many companies already provide software and hardware options for implementing the EKM. The key management is done by the Hardware Security Modules (HSM). HSM are devices that store the encryption keys in hardware modules or software. These devices act as an intermediary between an application and the encryption key. A common example of these devices is the token, provided by some databases to access the current account.

It is worth noting, however, that the implementation of EKM is not available for all versions and editions of SQL Server. Beginning with SQL Server 2008 and only for the Enterprise editions is that this option can be used.

By default the EKM is disabled, being necessary to change the settings of the SQL Server instance - through the stored procedure sp_configure - for it to be enabled. This procedure is shown in Listing 1.

Listing 1. Script to enable EKM.

  --Enabling the EKM
  sp_configure 'show advanced', 1
  sp_configure 'EKM provider enabled', 1

One important information on the use of EKM is that Microsoft may request the encryption key or requesting access to your key management tool if you need to perform troubleshooting of any problems. In this case, after the resolution of the problem, you must renew the encryption keys to maintain the security of the keys.

Encryption Options

Basically, within SQL Server, data can be encrypted in two ways (we will see each option in detail below):

  • Applying encryption on specific columns that contain restricted and confidential data;
  • Encrypting the entire database with Transparent Data Encryption (TDE).

Both options provide the same concepts presented so far, as SMK, DMK, certificates and keys, but as you will see, each has a applicability and a different impact on the database.

Along with the implementation of encryption in the database, we will also see how to implement data encryption at the time they are being transferred over the network using the Security Socket Layer (SSL) protocol.

Encrypting Column

When we have a table that will hold restricted and confidential data, it is often necessary that the column that contains the data is fully protected from any unauthorized access.

Once the individual has a user to access the database, it can use various tools such as SQL Server Management Studio, Excel, ODBC, among others, to run a SELECT on this table. For the encrypted column be read correctly, you must first open the encryption key. If the key is not open, the data is all encrypted. This is the protection implemented by this encryption option.

Even though some companies have a very strong security policy, implementing an effective access control and privileges, there will always be individuals with access to the database. You can be the application to support team or the team of database administrators. Encryption column aims to protect data even these groups.

A classic example where it is necessary to encrypt the data in a column are the tables that contain salary information in HR systems. Other examples are the tables that have personal data such as credit card number, social security number, passwords. In all these cases, data encryption directly in the column is indicated.

One of the factors to be analyzed before implementation of this encryption option is the impact on the application that uses the database. As others have said, from the moment the encryption is implemented, you must open the encryption key every time the data of the encrypted column is being read or updated. The best way to implement this encryption is that the application is already developed based on the use of encryption to minimize future impacts.

To better understand how cryptography works implemented directly in the column of a table, consider the following scenario presented.

A large company that supports multiple SQL Server instances, manage passwords SA account of SQL Server in an Excel spreadsheet. However, the IT manager understood that this control is not safe enough and, because of the high risk that this information is if it becomes public, was required to pass these passwords to be stored in a database in encrypted manner.

For this, a database was created for the DBA team that has a table to store the passwords in encrypted form. In Listing 2 you can see the script used to create this table.

Listing 2. Script for table creation and insertion Encrypting data.

  --Creating Password table
  USE Password_Control
  CREATE TABLE Passwords (
  SQLInstance VARCHAR(50),
  PWD         VARCHAR(15))
  --Inserting passwords

So far, the passwords were entered in an unencrypted manner. The first step in the implementation of encryption is to create the Database Master Key. The password used to create the DMK should follow your domain password policies. To create the DMK, use the following script:

--Creating the DMK
USE Password_Control

PASSWORD = 'TemporaryPasswordforDMK@123'

Then we will create a certificate which will protect the encryption key. To do this, run:

--Creating the certificate
USE Password_Control

WITH SUBJECT = 'Password'

Once you have created the certificate, we will set a symmetric key to encrypt the data. In this scenario, we will use the AES_256 algorithm, since the data you want to protect (passwords of accounts SA) are considered highly restricted. Thus, run the script:

--Creating symetric key
USE Password_Control




To check all symmetric keys created, just perform a simple query in sys.symmetric_keys system table, as follows:

--Checking created symetric keys
USE Password_Control

SELECT * FROM sys.symmetric_keys

At this point you may notice that this table also contains the key information used for the DMK (##MS_DatabaseMasterKey##). We can note that the DMK was created using the TRIPLE_DES algorithm because it was used SQL Server 2008 for this activity. Refer to Figure 2.

Checking symetric keys.

Figure 2. Checking symetric keys.

After the creation of the encryption key, the database is ready to encrypt the data. Thus, the next step is to encrypt the data of PWD column of the table Passwords.

In order to view the encrypted data, add a new column in the table. So you can compare the decrypted data with encrypted. See the script in Listing 3.

Note that we are creating the new column as VARBINARY(256) because of our chosen algorithm AES_256. If an algorithm that uses a 128-bit key had chosen, this column should be VARBINARY(128).

Listing 3. Script for creating encrypted column.

  USE Password_Control
  ALTER TABLE Passwords
  ADD PWD_Encrypt VARBINARY(256);

From now on, for any query that uses the new column, you must open the encryption key.

Next, we'll update the PWD_Encrypt column with data from the PWD column encrypted way (see Listing 4). We note that in the final script we are closing the encryption key. It is highly recommended that after any execution (SELECT, INSERT, UPDATE, DELETE) using an encrypted column, the encryption key is closed to prevent possible unauthorized access to data.

Listing 4. Script to insert encrypted data.

 USE Password_Control
  --Opening the symetric key
  --Updating the new column
  UPDATE Passwords
  SET PWD_Encrypt = EncryptByKey(Key_GUID('Passwords_SYM_Key'), PWD)
  --Closing the symetric key

It is extremely important that soon after the implementation of encryption columns is also made up of the SMK and DMK.

It is not uncommon for DBAs receive requests to restore a database or that this database is moved to a test environment. In such cases, if the database has encryption columns, the keys must also be restored. If there is any fault on the restore process of the keys, you can not read the encrypted data.

To back up the SMK and DMK, you can run the script in Listing 5.

  --Backup of SMK
  --Backup of DMK
  USE Password_Control
  Transparent Data Encryption (TDE)

Nowadays it is very common for devices containing the backups (disks, tapes, DVDs, etc.) do not be in the companies, but they are sent and stored on third parties, for the sake of security and strategy for business continuity . Depending on the shipping of these backups strategy, the device containing the backup can be lost.

For this reason, and also because it does not require any change in the application that uses the database, companies are increasingly using the TDE.

The main difference between the TDE encryption and columns is that the encryption of columns is necessary to open the encryption key each time you run any command against the encrypted column. In TDE this is not necessary.

When the TDE is enabled, the entire database is encrypted and, therefore, if a malicious individual to a copy of the database (.MDF and .ldf) or even backup of this, he can not read an information without restoring also the encryption keys used for the implementation of TDE, which in most cases will not be available along with the database.

This encryption option is available from the 2008 version of SQL Server, and only for the Enterprise editions.

Another difference is that for the TDE can be enabled at both SMK as the certificate should be created in the master database and not in the user database.

An important and complementary information is that encrypted data can not be compressed in the same proportion as decrypted data. Thus, it is not recommended to use backup compression when TDE is enabled.

To check how the TDE works, we will use the same database created for encryption columns (Password_Control). Note the script in Listing 6.

Listing 6. Script to enable TDE.

  -- Create the SMK in master
  USE master
  --Criate the Certificate in master
  --Enables the encryption to the database
  USE Password_Control
  --Configure the database to use TDE

From that moment the TDE is enabled for the database. A simple way to confirm whether or not you are using the TDE is to check the is_encrypted column sys.databases table.

Once implemented the TDE, you can not restore this database without restoring the certificate used to protect the encryption key.

Similarly, if you have to move a database to another SQL Server instance, you must first restore the certificate, and only then you can attach the bank. For this reason it is very important to back up the certificate and key associated with it as soon as they are created.

Listing 7 shows how to perform the backup and the restore of a certificate used in the implementation of TDE.

Listing 7. Backup of the certificate used in the SPT and restore of the certificate.

  --Backup of the certificate
  USE master
  BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TEMP\TDECertificate_Cert'
        FILE = 'D:\TEMP\TDECertificate_Key',
              ENCRYPTION BY PASSWORD = 'TDEPWD@123') 

Since the back up is done, the database can be restored again in the same or another SQL Server instance. Before restoring the database, however, the SMK in the master database must have already been created. It will be used to protect the certificate that will restore.

After the SMK has been created, the certificate must be restored using the script:


FROM FILE = 'D:\TEMP\TDECertificate_Cert'


FILE = 'D:\TEMP\TDECertificate_Key',


After the certificate is restored, the database can also be restored using SQL commands such as RESTORE DATABASE or, if the database has been transferred to another instance through the process of Dettach/Attach, the stored procedure sp_attach_db.

Security Socket Layer (SSL)

After the implementation of encryption, directly in columns or through the TDE, shall be reviewed where the data will be sent out. Depending on the type of data and how it is restricted and confidential, the communication between the client and the database must also be encrypted.

The method used by SQL Server to make data safe during the transfer of those the network is using the Security Socket Layer (SSL) protocol. Once implemented SSL, all communication between the client and the database is encrypted and thus, if any tool monitoring data traffic on the network, it can not understand these data.

The implementation of SSL on a SQL Server instance can be more complex than the encryption directly in columns or TDE, since you need a certificate issued by a certifying entity, as in the case of VeriSign. In addition, if the department uses a domain account to run the SQL Server services, this account must have access to the certificate.


In this article the different data encryption methods we have available in SQL Server have been addressed. They protect the data of possible internal and external attacks.

However, it is noteworthy that the encryption is not the silver bullet for data protection. Along with encryption, must also be set a security and audit policy. So even if the data is protected, it is strongly recommended to give access only to users who really need it. It is also strongly recommended monitor who is trying to access sensitive data, creating an audit process in objects that have encrypted data. From the 2008 release, SQL Server itself provides auditing options.

Once implemented encryption, should also be created a strategy to save the key information, passwords and certificates. Without this information, you can not restore these databases in case of failures.

Finally, it is important to note that encryption should be aligned with the business needs. It's not every database that must have your encrypted data and is not any kind of encryption to be used. In addition, it is the role of the database administrator understand the need for encryption, determine the most appropriate method and implement encryption.

Web developer and passioned for web design, SEO and front end technologies.

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login