Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910

Encrypt SSN Example (TDE) Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 12:37 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
Accidental post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1594772
Posted Monday, July 21, 2014 12:38 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
Accidental post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1594773
Posted Monday, July 21, 2014 12:39 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
Accidental post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1594774
Posted Monday, July 21, 2014 12:47 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
Lynn Pettis (7/21/2014)
Welsh Corgi (7/21/2014)
Hi Tom the script that provided me still works.

USE tempdb;
GO


-- TRUNCATE TABLE dbo.Name_and_SSN

-- DROP TABLE dbo.Name_and_SSN


CREATE TABLE dbo.Name_and_SSN
(Full_Name VARCHAR(50),
CLEAR_SSN VARCHAR(12));

--point_ctl.agent_support.tax_id_number
--and point_cyp same table

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');

/* ADD COLUMN */
--ALTER TABLE dbo.Name_and_SSN ADD ENCR_SSN VARBINARY(68) NULL;


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

OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';

UPDATE dbo.Name_and_SSN
SET ENCR_SSN = EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(68), CLEAR_SSN))
,CLEAR_SSN = '**********';


SELECT
NS.Full_Name
,NS.CLEAR_SSN
,NS.ENCR_SSN
FROM dbo.Name_and_SSN NS


SELECT
NS.Full_Name
,CONVERT(varchar(128), DecryptByKey(NS.ENCR_SSN)) as Plaintext_SSN
,DATALENGTH(NS.ENCR_SSN) AS D_LEN
FROM dbo.Name_and_SSN NS;

/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
DROP SYMMETRIC KEY AES256SecureSymmetricKey;


The following script was working but now it does not work:

[code="sql"]

SELECT *
INTO POINT_CTL.AGENT_SUPPORT_ENCRYPT
FROM POINT_CTL.AGENT_SUPPORT

ALTER TABLE POINT_CTL.AGENT_SUPPORT_ENCRYPT ADD ENCR_TAX_ID_NUMBER VARBINARY(68) NULL;


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


OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';

UPDATE POINT_CTL.AGENT_SUPPORT_ENCRYPT
SET ENCR_TAX_ID_NUMBER = EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(68), TAX_ID_NUMBER))
,TAX_ID_NUMBER = '**********';



SELECT
PRIMARY_ACCOUNT_NAME
,TAX_ID_NUMBER
,ENCR_TAX_ID_NUMBER
FROM POINT_CTL.AGENT_SUPPORT_ENCRYPT

SELECT
PRIMARY_ACCOUNT_NAME
,CONVERT(varchar(128), DecryptByKey(ENCR_TAX_ID_NUMBER)) as Plaintext_TAX_ID_NUMBER
,DATALENGTH(ENCR_TAX_ID_NUMBER) AS D_LEN
FROM POINT_CTL.AGENT_SUPPORT_ENCRYPT;

/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
DROP SYMMETRIC KEY AES256SecureSymmetricKey;

Do you see anything that would make the second query not work?

Thanks.




The code that is working for you is sandbox code. It is nothing more than a demonstration of how to accomplish the task. After you encrypt your data with the symmetric key, if you drop the key you lose the ability to decrypt the data that you encrypted.

This is why we brought up topics like key management. The actual encrypting of the data is easy part. Managing the keys, who has access to them, where they are used, how you protect them including backing them up in case of server failure are the hard part of the process. One of the reasons I asked about the whole process, what you are attempting to accomplish. Still really not sure what you are trying other than a very vague 50,000 foot view.

You need to plan out exactly what you are trying to accomplish. The process of what will be done. How are you going to manage the keys. Are you going to handle changing the keys, if that is requirement over time. This isn't something to approach lightly.


Lynn,

Thanks for your input but I only had one column in two tables that needed to be encrypted ASAP. Pretty straight forward.

I had a simple task and that was know what it would take to get those two columns encrypted and decrypted.

I have it under control. No worries.

Thanks.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1594778
Posted Sunday, July 27, 2014 3:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:44 PM
Points: 4,232, Visits: 4,284
Lynn,

Thanks for all of the positive feedback.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1596589
« Prev Topic | Next Topic »

Add to briefcase «««678910

Permissions Expand / Collapse