Transparent Data Encryption
(TDE) is one of the options to protect your data available wih SQL2008. This
encrypts your database, data files, transaction log, and the backup files
created for your database. Keep in mind,
however, that this has been known to cause a 20-25% increase in processor load
and a significant performance hit. But
if your number 1 priority is encrypting your data, this is one option.
Enabling TDE Encryption
The following SQL commands
will get TDE setup in your SQL2008 database. You must have a certificate and
private key already generated for this.
Like many sites, you can use the same certificate and private key for
all of your SQL2008 Databases.
--- SET
ENCRYPTION
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
GO
CREATE CERTIFICATE tdeCert
FROM FILE = 'path to certificate file'
WITH PRIVATE KEY (FILE = 'path to private key file',
DECRYPTION BY PASSWORD = 'password');
GO
USE DBNAME
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE tdeCert;
GO
USE master;
ALTER DATABASE DBNAME
SET ENCRYPTION ON;
GO
After this command, you can
check the status of the encryption of the database with the below query.
--- CHECK
ENCRYPTION STATUS
USE master
SELECT
database_id, encryption_state, percent_complete
FROM sys.dm_database_encryption_keys;
GO
Now you’re all set.
Disabling TDE Encryption
The following process will
get TDE removed from your database, after you have determined the performance
hit is too much, or the novelty of having an encrypted database has worn off.
Removing TDE is basically
the reverse of enabling it, however you will need to wait between steps.
--- REMOVE
ENCRYPTION
USE master;
ALTER DATABASE DBNAME
SET ENCRYPTION OFF;
GO
This will make the database
start the decryption process. Use the query below to check the status of the
decryption process.
--- CHECK
ENCRYPTION STATUS
USE master
SELECT
database_id, encryption_state, percent_complete
FROM sys.dm_database_encryption_keys;
GO
You may need to truncate the
transaction log, or shrink the log file, if the encryption state stays at 2 with
percentage 0 for a long period of time. Then stop/restart the SQL Server
instance. When your server is back up re-run the above query to see the
complete decryption status. This second time will run much faster.
Once the process completes
and the database_id you are decrypting is no longer encrypted (will show encryption state 1 when complete), you can go on to the encryption key removal.
--- REMOVE THE
ENCRYPTION KEY
Use DBNAME
DROP DATABASE ENCRYPTION KEY
GO
Now simply remove the master
key.
--- REMOVE THE
CERTIFICATE AND MASTER KEY
USE master
DROP CERTIFICATE tdeCert
DROP MASTER KEY
GO
Hope this helps...
