I am writing this article because I could not find a simple explanation on how to encrypt, backup and restore a SQL database. This applies to SQL 2008 and 2008 R2 and most likely any version that uses Transparent Data Encryption (TDE) in the future.
Other titles/search-terms that you may have searched for are…
- How to Encrypt a SQL Database
- Apply TDE
- Restore a TDE database
- Restore an encrypted SQL database
- Backup/Restore a TDE Certificate
Below are the steps to Encrypt a SQL Database. The MOST important part though is to note the passwords used and to save the certificate and private key files. If you lose any of that information, you database can never be restored and is lost forever!
Enable Encryption on a SQL Database
1. Create a master key encryption in Master database.
2. Create a Certificate that TDE will use.
3. BACKUP the certificate to 2 files. (A Cert File and Private Key file that is encrypted with another password)
4. Create a Database Encryption Key based off of the Cert you created in step 2
5. Turn Encryption on in the target database
Sample Encrypt Database Script
–Step 1
USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ’1StrongPassword’
go
–Step 2
CREATE CERTIFICATE TDECert WITH SUBJECT = ‘TDE Certificate’
go
–Step 3
BACKUP CERTIFICATE TDECert TO FILE = ‘C:\Temp\TDECert.Cer’
WITH PRIVATE KEY (
FILE = ‘C:\Temp\TDECertPrivateKey.pvk’,
ENCRYPTION BY PASSWORD = ’2ndStrongPassword’)
go
–Step 4
USE SecureTest
go
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
go
–Step 5
ALTER DATABASE SecureTest
SET Encryption ON
Needed in order to Restore an Encrypted Database
1. The Master Database Encryption Password
2. The Certificate File Used
3. The Private Key File Used
4. The Decryption password for 2 and 3 (It is the same password for both).
Restore Encrypted SQL Database
1. Create the Master Key in the Master Database
2. Restore the Certificate used for encrypting the database to the Master Database.
This is done from 2 files (which are encrypted using yet another password)
-The Certificate File and the Private Key File.
-These are restored using the "CREATE CERTIFICATE" command as "RESTORE CERTIFICATE" is not a valid command.
3. Restore the database normally.
Sample Restore Database Script
–Step 1
USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ’1StrongPassword’
go
–Step 2
CREATE CERTIFICATE TDECert
FROM FILE = ‘C:\Temp\TDECert.Cer’
WITH PRIVATE KEY (FILE = ‘C:\Temp\TDECertPrivateKey.pvk’,
DECRYPTION BY PASSWORD = ’2ndStrongPassword’)
go
–Step 3
–Restore database normally. It will use the cert from step 2
