The following code is some I whipped up this morning to use a lookup table to then go and encrypt values in a table. This way the data team does not have to even look at the data reducing the risk of sensitive information leaking out. It is all still being worked on but I wanted to make sure I got it out here.
Table used for reference is simple and just contains the tablename and columnname.
create table encrypt_columns (Tablename varchar(50), columnname varchar(100))
Stored procedure created so the various SSIS packages that loads the original data to SQL does not have to be modified to perform the encryption other than to call this procedure. Note that this does change the underlying table structure, so on my project we may change the SSIS to create the table rather than truncate it.
Create procedure usp_encrypt_PII as
/*** Script to encrypt data loaded to SQL as plaintext and identified in the encrypt_columns table.
Created: 02/03/2012
by: CJB
Notes: Nested cursors that are data driven from values in dbo.encrypt_columns table.
***/
DECLARE @tablename NVARCHAR(100)
,@columnname NVARCHAR(100)
,@sqltxt NVARCHAR(500)
/* Open the keys to be used to encrypt the values */
OPEN SYMMETRIC KEY <keyname> DECRYPTION BY CERTIFICATE <Cert_Name>;
/* Go through each tablename in the encrypt columns table and
then encrypt the columns within it */
DECLARE cur_table CURSOR FAST_FORWARD READ_ONLY FOR
SELECT DISTINCT tablename
FROM encrypt_columns;
OPEN cur_table;
FETCH NEXT
FROM cur_table
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
/* Go through each column listed in the table */
DECLARE cur_column CURSOR FAST_FORWARD READ_ONLY FOR
SELECT DISTINCT columnname
FROM encrypt_columns
WHERE Tablename = @tablename
OPEN cur_column
FETCH NEXT FROM cur_column INTO @columnname
WHILE @@FETCH_STATUS = 0
BEGIN
/* prefix the original column with pre_*/
SET @sqltxt = N’exec sp_rename ”dbo.’ + @tablename + N’.[‘ + @columnname + ‘]”,”pre_’ + columnname + N”’,”COLUMN”’
EXEC sp_executesql @sqltxt
/* Add the varbinary column to hold the encrypted values */
SET @sqltxt = N’alter table ‘ + @tablename + N’ add [‘ + @columnname + N’] varbinary(100)’
EXEC sp_executesql @sqltxt
/* set the varbinary to the encrypted value of the ‘pre’ column */
SET @sqltxt = ‘update ‘ + @tablename + N’ set [‘ + @columnname + N’] = ENCRYPTBYKEY(KEY_GUID(”EBCDICKEY”),[pre_’ + @columnname + N’])’
EXEC sp_executesql @sqltxt
/* Once encrypted – remove the original unencrypted PII columns */
SET @sqltxt = ‘alter table ‘ + @tablename + N’ drop column [pre_’ + @columnname + ‘]’
EXEC sp_executesql @sqltxt
FETCH NEXT
FROM cur_column
INTO @columnname
END
CLOSE cur_column
DEALLOCATE cur_column
FETCH NEXT FROM cur_table INTO @tablename
END
CLOSE cur_table
DEALLOCATE cur_table