July 16, 2014 at 11:29 am
Lynn Pettis (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
I need to add to a Data Transformation task to Load to Staging.I need o update the existing record to the encrypted value so I need to add an addition column.
Can I use that simple Update statement to do a mass update?
Thanks for all of the help.
I appreciate it.:-)
Not a lot of information for sudden change in topics. Care to give a more detailed explanation as to what is needed?
This is as frustrating for you as it is for us. All you are providing are little snippets of information here and there. We ask for clarification of what you are trying to accomplish and we get another little snippet. Is it really that hard to provide us with the full picture of what it is you are trying to accomplish?
One, we are volunteers on this site and we want to help. Not just to help others but in fact to help ourselves if we are honest about it. What ever you are doing right now is something I don't have to worry about. We have PII information in our database, but the encryption occurs in the application not the database. So if I try to help you I am helping myself at the same time because I get to work through the same things you have to work through. Sounds like a win-win scenario to me.
Two, we don't see the things you see and we don't know what you know. We have to rely on you to provide us with the full picture of what you are working on, where your problem points are, what you have tried so far to solve the problem, that code you've written, the error messages you are getting. What ever will help us to see what you are dealing with.
Right now you say you have SSNs that need to be encrypted, great. Doesn't tell us a whole lot if you really look at it. Do you need to encrypt this in place (i.e. the same table in the same database), do you need to encrypt them as they are inserted into a new table in a new database?
Bottom line, please help us to help you.
Yes my ultimate objective is to have a encrypted column that replaces the existing un-encrypted column, same database and same table.
I hope you have a nice day.:-)
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/
July 16, 2014 at 11:31 am
Welsh Corgi (7/16/2014)
Lynn Pettis (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
I need to add to a Data Transformation task to Load to Staging.I need o update the existing record to the encrypted value so I need to add an addition column.
Can I use that simple Update statement to do a mass update?
Thanks for all of the help.
I appreciate it.:-)
Not a lot of information for sudden change in topics. Care to give a more detailed explanation as to what is needed?
...
I was trying to take it one step at a time.
Create the necessary objects.
Update existing Data
Move it into the ETL Task
Excuse me for not asking how to do every task at once.
Regards.
So, based on a 30,000 foot view of what you are doing I see this:
Encrypt data in place in a database.
Using SSIS transfer this encrypted data from the existing database to another database (potentially on another server).
Am I correct in this assessment?
You just added additional complexity to the process that you need to consider with regard to the encrypted data.
Do you actually transfer the encrypted data? If you do, what do you need to do on the target system to ensure that you can decrypt the data there if it needs to be displayed or reported on.
Do you decrypt the data on the source and encrypt it again on the target? What needs to be done here? Is my transmission from source to target encrypted, do I need to worry about the data being captured while being transferred?
Not sure if you have even taken into consideration key management. If you are using a Certificate to protect the symmetric key used to encrypt the PII, what do you do when the certificate expiries? Do you need to share this certificate with the other server?
And I am probably missing things here myself.
July 16, 2014 at 12:00 pm
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
Lynn Pettis (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
I need to add to a Data Transformation task to Load to Staging.I need o update the existing record to the encrypted value so I need to add an addition column.
Can I use that simple Update statement to do a mass update?
Thanks for all of the help.
I appreciate it.:-)
Not a lot of information for sudden change in topics. Care to give a more detailed explanation as to what is needed?
...
I was trying to take it one step at a time.
Create the necessary objects.
Update existing Data
Move it into the ETL Task
Excuse me for not asking how to do every task at once.
Regards.
So, based on a 30,000 foot view of what you are doing I see this:
Encrypt data in place in a database.
Using SSIS transfer this encrypted data from the existing database to another database (potentially on another server).
Am I correct in this assessment?
You just added additional complexity to the process that you need to consider with regard to the encrypted data.
Do you actually transfer the encrypted data? If you do, what do you need to do on the target system to ensure that you can decrypt the data there if it needs to be displayed or reported on.
Do you decrypt the data on the source and encrypt it again on the target? What needs to be done here? Is my transmission from source to target encrypted, do I need to worry about the data being captured while being transferred?
Not sure if you have even taken into consideration key management. If you are using a Certificate to protect the symmetric key used to encrypt the PII, what do you do when the certificate expiries? Do you need to share this certificate with the other server?
And I am probably missing things here myself.
I started out by asking a basic question about encryption.
I am Grateful for the help and I got my answer. Then I asked about the next steps.
If I offended you then let it go. Lets talk about how we can solve the problem as opposed to what information that is that I did not provide that is making you angry.
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/
July 16, 2014 at 12:12 pm
Welsh Corgi (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
Lynn Pettis (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
I need to add to a Data Transformation task to Load to Staging.I need o update the existing record to the encrypted value so I need to add an addition column.
Can I use that simple Update statement to do a mass update?
Thanks for all of the help.
I appreciate it.:-)
Not a lot of information for sudden change in topics. Care to give a more detailed explanation as to what is needed?
...
I was trying to take it one step at a time.
Create the necessary objects.
Update existing Data
Move it into the ETL Task
Excuse me for not asking how to do every task at once.
Regards.
So, based on a 30,000 foot view of what you are doing I see this:
Encrypt data in place in a database.
Using SSIS transfer this encrypted data from the existing database to another database (potentially on another server).
Am I correct in this assessment?
You just added additional complexity to the process that you need to consider with regard to the encrypted data.
Do you actually transfer the encrypted data? If you do, what do you need to do on the target system to ensure that you can decrypt the data there if it needs to be displayed or reported on.
Do you decrypt the data on the source and encrypt it again on the target? What needs to be done here? Is my transmission from source to target encrypted, do I need to worry about the data being captured while being transferred?
Not sure if you have even taken into consideration key management. If you are using a Certificate to protect the symmetric key used to encrypt the PII, what do you do when the certificate expiries? Do you need to share this certificate with the other server?
And I am probably missing things here myself.
I started out by asking a basic question about encryption.
I am Grateful for the help and I got my answer. Then I asked about the next steps.
If I offended you then let it go. Lets talk about how we can solve the problem as opposed to what information that is that I did not provide that is making you angry.
Not offended, not angry either. Frustrated, that I am. I want to help solve your problem (we all do) and to do it in a way that allows you to support what ever process is put in place. I ask questions to get a better idea of what it is you are trying to accomplish but to be honest, it feels like pulling hens teeth. Every answer is but a snippet, it doesn't fill in the full picture. Like a jigsaw puzzle with pieces missing. The frustrating part is you see those missing pieces but don't seem to understand I don't or maybe no one else out here who has been trying to help sees them either.
July 16, 2014 at 1:42 pm
Okay, I am going to have to walk away for awhile. Maybe its just the constant work with no days off, but I volunteered to come out here.
I'll try to see if I can find some additional articles or blog posts that may help but it may not happen tonight or tomorrow. I just need some distance and see if things may change while I work on other things for a bit.
July 16, 2014 at 1:50 pm
The encryption part stands alone. Once you have it working and understand it, then add in the other parts you need in your solution. This is the "divide and conquer" approach of problem-solving.
Encrypted data works like any other data, so you can do your update and be done with it. You can still manipulate your table like any other table, but you have to allow for the encryption if you copy it across different servers. That's the part about knowing the big picture.
What's next? If you want to do something in SSIS, I'm going to bow out quickly. I can't do much beyond spelling it, and I've gotten that wrong a couple of times. 😛
July 16, 2014 at 2:19 pm
Welsh Corgi (7/16/2014)
I started out by asking a basic question about encryption.
I am Grateful for the help and I got my answer. Then I asked about the next steps.
If I offended you then let it go. Lets talk about how we can solve the problem as opposed to what information that is that I did not provide that is making you angry.
To be honest, the initial question did set of few alarms. TDE main application is to protect data at rest, not the access to individual sensitive parts of information. Hence the response and the attention your question has drawn.
Like Ed Wagner pointed out and as you have seen in the previous posts, the technical part is straight forward, in a way similar to a type cast or data conversion.
Now I have a question, are the actual SSN used further down the line or is it enough to recognize the values? Reason for asking is simple, in terms of security, storing irreversible values is less of a burden and more secure, in other words, if you can decrypt it, someone else can!
😎
July 16, 2014 at 5:16 pm
ok, I'm sorry for causing any grief.
I have SSN's in a column in a table.
I want to encrypt those values.
That would be a simple update statement?
Thank you all for your help.
The help that I got with the sample code was great.
What do I need to do to update existing values to encrypted. I assume that I need to create a new column with a different data Type and then perform the update to that column.
Thank you for your help and patience.
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/
July 16, 2014 at 5:37 pm
Eirikur Eiriksson (7/16/2014)
Welsh Corgi (7/16/2014)
I started out by asking a basic question about encryption.
I am Grateful for the help and I got my answer. Then I asked about the next steps.
If I offended you then let it go. Lets talk about how we can solve the problem as opposed to what information that is that I did not provide that is making you angry.
To be honest, the initial question did set of few alarms. TDE main application is to protect data at rest, not the access to individual sensitive parts of information. Hence the response and the attention your question has drawn.
Like Ed Wagner pointed out and as you have seen in the previous posts, the technical part is straight forward, in a way similar to a type cast or data conversion.
Now I have a question, are the actual SSN used further down the line or is it enough to recognize the values? Reason for asking is simple, in terms of security, storing irreversible values is less of a burden and more secure, in other words, if you can decrypt it, someone else can!
😎
Again. Thank you to you and Tom and others that have helped.
My next step is to update unencrypted SSN's to encrypted SSN's.
I'm not going to encrypt in Staging just the final destination i.e. Data Warehouse.
That is all that I'm asking for.
I was hoping that my inquiry would not cause such an stir.: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/
July 16, 2014 at 6:24 pm
Ed Wagner (7/16/2014)
Welsh Corgi (7/16/2014)
Lynn Pettis (7/16/2014)
Welsh Corgi (7/16/2014)
I need to add to a Data Transformation task to Load to Staging.I need o update the existing record to the encrypted value so I need to add an addition column.
Can I use that simple Update statement to do a mass update?
Thanks for all of the help.
I appreciate it.:-)
Not a lot of information for sudden change in topics. Care to give a more detailed explanation as to what is needed?
I have SSN's in an existing table that I need to encrypt. So it amounts to an update of that data.
Do you need for me to provide addition details?
I don't think anyone needs additional details on an update statement, but that doesn't involve staging tables or data transformation tasks. A simple update statement will suffice.
Also, for what it's worth:
1. Don't forget about the big picture as brought up before.
2. Backup your SQL you used to create the certificates and keys.
3. Try to really understand how this stuff works before releasing it to production. After all, you're going to be the one supporting it, so you had better understand how it works. You also don't want to get into a situation where you've overwritten a column of SSNs and later find out you can't read them.
Just looking out for the gotchas down the road.
I just asked a question on how to update to encrypted values. I'm not looking for nothing but generalized responses.
I have a specific question...
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/
July 16, 2014 at 7:02 pm
Thank you so much for your help.
I appreciate the criticism.
🙂
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/
July 16, 2014 at 10:44 pm
This sample is kind of a walk through, add column, update with encrypted, mask the clear text etc..
😎
USE tempdb;
GO
CREATE TABLE dbo.Name_and_SSN
(Full_Name VARCHAR(50),
CLEAR_SSN VARCHAR(12));
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;
/* 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!';
/* UPDATE AND MASK */
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;
DROP TABLE dbo.Name_and_SSN;
Result #1
Full_Name CLEAR_SSN ENCR_SSN
------------------- ------------ ----------------------------------
Egor Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Frederic Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Helga Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Hermine Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Result #2
Full_Name Plaintext_SSN D_LEN
------------------ --------------- --
Egor Mcfuddle 999-01-1234 68
Frederic Mcfuddle 999-02-1234 68
Helga Mcfuddle 999-03-1234 68
Hermine Mcfuddle 999-04-1234 68
July 16, 2014 at 11:10 pm
Eirikur Eiriksson (7/16/2014)
This sample is kind of a walk through, add column, update with encrypted, mask the clear text etc..😎
USE tempdb;GO
CREATE TABLE dbo.Name_and_SSN
(Full_Name VARCHAR(50),
CLEAR_SSN VARCHAR(12));
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;
/* 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!';
/* UPDATE AND MASK */
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;
DROP TABLE dbo.Name_and_SSN;
Result #1
Full_Name CLEAR_SSN ENCR_SSN------------------- ------------ ----------------------------------
Egor Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Frederic Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Helga Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Hermine Mcfuddle ********** 0x00CFEE3749E6C84A85A516906A10....
Result #2
Full_Name Plaintext_SSN D_LEN------------------ --------------- --
Egor Mcfuddle 999-01-1234 68
Frederic Mcfuddle 999-02-1234 68
Helga Mcfuddle 999-03-1234 68
Hermine Mcfuddle 999-04-1234 68
You rock!
Thank you:-D
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/
July 17, 2014 at 8:24 am
That was a great example.
I have a little problem.
I'm going to load the data and encrypt it in an SSIS Package. Data Transformation followed by an Execute SQL Task to perform the encryption.
How do I decrypt the ENCR_SSN column?
I appreciate 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/
July 17, 2014 at 8:34 am
I recreated and opened the key. It decrypts from the same session but when I go to another session it list null.
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/
Viewing 15 posts - 46 through 60 (of 94 total)
You must be logged in to reply to this topic. Login to reply