September 26, 2016 at 2:46 am
Greetings dear all,
I've a column in a table with type NVARCHAR(50), with values that now "people" decided to turn it in an encrypted value. The only way that I know in order to achieve that is using a VARBINARY column where store the encrypted value, so is there a way to get it done without adding a new column but just chenging the existing one with the values? :unsure:
Kindly,
Kaxtanhu.
September 26, 2016 at 3:22 am
Kaxtanhu (9/26/2016)
Greetings dear all,I've a column in a table with type NVARCHAR(50), with values that now "people" decided to turn it in an encrypted value. The only way that I know in order to achieve that is using a VARBINARY column where store the encrypted value, so is there a way to get it done without adding a new column but just chenging the existing one with the values? :unsure:
Kindly,
Kaxtanhu.
Depending on the actual length of the values in the column, you may have to change it to accommodate for the longer encrypted values.
😎
Here is a quick encryption - decryption example
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Name_and_SSN') IS NOT NULL DROP TABLE dbo.Name_and_SSN;
CREATE TABLE dbo.Name_and_SSN
(Full_Name VARCHAR(50),
CLEAR_SSN VARCHAR(150));
INSERT INTO dbo.Name_and_SSN (Full_Name,CLEAR_SSN)
VALUES
('Egor Mcfuddle' ,'999-01-1234')
,('Frederic Mcfuddle','999-02-1234')
,('Helga Mcfuddle' ,'999-03-1234')
,('Hermine Mcfuddle' ,'999-04-1234');
/* KEY STUFF */
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES256SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES256SecureSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
/* Check the encryption and data length */
SELECT
CONVERT(VARCHAR(150),EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(150), CLEAR_SSN)),1) AS ENC_SSN
,DATALENGTH(CONVERT(VARCHAR(150),EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(150), CLEAR_SSN)),1)) AS ENC_SSN_DL
FROM dbo.Name_and_SSN
/* UPDATE AND MASK */
UPDATE dbo.Name_and_SSN
SET CLEAR_SSN = CONVERT(VARCHAR(150),EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(150), CLEAR_SSN)),1);
SELECT
NS.Full_Name
,NS.CLEAR_SSN
FROM dbo.Name_and_SSN NS
SELECT
NS.Full_Name
,CONVERT(varchar(150), DecryptByKey(CONVERT(VARBINARY(150),NS.CLEAR_SSN,1))) as Plaintext_SSN
,DATALENGTH(NS.CLEAR_SSN)
FROM dbo.Name_and_SSN NS;
/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
--DROP SYMMETRIC KEY AES256SecureSymmetricKey;
--DROP TABLE dbo.Name_and_SSN;
Output
ENC_SSN ENC_SSN_DL
------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
0x0068CDDF2382754D93F5491909180C10010000008AD01862091A74198FCC5AAE042CCC2531BDFDDE787163385BAA057F79BDF7949039E7CBDC424CC9A3DD45FE9F1F2E06 138
0x0068CDDF2382754D93F5491909180C10010000009BD6E70CBD7D96B798FCA43A27E98538EEA2978040D69B5280FD1A457BAA9810B5A9C3B6D42B1B699AF5E85C4A9E2175 138
0x0068CDDF2382754D93F5491909180C1001000000F6A39CE357F8C7B0B1AC0F0837B2DB5CE43E5419D94D617E502D40F64F54A11FF479E6A7D82E794F221946D019788D34 138
0x0068CDDF2382754D93F5491909180C100100000059B3C0FB9CEC1B5E82AD1CBFA23647FA1BC89E59310DE19DACAD01FDBE34629534E78821D97F6569B1F27F7DDDD75029 138
Full_Name CLEAR_SSN
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
Egor Mcfuddle 0x0068CDDF2382754D93F5491909180C1001000000C398BE2F3D4884395C05C62C1CDDECB5B3DF72F7CFCA9B59FD08C702FDD035C81D94DAE41C40ED3C40FB0FC04892C082
Frederic Mcfuddle 0x0068CDDF2382754D93F5491909180C10010000009FD4FF61187FE4B3EDB28FBBEF43EA08B6B771BDAE2475B932A4F784BE12F468E690EA1250A748E7265BB6D98B67FC9B
Helga Mcfuddle 0x0068CDDF2382754D93F5491909180C1001000000683B4B3A9CCC308ED87B968817C5DBC777082D1F718D7FA9A2D0EFC0FA4A9098069B7FF3FC6E3361B8541DBE96523FF8
Hermine Mcfuddle 0x0068CDDF2382754D93F5491909180C1001000000EC54D03D80ABFD3E874B186C3C26A32549A2A7639D6454F24E6B75D4862341243B0A131BE88962609AF97AF9060D9C75
Full_Name Plaintext_SSN
-------------------- --------------- -----------
Egor Mcfuddle 999-01-1234 138
Frederic Mcfuddle 999-02-1234 138
Helga Mcfuddle 999-03-1234 138
Hermine Mcfuddle 999-04-1234 138
September 26, 2016 at 6:05 am
@Eirikur,
Many thanks!!!
It works for my Test DB so I'll study all scenarios of my production one and then apply it.
And as you said, I had to alter the column length.
Kindly,
Kaxtanhu.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply