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 Saturday, July 19, 2014 6:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 7,814, Visits: 9,563
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
Post #1594346
Posted Saturday, July 19, 2014 6:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 7,814, Visits: 9,563
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
Post #1594347
Posted Saturday, July 19, 2014 6:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 7,814, Visits: 9,563
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
Post #1594348
Posted Saturday, July 19, 2014 7:18 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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.


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 #1594353
Posted Monday, July 21, 2014 6:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1594560
Posted Monday, July 21, 2014 6:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 2,021, Visits: 3,484
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
Post #1594570
Posted Monday, July 21, 2014 8:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 20,753, Visits: 32,569
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 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)
Post #1594614
Posted Monday, July 21, 2014 8:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1594632
Posted Monday, July 21, 2014 8:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1594634
Posted Monday, July 21, 2014 8:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
I should have mentioned that I was executing the commands on line at a time.


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 #1594637
« Prev Topic | Next Topic »

Add to briefcase «««678910»»

Permissions Expand / Collapse