SQL Server Encryption

Today, I am very happy because my first article (SQL Server Encryption) was published in SQL Server Pro. In this article, after providing an overview of the SQL Server encryption model, I described the encryption options available in SQL Server and how you can use them to encrypt sensitive information stored inside your SQL Server databases.

Advertisements

Transparent Data Encryption (TDE) with Database Mirroring

Database security is essential for the organisations working in healthcare and financial sector because the databases in such organisations contain sensitive and confidential information about their customers. The organisation working in such sectors also falls under the boundaries of data regulatory and compliance requirements (such as HIPAA, PCI-DSS, SSAE16 etc.) which require the encryption of sensitive data inside databases and at operating system level. SQL Server offers a variety of options for encryption which includes Transparent Data Encryption (TDE). This feature is available in since the release of SQL Server 2008 and provides us the real-time encryption of data and log files. The Transparent Dat Encryption (TDE) also encrypts backups, snapshots and transaction logs of the database.

Checkout my article here in which I discussed the architecture of Transparent Data Encryption (TDE), and the process of implementing Transparent Data Encryption (TDE) on standalone and mirrored database.

This article is published on SSWUG.org.

Using Cell-Level Encryption in SQL Server

Industry guidance such as the Payment Card Industry Data Security Standard (PCI-DSS), Healthcare Insurance Portability and Accountability Act (HIPAA) and numerous state privacy breach notification laws require the use of encryption for sensitive data such as credit card numbers, security related data etc. To meet these compliance and regulatory requirements, SQL Server provides users the choice between transport-level encryption, cell-level encryption, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. I’ve already discussed the database-level and transport-level encryption in my previous blog posts here and here respectively. In this blog post, I’m going to show you the implementation of cell-level encryption.

What is Cell-level encryption?

The Cell-level encryption is a series of built-ins and the key management hierarchy. This option follows a manual process that requires a redesign of the application to call the encryption and decryption functions. In addition, you also must modify the schema to store the data as varbinary and then recast back to appropriate data type when read.

The SQL Server encryption key hierarchy

Microsoft Reference: http://technet.microsoft.com/en-us/library/cc966395.aspx

Comparing the Cell-level encryption options

When encrypting data, you need to choose the most appropriate encryption options. The options available are described as follow:

  • Passphrase – A password that can contain spaces. A passphrase is the least secure option. It requires you to use the same passphrase when encrypting and decrypting the data. If stored procedures and functions aren’t encrypted, the passphrase is accessible through metadata.
  • Asymmetric key – Offers strong protection by using a different key to encrypt and decrypt the data. However, it provides poor performance and shouldn’t be used to encrypt large values. Can be signed by the database master key or created using a password.
  • Symmetric key – Offers good performance and is strong enough for most requirements. Uses the same key to encrypt and decrypt the data.
  • Certificate – Offers strong protection and good performance. Can be associated with a user. A certificate must be signed by the database master key.

Important Built-In functions for Cell-level encryption

Encryption

Decryption

Demo: Cell-level Encryption

For the purpose of this demo, I’m going to use AdventureWork2012 database.

Create Database Master Key (DMK):

USE AdventureWorks2012
GO

-- Creates a database master key encrypted by password $Str0nGPa$$w0rd
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '$tr0nGPa$$w0rd'
GO

Encrypting data using Symmetric Key encrypted by Asymmetric Key – The purpose of creating is the Asymmetric key is to encrypt our Symmetric Key.

Create Asymmetric Key:

USE AdventureWorks2012
GO

-- Creates an asymmetric key encrypted by password '$e1ectPa$$w0rd'
CREATE ASYMMETRIC KEY MyAsymmetricKey
	WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = '$e1ectPa$$w0rd'
GO

Execute the query below, to view the information about asymmetric key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[asymmetric_keys]
GO

Create Symmetric Key:

USE [AdventureWorks2012]
GO

-- Creates an symmetric key encrypted by asymmetric key
CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
GO

Execute the query below, to view the information about symmetric key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[symmetric_keys]
GO

For demonstration purposes, I’m going to create a table called TestEncryption. This table has three columns Name, CreditCardNumber and EncryptedCreditCardNumnber. The EncryptedCreatedCardNumber stores the encrypted credit card number stored in CreditCardNumber column. Also insert some dummy data into this table for demonstration purposes. Execute the following code to create this table and insert dummy data:

USE [AdventureWorks2012]
GO

CREATE TABLE TestEncryption (
	[Name] [varchar](256)
	,[CreditCardNumber] [varchar](16)
	,[EncryptedCreditCardNumber] [varbinary](max)
	)
GO

INSERT INTO TestEncryption (
	[Name]
	,[CreditCardNumber]
	)
SELECT 'Simon Jones'
	,'9876123456782378'

UNION ALL

SELECT 'Kim Brian'
	,'1234567898765432'
GO

SELECT *
FROM TestEncryption
GO

ResultSet:

USE [AdventureWorks2012]
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
WITH PASSWORD  = '$e1ectPa$$w0rd'
GO

Execute the following query returns the list of opened key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[openkeys]
GO

Now execute the following script update the TestEncryption table to insert the values in EncryptedCreditCardNumbers column from CreditCardNumbers column:

USE [AdventureWorks2012]
GO

--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE TestEncryption
SET [EncryptedCreditCardNumber] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CreditCardNumber)
GO

Once successfully executed, Verify the value inside EncryptedCreditCardNumber column by running the following query:

USE [AdventureWorks2012]
GO

SELECT * FROM [TestEncryption]
GO

ResultSet:

Executing the following query to retrieve the data inside EncryptedCreditCardNumber column using DECRYPTBYKEY encryption function:

USE [AdventureWorks2012]
GO

SELECT CONVERT([varchar](16), DECRYPTBYKEY([EncryptedCreditCardNumber]))
 FROM [TestEncryption]
GO

ResultSet:

Encrypting data using symmetric key encrypted using Passphrase – In this script, The data is encrypted using symmetric key using Phaseprase (see below):

USE [AdventureWorks2012]
GO

-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyPwd
	WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'RememberMe!'
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyPwd DECRYPTION BY PASSWORD = 'RememberMe!'
GO

-- Add EncryptedCreditCardNumber2 column in the Test encryption table.
-- This column stores the data encrypted using key encrypted by Passphrase.
ALTER TABLE TestEncryption ADD [EncryptedCreditCardNumber2] [varbinary] (max)
GO

--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber2] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyPwd'), CreditCardNumber)
GO

SELECT *
FROM [TestEncryption]
GO

ResultSet:

Encrypting data using Certificate – You can also encrypt the data using Certificate as follow:

USE [AdventureWorks2012]
GO

-- Creates a certificate
CREATE CERTIFICATE MyCertificate
	WITH SUBJECT = 'Demo Cert'
		,EXPIRY_DATE = '10/31/2050'
GO

-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyCert
	WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyCert DECRYPTION BY CERTIFICATE MyCertificate
GO

-- Add two more columns in the Test encryption table.
-- EncryptedCreditCardNumber3 column stores the data encrypted directly using certificate.
-- EncryptedCreditCardNumber4 column stores the data encrypted key encrypted using certificate.
ALTER TABLE TestEncryption ADD [EncryptedCreditCardNumber3] [varbinary] (max)
	,[EncryptedCreditCardNumber4] [varbinary] (max)
GO

--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber3] = ENCRYPTBYCERT(CERT_ID('MyCertificate'), CreditCardNumber)
GO

SELECT *
FROM sys.certificates

--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber4] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyCert'), CreditCardNumber)
GO

-- Examine the encrypted columns
SELECT [EncryptedCreditCardNumber3]
	,[EncryptedCreditCardNumber4]
FROM [TestEncryption]
GO

-- Reading data by decrypting data of [EncryptedCreditCardNumber3] using DECRYPTBYCERT function
SELECT CONVERT([varchar](16), DECRYPTBYCERT(CERT_ID('MyCertificate'), [EncryptedCreditCardNumber3])) AS [CreditCardNumber]
FROM [TestEncryption]
GO

-- Reading data by decrypting data of [EncryptedCreditCardNumber4] using DECRYPTBYKEY function
SELECT CONVERT([varchar](16), DECRYPTBYKEY([EncryptedCreditCardNumber4])) AS [CreditCardNumber]
FROM [TestEncryption]
GO

ResultSet:

Benefits of using Cell-level encryption

  • Cell-level encryption offers more granular level of encryption.
  • Data is not decrypted until it is used so that even the page is loaded into memory, sensitive data is not in clear text.
  • Key can be assigned to users and protect by password to prevent automatic decryption.

Drawbacks of using Cell-level encryption

  • Administrator is burdened with maintaining the keys.
  • For cell-level encryption, the performance impact on SQL Server optimization is 20% more than it is for Transparent Data Encryption (TDE).

Conclusion

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

References

Encrypting Connections to SQL Server

I manage databases for financial organisation. These databases have confidential data of our customers such as credit card numbers, security related data etc. This confidential data must be encrypted when it is transmitted between the client and the server over the network. That way, if anyone is reading our network traffic, will not be able to interpret the data.

In this blog post, I’m going to discuss the different options available for encrypting SQL Server connections.

Continue reading

Securing Database Contents with Transparent Data Encryption (TDE)

Introduction

Microsoft SQL Server has many security features available within the database, but until release of SQL Server 2008 there has been no “out-of-the-box” method for protecting the data at the operating system level. The Transparent Data Encryption (TDE) feature introduced in SQL Server 2008 allows sensitive data to be encrypted within the data files to prevent access to it from the operating system. It solves the problems of security of data means encrypting databases on hard disk and on any backup media and is the best possible choice for bulk encryption to meet the regulatory compliance or corporate data security standards. This feature encrypts both data and logs as the records are written to SQL database files (*.mdf) in real-time, including backups, snapshots and transaction logs. TDE encrypts data before it’s written to disk and decrypts data before it is returned to the application. The encryption and decryption process is performed at the SQL layer, completely transparent to applications and users. TDE encryption uses a Database Encryption Key (DEK) (that is an asymmetric key secured by using a certificate stored in the master database), which is stored in the database boot record for availability during recovery.

In this post, I’ll show you how to encrypt database using Transparent Data Encryption (TDE) and then I will discuss the limitations of TDE.

Architecture of Transparent Data Encryption

The following illustration shows the architecture of TDE encryption:

Service Master Key is created at a time of SQL Server setup; DPAPI encrypts the Service Master Key. Service Master Key encrypts Database Master Key for the Master Database. The Database Master Key of the master Database Creates the Certificate then the certificate encrypts the database encryption key in the user database. The entire database is secured by the Database Master Key of the user Database by using TDE. TDE performs the encryption at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.

Microsoft Reference: Transparent Data Encryption (http://msdn.microsoft.com/en-us/library/bb934049.aspx)

Demo

Note: For the purposes of this post, I’ll be encrypting SQL Server 2012 sample database AdventureWorks2012 database using TDE. It is advisable to backup the database prior to implementing TDE.

Our first step to setup TDE is to create a database master key for our master database. To do that, open the New Query window and execute the following script:

USE [master]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1';
GO

Information about the database master key is visible in the sys.symmetric_keys catalog view. Execute the following query to verify that database master key is encrypted by the service master key:

USE [master]
GO

SELECT b.[name]
	,a.[crypt_type_desc]
FROM [sys].[key_encryptions] a
INNER JOIN [sys].[symmetric_keys] b ON a.[key_id] = b.[symmetric_key_id]
WHERE b.[name] = '##MS_DatabaseMasterKey##';
GO

Example:

Now the next step is to create a self-signed certificate that is protected by the database master key of our master database. This certificate encrypts the Database Encryption Key in the AdventureWorks2012 database.

Execute the following script to create the self-signed certificate:

USE [master]
GO

CREATE CERTIFICATE CertificateforTDE
	WITH SUBJECT = 'Certificate for TDE'
		,EXPIRY_DATE = '20220101';
GO

Navigate as follow, to view this certificate in SQL Server Management Studio:

Note: Certificate expiration is not enforced when the certificate is used for encryption.

Now backup your certificate and database master key immediately as you need them in a recovery situation. To do that, execute the following script to backup certificate:

USE [master]
GO

BACKUP CERTIFICATE CertificateforTDE TO FILE = 'D:\TDE_Demo\CertificateforTDE.cer'
WITH PRIVATE KEY (
		FILE = 'D:\TDE_Demo\CertificateforTDE.key'
		,ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
		)
GO

Execute the following script to backup the database master key of master database:

USE [master]
GO

-- Master key password must be specified when it is opened.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'

BACKUP MASTER KEY TO FILE = 'D:\TDE_Demo\ExportedMasterKey.key'
ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1';
GO

Once successfully executed, verify that the master key and security certificate backup files are created in the location specified in script for example for in this demo its D:\TDE_Demo\ (see below):

Note: Save your master key and security certificate backup files in a secure location as you’ll need them when restoring the database on a different SQL Server otherwise the restore process will fail.

Now that we have created the database master key and the certificate in the master database, we are now ready to create the database encryption key for our database.

Execute the following script to create the database encryption key in AdventureWorks2012 database:

USE [AdventureWorks2012]
GO

CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertificateforTDE;
GO

The AES_128 option specifies Advanced Encryption Standard (AES) with a 256 bit key length, and we protect the database encryption key with the AWTDECertificate certificate that was created in the Master database.

Note: SQL Server allow you to encrypt data with several encryption algorithms such as DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

The final step in the setup process of TDE is to enable it. This is accomplished by executing the ALTER DATABASE command with the SET ENCRYPTION ON argument.

Execute the following script to enable TDE on AdventureWorks2012 database:

USE [master]
GO

ALTER DATABASE [AdventureWorks2012]
SET ENCRYPTION ON;
GO

To verify that database is encrypted using TDE, right-click the database and choose option and you will see encryption option is now ON as shown in figure below:

Execute the following query, to find out what databases are encrypted using TDE:

USE [master]
GO

SELECT db.[name]
	,db.[is_encrypted]
	,dm.[encryption_state]
	,dm.[percent_complete]
	,dm.[key_algorithm]
	,dm.[key_length]
FROM [sys].[databases] db
LEFT JOIN [sys].[dm_database_encryption_keys] dm ON db.[database_id] = dm.[database_id];
GO

Recovery

Now perform the full backup of AdventureWorks2012 database using script below:

USE [master]
GO

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH NOFORMAT
	,NOINIT
	,NAME = N'AdventureWorks2012-Full Database Backup'
	,SKIP
	,NOREWIND
	,NOUNLOAD
	,STATS = 10;
GO

Now test the restore the database of AdventureWorks2012 database as follow:

As you can see from above that database restore is successful on same instance.

Now try restoring this database on different SQL Server using script below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH FILE = 1
	,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream'
	,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf'
	,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf'
	,NOUNLOAD
	,STATS = 5;
GO

As you can see, the restore process will fail with the error below:

This is because the database is encrypted with TDE.

To restore the TDE encrypted database on different SQL Server instance, you first need to restore the database master key and then the self-signed certificate that is used to encrypt the database encryption key.

Execute the following script to restore the database master key from master key backup:

USE [master]
GO

RESTORE MASTER KEY
FROM FILE = 'D:\TDE_Demo_Backup\ExportedMasterKey.key'
DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2';
GO

Next step is to create the SQL Server certificate on the second SQL Server using the Private Key backup of Principle SQL Server. To do that execute the following script:

USE [master]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2'

CREATE CERTIFICATE CertificateforTDE
FROM FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.cer'
WITH PRIVATE KEY (
		FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.key'
		,DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
		);
GO

We are now ready to restore the database on second SQL Server.

Note: You must OPEN MASTER KEY first and then perform restore otherwise restore will fail.

Execute the script below which we executed earlier to restore the AdventureWorks2012 database:

USE [master]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2'

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH FILE = 1
	,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream'
	,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf'
	,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf'
	,NOUNLOAD
	,STATS = 10;
GO

This time restore script executed successfully and AdventureWorks2012 database has now been restored on second SQL Server (see below):

Limitation of Transparent data encryption

  • TDE does not provide encryption across communication channels.
  • When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
  • The encrypting certificate or Asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
  • Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.