TDE: Looking inside your database and backups

TDE (Transparent Data Encryption) encrypts the data files at rest but don’t forget that it also encrypts your backup file too. I fancied looking inside the MDF and a backup of my database before enabling TDE and comparing it to when enabled.

Continue reading

My backup and SLEEP_BPOOL_FLUSH

I noticed that my backup was taking longer than usual.

I went to Perfmon to look at some counters, more specifically Physical disk:Avg sec/read(orange/yellow line) and Physical disk:Avg sec/write(blue line), yes it was very busy, much busier than normal.

bak2

Waits stats was painting the following picture: I was very intrigued with the sleep_bpool_flush so I headed straight over to the waits library.

waits1bak

You get this wait type when the checkpoint process realizes that it’s saturating the I/O subsystem. (https://www.sqlskills.com/help/waits/sleep_bpool_flush/), so it seemed that my backup could not even issue a checkpoint successfully. With that thinking I decided to do a manual checkpoint which just made the problem worse.

blockme

After some detective work I found the answer. Someone enabled TDE and it was going though the encryption process whilst I was trying to take a backup so my backup was never going to complete anytime soon.

The error message I was ended up with was  “Msg 5901, Level 16, State 1, Line 7 One or more recovery units belonging to database ‘NEWdb’ failed to generate a checkpoint”.

You can use the following query to understand the state of the encryption process for your databases.

SELECT
db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id

bak3

When you have a state of 2 it means encryption is in progress, you may have heard of this as an encryption scan.

This behaviour is confirmed via Books on line and totally makes sense. (Last bullet point)

The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE…SET ENCRYPTION statements.

  • Dropping a file from a filegroup in the database.
  • Dropping the database.
  • Taking the database offline.
  • Detaching a database.
  • Transitioning a database or filegroup into a READ ONLY state.
  • Using an ALTER DATABASE command.
  • Starting a database or database file backup.

You live and learn.

TDE Recovery

This week’s TSQL Tuesday is being held by the mighty Kenneth Fisher – Backup and Recovery- see this link, how can I not get involved with this one?

tsql

I have been blogging for about a year now and have covered most of Kenneth’s bullet points – except Internals and SSAS based stuff, so I decided to do something different but still about backups. This post is all about the importance of backing up certain objects when you are using TDE – Transparent Data Encryption.

To understand TDE see my older post  – https://blobeater.blog/2016/11/22/playing-with-tde/

So I have enabled TDE and I have a Full backup – lets restore it to a different server.

USE [master]
RESTORE DATABASE [DWHDB]
FROM  DISK = N'C:\temp\dwh.bak' WITH  FILE = 1, STATS = 5
GO

Cannot find server certificate with thumbprint ‘0xC0367AC8E9AE54538C17ACB0F63070D6FF21316A’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

The dreaded cannot find server certificate with thumbprint message. Why is this happening?

Well if you read my post on TDE ( or anybody else’s) you would have noticed the following message:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

So the point is – back them up

USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'c:\data\MyServerCert.cer'
WITH PRIVATE KEY
(FILE = 'c:\data\certificate_MyServerCert.pvk',
ENCRYPTION BY PASSWORD = 'xxxxxxxx')

Then on the restoring server you will need to issue the following (assuming you have a database master key too)


CREATE CERTIFICATE MyServerCert
FROM FILE = 'c:\temp\MyServerCert.cer'
WITH PRIVATE KEY (FILE = 'c:\temp\certificate_MyServerCert.pvk',
DECRYPTION BY PASSWORD = 'xxxxxxxx');

GO
USE [master]
RESTORE DATABASE [DWHDB]
FROM  DISK = N'C:\temp\dwh.bak' WITH  FILE = 1, STATS = 5

GO

RESTORE DATABASE successfully processed 13002 pages in 0.444 seconds (228.770 MB/sec) – the restore works a charm.

 

 

Playing with TDE

UPDATE November 2016 – With SQL Server 2016 SP1, Microsoft will include key enterprise-class features in every edition of SQL Server 2016. This includes TDE! So lets have a recap.

What is it?

Quite simply – ENCRYPTION at rest; your data files will be encrypted on disk which is done at page level. So the page will be encrypted when written to disk and decrypted when read into the buffer pool.

Architecture

Taken from Books on line (BOL) we will ultimately be going though the below steps in strict order.

tde

TSQL

SELECT * FROM sys.symmetric_keys

tde1

You will see that I have a Service master key and I have already created my Database master key. So let’s create a certificate.

USE master
GO
CREATE CERTIFICATE TDECertAW WITH SUBJECT = 'tde Certificate AW'

Now that we have created a certificate we will use it to encrypt the Database Encryption key in the user database.

USE [CustRecords]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECertAW;

GO

Output = Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

So yes, you really should follow the instructions above and store these files in a secure offsite location.

USE master;
GO
BACKUP CERTIFICATE TDECertAW
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TDECertAW.cer'
WITH PRIVATE KEY
(FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\certificate_TDECertAW.pvk',
ENCRYPTION BY PASSWORD = '!£$Strongpasswordherewelikesqlserver#')

Enable it

ALTER DATABASE [CustRecords] SET ENCRYPTION ON;

GO
--check

USE master;
GO
SELECT
db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE name = 'CustRecords'

State 3 which means encrypted. (Please note that TempDB will also get encrypted).

tde3

Digging deeper

Let’s look at a page before encryption was enabled. First the contents of a table from SSMS (SQL Server Management Studio)

tde4

So let’s calculate the offset for page 78:  78*8192 = 638976, so when we use the hex-editor we shall look for 638976 (decimal) as the beginning of the page.

tde5

You will see the ASCII representation of the data. This is what we expect for a database that does not have TDE enabled.

So what happens when TDE is enabled?

Let’s check a page first using print option 3 which means per row interpretation.

DBCC TRACEON (3604);
GO
DBCC PAGE (CustRecords, 1, 78, 3);

tde6

Above is a snippet where you see decrypted data – as you would expect.

So let’s look at the MDF (on-disk) using the technique discussed earlier – again navigating to the offset via a hex-editor.

tde7

This is the outcome of enabling TDE – see the difference?

 

 

Azure SQL Database – TDE

I work in the financial space so you can imagine that security is quite high on the agenda. TDE (Transparent Data Encryption – see this article for more details https://blobeater.wordpress.com/category/sql-server-2/tde/ ) is nothing new, setting it up on “earthed” SQL Servers can have some what of an overhead but in the Azure world it is so simple to setup.

The big advantage of TDE in Azure over the earthed flavour is that Microsoft does alot of the work for you. Assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Also Microsoft will rotate your certificate at least every 90 days.

Azure SQL Database does not support Azure Key Vault integration with TDE. SQL Server running on an Azure virtual machine (IaaS) can use an asymmetric key from the Key Vault.

Within the settings section of your Azure SQL Database you will find the TDE option as shown below. To do this successfully you will need to be connected as the Azure Owner, Contributor, or SQL Security Manager.

enc1

Select ON for the data encryption option and click save.

enc2

Seriously, that is all it takes.

I will then move to SSMS (SQL Server Management Studio) and issue the following query to understand the status. (Credit to David Pless: TSP – Microsoft)

 SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0' THEN 'No database encryption key present, no encryption'
         WHEN '1' THEN 'Unencrypted'
         WHEN '2' THEN 'Encryption in progress'
         WHEN '3' THEN 'Encrypted'
 WHEN '4' THEN 'Key change in progress'
 WHEN '5' THEN 'Decryption in progress'
 WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'
         ELSE 'No Status'
      END,
percent_complete, create_date, key_algorithm, key_length,
encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

enc3

Naturally tempdb gets encrypted too along with the user database. Azure uses AES-256 bit algorithm.

These sorts of activities get tracked and logged by the Activity log within Azure.

enc4