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)')
GOCREATE 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.
