I ran into an interesting problem lately. I have a table that has encrypted data with a key on a column. The data however needs to be a piece of an ETL process and then it has to become unencrypted.
This led me to ask the question: Can I create a table, insert a record, encrypt the record, move the record to various tables, and finally decrypt the record in a new table successfully?
Microsoft calls this column encryption, since this is a property of a table, I’d partially expect for it to be specific to the table that holds the data. The answer is in the MSDN articles if you read it thoroughly however. “Encrypt a Column of Data”. It’s not encrypt the column in the table, nor is it encrypt the column, it’s a column of data that becomes encrypted with a key and cert.
Let’s create a test scenario here and put the idea to the test. I couldn’t find a definitive black and white answer immediately online so I figured we should figure it out ourselves!
-- Create test table source
CREATE TABLE TestETLSource (
id INT PRIMARY KEY identity(1, 1)
,FakeData VARCHAR(255)
,FakeDataEncrypted VARBINARY(128)
,StartTime DATETIME2(7)
,EndTime DATETIME2(7)
)
-- Insert a record
INSERT INTO TestETLSource (
FakeData
,StartTime
,EndTime
)
VALUES (
'Testtesttestyo'
,'2017-01-02 02:02:02.222'
,'2017-01-04 02:02:02.222'
)
Before we begin messing with the keys and certificates, let’s see what our current setup is.
--Reference queries to find any symmetric keys, asymmetric keys, and Certificates.
SELECT NAME
,key_length
,algorithm_desc
,create_date
,modify_date
FROM sys.symmetric_keys;
SELECT NAME
,algorithm_desc
FROM sys.asymmetric_keys;
SELECT NAME
,subject
,start_date
,expiry_date
FROM sys.certificates;
My results returned typical MS certificates, nothing I’ve made before. So I’ll go ahead and create a master key and cert.
If you already have a master key or certificate, you can skip this immediate section.
-- Create master key because none exists
CREATE master KEY ENCRYPTION BY password = 'some strong password'
-- Create certificate
CREATE CERTIFICATE FakeCert ENCRYPTION BY PASSWORD = 'some strong password'
WITH SUBJECT = 'Just some fake data';
GO
Now that we have a master key and certificate, we can create a symmetric key to use that certificate.
-- Create key
CREATE SYMMETRIC KEY FakeKey
WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE FakeCert;
GO
Let’s test the key out!
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY FakeKey DECRYPTION BY CERTIFICATE FakeCert
WITH password = 'some strong password';
Now that it is working, we can go ahead and encrypt the value in the column FakeData using the symmetric key FakeKey. We’ll save the data in the column FakeDataEncrypted.
UPDATE TestETLSource
SET FakeDataEncrypted = EncryptByKey(Key_GUID('FakeKey'), FakeData);
GO
Make sure to close your key in your session when you are done!
CLOSE SYMMETRIC KEY FakeKey;
GO
Now let’s verify the results. First, open the symmetric key with which to decrypt the data:
OPEN SYMMETRIC KEY FakeKey DECRYPTION BY CERTIFICATE FakeCert
WITH password = 'some strong password';
Now let’s select the FakeData and FakeDataEncrypted column. If the decryption worked, the data will match the decrypted value.
SELECT FakeData
,CONVERT(NVARCHAR, DecryptByKey(FakeDataEncrypted)) AS 'Decrypted Data'
FROM TestETLSource;
Notice how it doesn’t match? This is due to the nvarchar data type.
SELECT FakeData
,CONVERT(VARCHAR, DecryptByKey(FakeDataEncrypted)) AS 'Decrypted Data'
FROM TestETLSource;
Much better! Don’t forget to close the key when you’re done again.
-- Close the key now that we are not using it.
CLOSE SYMMETRIC KEY FakeKey;
GO
Now that we are done testing the key and encrypting the data, we can begin with our real test. Let’s create our stage and destination tables.
-- Create test stage table
CREATE TABLE TestETLStage (
Stageid INT PRIMARY KEY identity(1, 1)
,id INT
,FakeData VARCHAR(255)
,FakeDataEncrypted VARBINARY(128)
,StartTime DATETIME2(7)
,EndTime DATETIME2(7)
);
-- Create test destination table
CREATE TABLE TestETLDest (
Destid INT PRIMARY KEY identity(1, 1)
,Stageid INT
,id INT
,FakeData VARCHAR(255)
,FakeDataEncrypted VARBINARY(128)
,StartTime DATETIME2(7)
,EndTime DATETIME2(7)
);
Now we’ll take the record and move it into our staging table to perform a transformation on the data.
-- Insert records into ETL table
INSERT INTO TestETLStage (
id
,FakeData
,FakeDataEncrypted
,StartTime
,endtime
)
SELECT id
,fakedata
,FakeDataEncrypted
,starttime
,endtime
FROM dbo.TestETLSource;
Now we’ll perform an update on our stage data.
-- Perform ETL
UPDATE TestETLStage
SET EndTime = (
SELECT getdate()
);
Now we’ll take that data out of the stage table and move it over to our destination table.
-- Insert into Dest table
INSERT INTO TestETLDest (
Stageid
,id
,FakeData
,FakeDataEncrypted
,StartTime
,endtime
)
SELECT Stageid
,id
,fakedata
,FakeDataEncrypted
,starttime
,endtime
FROM dbo.TestETLStage;
Here’s where the rubber meets the road. Are our changes from the ETL table valid? Can we still unencrypt the correct values from the column now that we’ve changed tables and updated values in the rows?
OPEN SYMMETRIC KEY FakeKey DECRYPTION BY CERTIFICATE FakeCert
WITH password = 'some strong password';
SELECT FakeData
,CONVERT(VARCHAR, DecryptByKey(FakeDataEncrypted)) AS 'Decrypted Data'
,FakeDataEncrypted
,Destid
,Stageid
,id
,starttime
,endtime
FROM TestETLDest;
Great success!
-- Close the key now that we are not using it.
CLOSE SYMMETRIC KEY FakeKey;
GO
As my testing has shown, you can encrypt a column, move the data around different tables, and still decrypt it later. The encryption is on the column itself, not the table.
References used:
