Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encrypt SSN Example (TDE)


Encrypt SSN Example (TDE)

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
Welsh Corgi (7/18/2014)
I'm having a weird problem.

I do not have it in one AS400 Library within SSIS but when I go to another I get an error that the column does not exist. The column exist.

[quote][/
[Execute SQL Task] Error: Executing the query "IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WH..." failed with the following error: "Invalid column name 'ENCR_TAX_ID_NUMBER'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
quote]

I execute the code in SSMS and no errors.


IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES257SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES257SecureSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END


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

UPDATE POINT_CYP.AGENT_SUPPORT
SET ENCR_TAX_ID_NUMBER = EncryptByKey(Key_GUID('AES257SecureSymmetricKey'), convert(varbinary(68), TAX_ID_NUMBER))
,TAX_ID_NUMBER = '**********';

CLOSE SYMMETRIC KEY AES257SecureSymmetricKey;

No comprede?




The failing code you've quoted doesn't show teh where clause. So I can't be crertain what is happenoing. But it looks as if you have managed to wrtie a where clause which refers to a column in the table POINT_CYP.AGENT_SUPPORT when the FROM clause refers only to the view sys.symmetric_keys. Perhaps a copy and paste accidentally from the wrong place?

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
Welsh Corgi (7/18/2014)
It was not but is now returning null values from SSMS. I did was change the encryption key.

If you change the key you can't decrypt information that was encrypted before the change. So when you try to you are likely to get null values instead of anything useful. You can only decrypt using the same key as you used to encrypt.

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
Welsh Corgi (7/18/2014)
s soon as I open The key it fails in SSIS but it works inSSMS?

You haven't said what fails in SSIS nor what works in SSMS. So we can't see why one works and the other fails. Of course SSIS does place restrictions on your T-SQL, depending on what transforms you are using, and I don't know enough about SSIS to be any help on that side anyway.

Tom

Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
TomThomson (7/19/2014)
Welsh Corgi (7/18/2014)
I'm having a weird problem.

I do not have it in one AS400 Library within SSIS but when I go to another I get an error that the column does not exist. The column exist.

[quote][/
[Execute SQL Task] Error: Executing the query "IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WH..." failed with the following error: "Invalid column name 'ENCR_TAX_ID_NUMBER'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
quote]

I execute the code in SSMS and no errors.


IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES257SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES257SecureSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END


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

UPDATE POINT_CYP.AGENT_SUPPORT
SET ENCR_TAX_ID_NUMBER = EncryptByKey(Key_GUID('AES257SecureSymmetricKey'), convert(varbinary(68), TAX_ID_NUMBER))
,TAX_ID_NUMBER = '**********';

CLOSE SYMMETRIC KEY AES257SecureSymmetricKey;

No comprede?




The failing code you've quoted doesn't show the where clause. So I can't be certain what is happening. But it looks as if you have managed to write a where clause which refers to a column in the table POINT_CYP.AGENT_SUPPORT when the FROM clause refers only to the view sys.symmetric_keys. Perhaps a copy and paste accidentally from the wrong place?


I should have provided more information.

Now I can't get it to work in SSMS. It encrypts but it does not decrypt. The Plain Text returns ******.


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;



The code that you provided still works.

I'm not giving you enough information.
Unsure

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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
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.

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/
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2868 Visits: 5351
I'm no expert in this, but I think if you drop the key in the first query, then you won't be able to decrypt anything encrypted with it, so the second query returns null.
I think someone may already have said this earlier in the thread.

Create the key separately once, then only open & close it in your queries.


Edit:
Sorry, different tables etc. in the queries so that might not be the cause, although the point still stands.

Can't see anything wrong in the query, what's it returning - NULLs, ******, error message?

Thanks
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37975
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
I got it to work. I went to on old version of the package and copied and pasted it into SSMS. Reloaded the table ran the script and it executed.

The packages have been re-deployed.


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
SET ENCR_TAX_ID_NUMBER = EncryptByKey(Key_GUID('AES256SecureSymmetricKey'), convert(varbinary(68), TAX_ID_NUMBER))
,TAX_ID_NUMBER = '**********';

CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;




Then I needed to do the same for the same table name in a different schema.

I was changing the name of the symmetrict key from AES256SecureSymmetricKey to AES257SecureSymmetricKey.

That did not work so I changed it back to AES256SecureSymmetricKey and it worked like a charm.

Thank for all of the help.

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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
Gazareth (7/21/2014)
I'm no expert in this, but I think if you drop the key in the first query, then you won't be able to decrypt anything encrypted with it, so the second query returns null.
I think someone may already have said this earlier in the thread.

Create the key separately once, then only open & close it in your queries.


Edit:
Sorry, different tables etc. in the queries so that might not be the cause, although the point still stands.

Can't see anything wrong in the query, what's it returning - NULLs, ******, error message?

Thanks


Thanks. I was not dropping the Key. I was only closing it and reopening it. :-)

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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
I should have mentioned that I was executing the commands on line at a time.
Blush

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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search