Is my database secure now?
Though we have successfully enabled TDE on our database, we need to make sure that it is secured at all levels. We will do two tests on this. First we will backup the database and try to restore the backup on another SQL Server 2008 instance. The restore operation must fail unless the certificate used for securing the DEK’s private key is available in the master database. Secondly, we will try to attach the mdf and ldf files of the TestDatabase in another instance. It should not work too. Here is the code for testing.
— First step is backing up the TestDatabase.
BACKUP DATABASE [TestDatabase]
TO DISK = N’E:TestDatabaseFull.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase-Full Database Backup’
GO
— Now connect with another SQL Server 2008 instance.
— Try to restore the backup we have taken, in the new instance.
— This statement should be failed.
RESTORE DATABASE [TestDatabase]
FROM DISK = N’E:TestDatabaseFull.bak’
WITH FILE = 1,
MOVE N’TestDatabase’ TO N’D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATestDatabase.mdf’,
MOVE N’TestDatabase_log’ TO N’D:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATestDatabase_log.ldf’,
NOUNLOAD, STATS = 10
GO
The first steps backs up the database. The second part needs to run in a different SQL Server 2008 instance. When you try to restore the backup in a different instance of SQL Server, you will get an error message similar to below;
10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
52 percent processed.
62 percent processed.
73 percent processed.
83 percent processed.
94 percent processed.
100 percent processed.
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
You will face for same issue when you try to attach the database to the another instance.
USE [master]
GO
CREATE DATABASE [TestDatabase] ON
( FILENAME = N’D:TestTestDatabase.mdf’),
( FILENAME = N’D:TestTestDatabase_log.ldf’)
FOR ATTACH
GO
The result of above code is;
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58’.
It works. Our database is secured. The only way to restore or attach the TestDatabase in another instance is, adding the same certificate in the second instance. Study the code given below;
— backup the certificate
— The private key will be encrypted by the password specified
BACKUP CERTIFICATE DEKCertificate TO FILE = ‘E:DEKCertificate.cert’
WITH PRIVATE KEY
(
FILE = ‘E:Instance1PrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘Pa$$w0rd5454’
)
— create the certificate in the second instance by using the backup
— Private key will be loaded from the saved file.
USE [master]
GO
CREATE CERTIFICATE DEKCertificate
FROM FILE = ‘E:DEKCertificate.cert’
WITH PRIVATE KEY (
FILE = ‘E:Instance1PrivateKey.key’
, DECRYPTION BY PASSWORD = ‘Pa$$w0rd5454’
)
The first part of the code backs up the certificate into a file. It backs up the private key of the certificate too. The password specified in the code is for encrypting the private key. The second part of the code needs to be run in the second instance of SQL Server 2008. It creates a certificate by using the backup certificate. Once the code is run, you will be able to either restore or attach the TestDatabase database into the new instance.
What needs to be considered before enabling the TDE?
There are few things you need think about before enabling TDE on your database. They are;
Does TDE harm the implemented disaster recovery plan?
Think about a simple disaster recovery plan, backup and restore. You may have developed this plan and it works without any problems. You enabled TDE, still no problems, scheduled jobs back up your databases. Assume that the server started producing fatal errors which led you to install the OS and SQL Server again. You may easily go for re-installation without thinking twice, because you have your database backup with you. The problem comes when the databases are restored. You may have full backups of the databases that are not in encrypted format, you may have some of transactional backups that are taken after the TDE enabled, hence encrypted. You do not have the backup of certificates you used for TDE. This will lead you in an unexpected situation. Since you do not have the backup of the certificate used, you will not be able to restore the transactional backups.
Think about the development disaster recovery plan before enabling TDE. If you have plan, make sure that the plan works even after enabling the TDE. This applies not only for the backup and restore strategy, it applies other plans such as log shipping and database mirroring too.
Are there read-only file groups in your database?
If the database has read-only file groups, TDE will fail. When I test this, once the TDE is enabled, the encryption_state will never be 3 (encrypted) but 2 (encryption in progress). I found that SQL Server did not throw any exception while running the TDE code. Somehow, if you open the properties window of the database after enabling TDE, you will see that the value of the property Encryption Enabled is set to true. Let’s test this using the code below:
— create a new database for testing TDE on readonly file groups
USE master
GO
CREATE DATABASE [TestDatabase2] ON PRIMARY
( NAME = N’TestDatabase2_Primary’, FILENAME = N’E:TestDatabase2_Primary.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1_Default]
( NAME = N’TestDatabase2_FG1′, FILENAME = N’E:TestDatabase2_FG1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2_ReadOnly]
( NAME = N’TestDatabase2_FG2′, FILENAME = N’E:TestDatabase2_FG2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TestDatabase2_log’, FILENAME = N’E:TestDatabase2_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO
— Set the FG1_Default file group as the default one.
USE [TestDatabase2]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N’FG1_Default’)
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG1_Default] DEFAULT
GO
— Add a table to the default file group
USE [TestDatabase2]
GO
CREATE TABLE TestTable1 (Id int PRIMARY KEY, [Text] varchar(100))
GO
INSERT INTO TestTable1 VALUES (1, ‘hello’)
— Add a table to the FG2_ReadOnly file group
CREATE TABLE TestTable2 (Id int PRIMARY KEY, [Text] varchar(100))
ON [FG2_ReadOnly]
GO
INSERT INTO TestTable2 VALUES (1, ‘hello’)
GO
— Set the file group FG2_ReadOnly file group as READONLY
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N’FG2_ReadOnly’)
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG2_ReadOnly] READONLY
GO
— Create Database Encryption Key (DEK) in the user database
USE TestDatabase2
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID(‘TestDatabase2’))
BEGIN
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO
— Enable TDE on the database
ALTER DATABASE TestDatabase2
SET ENCRYPTION ON
GO