This article explains how to use transparent Data encryption (TDE) to protect a database, including the files that are backed up. To understand that this feature can be used to improve the security of your database application, please check this article.
STEP1:
Use master;
GO
IF EXISTS (SELECT
*
From
sys.databases
WHERE
name = ' Tdedemo ')
DROP DATABASE Tdedemo;
GO
CREATE DATABASE Tdedemo;
GO
Next, create the Server-level certificate which would protect the database key used to encrypt the database's files. This certificate in turn would be protected by the master key which if it does not exist would need to be created:
Create Master Key
Use master;
GO
IF not EXISTS (SELECT
*
From
Sys.symmetric_keys
WHERE
Name like '%[_]databasemasterkey% ')
BEGIN
CREATE MASTER KEY encryption by PASSWORD =
' 997jkhubhk$w4ez0876hkhjh5gh ';
END
GO
Create or obtain a certificate protected by the master key
CREATE CERTIFICATE Mytdecert
with SUBJECT = ' My TDE Certificate ';
GO
With the Server-level, the database can now is encrypted. This is do by first creating the database (symmetric) encryption key within the database and then enabling TDE:
Use Tdedemo;
GO
CREATE DATABASE encryption KEY with algorithm = aes_128 encryption by SERVER
CERTIFICATE Mytdecert;
GO
ALTER DATABASE Tdedemo
SET encryption on;
GO
Database encryption may take a and complete. While in progress, the Sys.dm_database_encryption_keys data management view would show the database in an encryption_state of 2:
SELECT
Db_name (database_id) as DB,
Encryption_state
From
Sys.dm_database_encryption_keys
WHERE
database_id = db_id ();
GO
Once TDE Encryption has been fully applied, the Encryption_state would become 3:
SELECT
Db_name (database_id) as DB,
Encryption_state
From
Sys.dm_database_encryption_keys
WHERE
database_id = db_id ();
GO
Now to demonstrate the protection of the database backup files through TDE, backup the database and its certificate. Please note that these is being backed up locally to the same location. This isn't a secure practice but is expediant for this demo:
Use master;
GO
BACKUP CERTIFICATE Mytdecert
to FILE = ' C:\temp\MyTdeCert '
With PRIVATE KEY (
FILE = ' C:\temp\MyTdeCertPrivateKey ',
Encryption by PASSWORD = ' 997jkhubhk$w4ez0876hkhjh5gh '
) ;
GO
BACKUP DATABASE Tdedemo
to DISK = ' C:\temp\TdeDemo.bak '
With INIT;
GO
By dropping the database and the Server-level certificate, we can simulate a restore to a different server:
DROP DATABASE Tdedemo;
GO
DROP CERTIFICATE Mytdecert;
GO
With the certificate missing, the restore operation would fail:
RESTORE DATABASE Tdedemo
From DISK = ' C:\temp\TdeDemo.bak ';
GO
MSG 33111, Level A, State 3, line 1
Cannot find server certificate with thumbprint ' 0x686a8264e4a17572fbae6a1d091a47d600847fb6 '.
MSG 3013, Level A, State 1, line 1
RESTORE DATABASE is terminating abnormally.
It's not until the certificate are recovered to the server and the backup file can be restored:
CREATE CERTIFICATE Mytdecert
From FILE = ' C:\temp\MyTdeCert '
With PRIVATE KEY (
FILE = ' C:\temp\MyTdeCertPrivateKey ',
Decryption by PASSWORD = ' 997jkhubhk$w4ez0876hkhjh5gh '
) ;
GO
RESTORE DATABASE Tdedemo
From DISK = ' C:\temp\TdeDemo.bak ';
GO
Processed 168 pages for database ' Tdedemo ', file ' Tdedemo ' on file 1.
Processed 2 pages for database ' Tdedemo ', file ' Tdedemo_log ' on file 1.
RESTORE DATABASE successfully processed pages in 0.157 seconds (8.415 mb/sec).
To reset the environment:
Use master;
GO
DROP DATABASE Tdedemo;
GO
DROP CERTIFICATE Mytdecert;
GO