October 26, 2016 at 7:25 am
Hi folks,
So, I want to encryot some columns in cetain tables in a database.
I was thinking creating a table where I specify which tables and columns to be encrypted and then use the rows in this table to choose which data to encrypt.
Does that sound doable?
If, so maybe someone can push me in the right direction on how to do this?
October 26, 2016 at 7:35 am
Sorry Guys, you have to be faster 😉
I think I found the answer myself here: https://msdn.microsoft.com/en-us/library/ms179331.aspx
Sorry for disturbing...
October 26, 2016 at 7:36 am
mickegohle (10/26/2016)
Hi folks,So, I want to encryot some columns in cetain tables in a database.
I was thinking creating a table where I specify which tables and columns to be encrypted and then use the rows in this table to choose which data to encrypt.
Does that sound doable?
If, so maybe someone can push me in the right direction on how to do this?
Here is a quick example from an earlier thread
😎
USE TEEST;
GO
SET NOCOUNT ON;
--http://www.sqlservercentral.com/Forums/FindPost1820837.aspx
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply