How SQL Server Can Make Things More Secure … Use Encryption!

One of the hottest topics in the technology world is security.  This topic is important because there are few things that can hurt a company more than stolen or misused data.  So, it is easy to see why security should always be an area of highest concern to DBA’s.  And one of the keys to having a secure environment is to understand the layers of security that can be involved with databases.  For example, security can be applied at the server level, to each SQL Server instance, to individual databases, to individual objects within the database, backups, etc.

In a previous post, I addressed some server and database level security (https://sqlbadboy.wordpress.com/2013/03/22/is-it-safe-is-it-secure/).  In this post, I am going to focus on how SQL Server uses encryption as a security measure.  Encryption is the process of obfuscating data, or purposely scrambling or masking it, by the use of keys and/or passwords.  Just to be clear, encryption does NOT solve access control issues, but rather adds another layer of security if those controls are bypassed.

*** NOTE: Please give thought as to when encryption needs to be used, since there is a performance overhead.  It should not be blindly applied to everything.  In some cases, it may be necessary to use encryption such as when data is moving over a public network.  On the other hand, it may not be necessary for data on an internal network.

How does SQL Server use encryption?

Here are the basics of SQL Server encryption, and the diagram is taken from: (Encryption Hierarchy: https://msdn.microsoft.com/en-us/library/ms189586.aspx):

50_Encryption

How to setup Transparent Data Encryption (TDE)?

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files.  The encryption of the database files is performed at the page level.  The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.  Also, using TDE does not increase the size of the encrypted database.

Steps to enable TDE on a database:

  1. Create a master key (https://msdn.microsoft.com/en-us/library/aa337551.aspx)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
  1. Create or obtain a certificate protected by the master key (https://msdn.microsoft.com/en-us/library/ms187798.aspx)
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
  1. Create a database encryption key and protect it by the certificate (https://msdn.microsoft.com/en-us/library/bb677241.aspx)
USE DatabaseName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
  1. Set the database to use encryption (https://msdn.microsoft.com/en-us/library/bb522682.aspx)
ALTER DATABASE DatabaseName SET ENCRYPTION ON

*** NOTE: Make sure that when you enable TDE that 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.  

Backup the database master key (https://msdn.microsoft.com/en-us/library/aa337546.aspx):

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
  ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Backup the certificate (https://msdn.microsoft.com/en-us/library/ms178578.aspx):

BACKUP CERTIFICATE TDECert 
TO FILE = 'C:\temp\dbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk',
ENCRYPTION BY PASSWORD = 'P@ssw0rd')

Moving a TDE enabled database (https://msdn.microsoft.com/en-us/library/ff773063.aspx):

If a database with TDE enabled needs to be moved to another server, you will have to use code like the following to restore the database master key and certificate.

RESTORE MASTER KEY
  FROM FILE = 'c:\temp\exportedmasterkey'
  DECRYPTION BY PASSWORD = 'P@ssw0rd'
  ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd';
CREATE CERTIFICATE TDECert
FROM FILE = 'D:\BACKUP\dbCerts\dbcert.cer'
WITH PRIVATE KEY (FILE = 'D:\BACKUP\dbCerts\dbcert_Key.pvk',
DECRYPTION BY PASSWORD = 'P@ssw0rd')

*** NOTE: At times, I have seen an issue with databases being restored with the following error, “Please create a master key in the database or open the master key in the session before performing this operation.”  In order to the master key to be used with all sessions, it must be encrypted by the service master key as follows:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Useful DMV’s to use with encryption:

Hopefully, this post gave enough information for a DBA to have a basic understanding of encryption in SQL Server and how to use it.

Advertisements
This entry was posted in DBA, Security and tagged , . Bookmark the permalink.

One Response to How SQL Server Can Make Things More Secure … Use Encryption!

  1. Pingback: Encrypted Database Objects … How to Guide | SQL Bad Boy – Michael Lowery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s