There is plenty of legislation and regulation in place these days that strongly suggest the encryption of data within a database.  In SQL Server, we have the ability to comply with these regulations in a couple of different ways.  We can choose to encrypt at the column level or we can choose to encrypt the entire database using TDE (transparent data encryption).

Given these opportunities and methods of encrypting a database and protecting data, some clients will want to comply with the regulation and move forward with encryption.  Every now and again, the client might run into something they can enable themselves.  TDE happens to be one of those things.  Enabling TDE is pretty easy.  Sometimes things run very smoothly and sometimes there is a problem.  Unfortunately, the problem may not always be immediately evident.

This is one of those times.  If you enable a setting in a database, you would expect it to be in effect and to be working.  Sometimes, with TDE, that change doesn’t take effect. What actually happens is the switch is thrown, the database tries to comply, but something prevents the database from completing the directive it was just given.  As luck would have it, I had the opportunity to assist recently with such a case.

Infinitely Encrypting a Database

Before diving too far down into the problem, let’s take a look at how we can gauge and measure the encryption progress.  Microsoft has provided a dynamic management view to see and track the status and progress of encryption that we have tried to enable.  This view is sys.dm_database_encryption_keys.  Within that view there are a few important and useful fields.  One field is the encryption thumbprint, another is the encryption state, and another tells us the percent that has been completed.

With a little tsql from the toolbelt, we can pull back the essential information to monitor the encryption progress.

SELECT DB_NAME(ek.database_id) AS DBName,ek.percent_complete,ek.modify_date
,ek.encryption_state
,CASE ek.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'
END AS Encryption_State_desc
FROM sys.dm_database_encryption_keys ek;

You’ll notice that I have a case statement in there to transform the encryption_state values into a human friendly form.  Sadly, the human friendly values are not stored in the database and are only documented in MSDN / BOL.

Now on to the problem at hand—the encryption that starts but never ends.  Given that I have a database named “Published”, I can enable encryption with the following script.

ALTER DATABASE Published
SET ENCRYPTION ON;

After issuing the encryption command, I can then pull up my “status” script and see how things are rolling.

In this example, and for the sake of this article, this database will remain at 0% complete for days and months if allowed to remain that way.  Notice that it still thinks it is progressing, but nothing is actually getting done.  This kind of result can also be seen after a database reaches 99% complete.  At some point, something occurred that caused grief for the encrypting process.  As soon as that happens, the report will revert back to 0% complete and just stall indefinitely.  The types of things that can cause this vary from corruption to index rebuilds occurring at the same time as the attempt at encryption.

When a stall such as this occurs, the fix could be as simple as restarting the encryption with the statement previously used.  Here that is again (bearing in mind to substitute the appropriate database name for the one I used in this demo):

ALTER DATABASE Published
SET ENCRYPTION ON;

In some cases, that won’t work.  And sometimes you just may want to proceed very cautiously.  In those events, you can get your database back on track to being encrypted by using the following:

DBCC TRACEON(5004)
GO
DBCC TRACEOFF(5004)
GO
ALTER DATABASE Published
SET ENCRYPTION ON;

The use of this trace flag forces the page scan, used to encrypt the database, to stop (even though it already appears to have stopped).

If I check my encryption status again, I should see the percentages growing until 100% is achieved and eventually the state that declares the database as being encrypted.  This is demonstrated in the following three screenshots.

And finally we have an encrypted database.

Conclusion

Transparent Data Encryption is useful in helping get a database into compliance with several regulations.  It is also fairly easy to implement.  Occasionally, it may only appear to have completed successfully.  When the SQL statement completes, TDE is not entirely in place.  Though it is “enabled”, it may still be waiting for the encryption phase to complete.  Once the statement to encrypt the database has been issued, it is important to follow up with a bit of monitoring to ensure the database progresses to the encrypted state.  If that doesn’t happen, you could end up in a state where you think the database is encrypted and it actually just stalled.

If you enjoy reading about security and encryption, check out these articles related to security and audits.