Someone asked a question about TDE (Transparent Data Encryption) and DDM (Dynamic Data Masking), which are two different technologies that are in the security area. As I’ve mentioned in the Stairway to Dynamic Data Masking, DDM is not a security technology. It makes programming some obfuscation easy, but it’s not really security. It can be easily bypassed.

In any case, how do these work together? Or do they work together? Good questions, and I’ll answer them.

First, TDE is a technology that encrypts your data at rest, meaning when on storage devices. This handles the encryption and decryption as data is read or written from storage, without coding for it or the user having to do anything.

DDM is a technology that takes results from a query and replaces some of the data with masked values. This works in memory, after a query runs. Query processing, with WHERE, GROUP BY, etc. clauses is not affected by DDM.

These work together since data is encrypted on disk. It is read into RAM and decrypted. The query processor then assembles some of this data into a result set. Once this is done, before the results are sent to the client, DDM will make data.

Demo

I’ll setup TDE and then add DDM and run a query. Here’s a quick table and enabling TDE:

CREATE DATABASE TDE_Primer;

GO

-- create and populate a table

USE TDE_Primer

go

CREATE TABLE MyTable

( myid INT

, myname VARCHAR(20)

, mychar VARCHAR(200)  

)
;

go

DECLARE @i INT = 65;

WHILE @i < 92
  begin
   INSERT mytable SELECT @i, 'Steve Jones', REPLICATE(CHAR(@i), 200);
   SELECT @i = @i + 1;
  END;

GO

From here, let’s enable TDE. If you have a master key in the master database, you’ll get an error, but it won’t really affect things.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This';

go
-- create certificate to secure TDE

CREATE CERTIFICATE TDEPRimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';

go

USE TDE_Primer;

GO

-- Create DEK

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDEPRimer_CertSecurity;

GO
ALTER DATABASE TDE_Primer
   SET ENCRYPTION ON;

GO

Once this is done, let’s alter our table. We also need a user that isn’t privileged.

ALTER TABLE dbo.MyTable ALTER COLUMN myname ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",0)')

GO
CREATE USER JoeDBA FROM LOGIN JoeDBA
GRANT SELECT ON dbo.mytable TO JoeDBA

Now we can check the execution of a query.

Masked data with TDE.