Transparent Data Encryption (TDE)
SQL Server has two ways of encrypting data. One way is by protecting data on the table, record or column level, and the other way is by protecting data “at the rest”. One of the best crypto features in the database world today is known as a Transparent Data Encryption.
Imagine the following scenario. Someone has an unauthorized access to your database system environment. That person finds a way to get the last database backup file, copies it and takes it in an unsecured environment. In this moment, the security mechanism just fell apart.
This scenario illustrates what can happen when someone illegally copies, detaches, and restores your database. The consequences for such activity can be substantial, depending on the sensitivity of your data environment.
TDE performs real-time I/O encryption and decryption of the database files (data and log). The encryption uses a Database Encryption Key (DEK) which is stored in the database boot record for availability during recovery. Backup of databases that have implemented TDE are also encrypted by using the DEK.
DPAPI encrypts the SMK (created at the time of a SQL Server setup). SMK encrypts the DMK of the master database. DMK of the master database creates a certificate in the master database. The certificate encrypts the DMK in the user database. The entire user database is secured by DEK of the user database by using TDE.
To implement TDE, we need to follow these steps:
- Create a master key
- Create or obtain a certificate protected by using the master key
- Create a database encryption key and protect it by using the certificate
- Set the database to use encryption
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd'; GO CREATE CERTIFICATE TDE WITH SUBJECT = 'My TDE Certificate'; GO SELECT * FROM sys.certificates CREATE DATABASE CryptoDB GO USE CryptoDB GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE; GO ALTER DATABASE CryptoDB SET ENCRYPTION ON; GO USE master GO DROP DATABASE CryptoDB GO DROP CERTIFICATE TDE GO DROP MASTER KEY GO |
Key point of TDE is that an entire database is encrypted on the fly. Data in an encrypted database are encrypted before they are written to a disk and decrypted when read into memory. In this case you should pay attention on the performance issue on heavily transaction load system.
Encrypted backup
In some case scenarios you only want to encrypt backup files to protect data “at the rest”. SQL Server 2014 have one new crypto feature and that is encrypted backup. In a nutshell almost same implementation like TDE, but major difference that database is not on the crypto “pressure” during regular transaction processes. The idea is that you have a business need to keep your backup secured. In this case there is no performance issues because the only encryption/decryption is during the backup/restore operation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe' GO CREATE CERTIFICATE CryptoBackup WITH SUBJECT = 'My BackUp Certificate'; GO CREATE DATABASE CryptoDB GO BACKUP DATABASE CryptoDB TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CryptoDB.bak' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = CryptoBackup ), STATS = 10 GO DROP CERTIFICATE CryptoBackup GO DROP MASTER KEY GO DROP DATABASE CryptoDB GO |
Or you can do it through SSMS GUI:
Before you can access the Encrypted backup option on this dialog window, you need to check (under the Medio Options tab) this option:
Symmetric encryption
Symmetric encryption is the type of encryption that uses the same key for encryption and decryption. SQL Server allows you to choose from several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
When a symmetric key is created, it must be encrypted by using at least one of the following:
- Certificate
- Password
- Symmetric key
- Asymmetric key
Example of creating a symmetric key encrypted by certificate:
|
1 2 3 4 5 6 7 8 |
-- Create a AES 256 symmetric key CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCert; GO |
Implementing Symmetric Encryption
In this exercise, you will pass all necessary steps for implementing symmetric encryption in a user-created sample database. Also, you will encrypt data in a user table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
--1. Create sample database. CREATE DATABASE SymCryptDB GO USE SymCryptDB GO -- 2. Create DMK. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd'; GO --3. Create certificate and symmetric key. CREATE CERTIFICATE SymCert WITH SUBJECT = 'Certificate for sym key', START_DATE = '2015-01-06', EXPIRY_DATE = '2016-01-06'; GO CREATE SYMMETRIC KEY SymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE SymCert; GO --4. Create sample table: CREATE TABLE EncryptedCustomer ( CustomerID int NOT NULL PRIMARY KEY, FirstName varbinary(200), MiddleName varbinary(200), LastName varbinary(200) ); --5. Open the key that is protected by the certificate. OPEN SYMMETRIC KEY SymKey DECRYPTION BY CERTIFICATE SymCert; GO --6. Insert the sample from AdventureWorks2014 and encrypt the data. INSERT INTO EncryptedCustomer ( CustomerID, FirstName, MiddleName, LastName ) SELECT BusinessEntityID, EncryptByKey(Key_Guid(N'SymKey'), FirstName), EncryptByKey(Key_Guid(N'SymKey'), MiddleName), EncryptByKey(Key_Guid(N'SymKey'), LastName) FROM AdventureWorks2014.Person.Person; GO --7. Close the key. CLOSE SYMMETRIC KEY SymKey; GO --8. Check the encrypted data. SELECT * FROM EncryptedCustomer; GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--9. Decrypt the sample data. OPEN SYMMETRIC KEY SymKey DECRYPTION BY CERTIFICATE SymCert; GO SELECT CustomerID, CAST(DecryptByKey(FirstName) AS nvarchar(100)) AS DecryptedFirstName, FirstName FROM EncryptedCustomer; GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--10. Cleaning part DROP SYMMETRIC KEY SymKey GO DROP CERTIFICATE SymCert GO DROP MASTER KEY GO |
Asymetric encryption
Asymmetric key pair is made up of a private key and a public key. Each key can decrypt data encrypted by the other key pair. Asymmetric encryption/decryption are relatively resource-intensive, but they provide a higher level of security than the symmetric encryption.
| Asymmetric Algorithms | ||
| Keyword | Algorithm | Key Length (Bits) |
| RSA_2048 | RSA | 2048 |
| RSA_1024 | RSA | 1024 |
| RSA_512 | RSA | 512 |
A asymmetric key is a database object protected at the database level. When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly.
By default, the private key is protected by the DMK. If no DMK has been created, a password is required to protect the private key. If a DMK does exist, the password is optional.
The private key can be 512, 1024, or 2048 bits long.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE ASYMMETRIC KEY StrongKey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd'; GO CREATE ASYMMETRIC KEY KeyPairFromFile AUTHORIZATION Denis FROM FILE = 'C:\Keys\Asymmetric\DenisCert.tmp' ENCRYPTION BY PASSWORD = 'Some3xtr4Passw00rd'; GO |
Symmetric vs. Asymmetric
Is one type of encryption key recommended over the other? Yes, but as always, performance is an issue. Symmetric key algorithms are mathematically simpler, and as a result, faster. The difference in speed can be significant even into the 100x faster range. Therefore, symmetric key algorithms are the way to go when encrypting data.
In moust cases asymmetric encryption in SQL Server is used to defend a symmetric key
Hashing
Sometimes business requirements will demand to hide some data without using encryption/decryption. There are plenty of mechanisms to make some data/information unreadable. Hashing is one of those mechanisms. Hash functions are powerful, fast and efficient ways to hide data and to check data integrity.
A cryptographic hash function is a function that implements an algorithm that takes some data and returns a fixed-size bit string.
The hash function has the following main properties:
- Easy to compute the hash value for any given message
- Impossible to generate a message that has a given hash
- Impossible to modify a message without changing the hash
- Impossible to find two different messages with the same hash.
Example;
|
1 2 3 4 5 6 7 |
SELECT HashBytes('SHA1', 'Jasmin Azemović'); Result is: ------------------------------------------ 0x026A71290A685DA3E09A11CA110014D3E0118191 |
In next example we will just add an extra space after name:
|
1 2 3 4 5 6 |
SELECT HashBytes('SHA1', 'Jasmin Azemović'); GO SELECT HashBytes('SHA1', 'Jasmin Azemović'); GO |
As you see, hash output is totally different
Hashing and encryption are not the same function and each have a totally different usage in practice. The basic point to remember is that hashing is used when there is no need to make a reversible operation, whereas encryption is used when you need to decrypt data at some later point in time.
Summary
As you have seen so far, protecting data is the most important thing in database environments. When all security elements fail (i.e. installation errors, authentication, authorization, bad access policy, etc.), there is no more protection. This two articles taught you how to implement advanced techniques for protecting data such as cryptography.
He is active in the area of databases and information security. He has an academic degree Ph.D. in the field of modeling, design and development an environment for the preservation of privacy beyond traditional SQL limitations. Jasmin regularly publishes research papers in indexed databases as include are: IEEE, Inspec, EI Compendex and ACM.
He is also a very active member in the professional IT world: Microsoft MVP (SQL Server), MCT, and author of several books in English: Writing T-SQL Queries for Beginners Using Microsoft SQL Server 2012 (MVP Press 2012), Securing Microsoft SQL Server 2012, MVP Press.
View all posts by Jasmin Azemovic
- SQL Server Confidential – Part II – SQL Server Cryptographic Features - January 29, 2015
- SQL Server Confidential – Part I – Crypto basics and SQL Server Cryptographic Features - September 29, 2014