July 16, 2014 at 8:18 am
Welsh Corgi (7/16/2014)
Tom, thank you very much for your help.If you were to execute the code that I posted you will find that it does not not store a value in the SSN Column.
Thanks again.
You have char(9) for the encrypted SSN column. I'm sure AES pads the to blocksize when the plaintext is short, so as you are using AES256 you need at least 256 bits - so the column needs to be declared as BINARY(32) or something larger (SSNs are short, so I thing BINARY(32) will be OK but maybe not; I used varbinary(128) in the examples, so perhaps it would be best to declare the column as VARBINARY(128)).
All encrypted columns have to be declared as BINARY or VARBINARY; BINARY is only valid if the length of the plaintext modulo the key length is teh same for every row in the table; I don't actually know whether Microsoft's T-SQL actually allows BINARY, it may require VARBINARY.
edit: I see Lynn already said mch the same while I was typing this reply.
Tom
July 16, 2014 at 8:22 am
I changed the Data Type.
I revised Tom's code for I had errors with the variable names that I used.
I can't get it to store the encrypted value.
CREATE TABLE Name_and_SSN
(Full_Name VARCHAR(50),
encodedSSN VARBinary (128))
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE SSN_Cert
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SSN_Cert
GO
OPEN SYMMETRIC KEY SSN_KEY DECRYPTION BY CERTIFICATE SSN_Cert;
DECLARE @Full_Name VARCHAR (50)
DECLARE @SSN CHAR (9)
SET @Full_Name = 'Egor Mcfuddle'
SET @SSN = '999-00-1234'
INSERT Name_and_SSN(full_name,encodedSSN)
values(@Full_name, EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn)));
--update an SSN by
--UPDATE Name_and_SSN
-- SET encodedSSN = EncryptByKey(Key_GUID('SSNKEY'), convert(varbinary(128), @ssn))
-- where full_name = @Full_name;
--read and decrypt an SSN by
SELECT CONVERT(varchar(128), DecryptByKey(SSN)) as Plaintext_SSN
FROM SSN_CERT where full_name = @Full_name;
Any help would be greatly appreciated.
Thanks everyone for your 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 16, 2014 at 8:44 am
Welsh Corgi (7/16/2014)
I can't get it to store the encrypted value.
I seem to have missed something out in the decryption. If you change
SELECT CONVERT(varchar(128), DecryptByKey(SSN)) as Plaintext_SSN
FROM SSN_CERT where full_name = @Full_name;
to
SELECT CONVERT(VARCHAR(128), DecryptByKey(Key_GUID('SSNKEY'),encodedSSN))
FROM SSN_CERT where full_name = @Full_name;
I think it will then find and decrypt the encrypted value.
Sorry for the mistake - just careless typing, and not checking it properly. I should remember to use copy and paste instead of retyping.
Tom
July 16, 2014 at 8:48 am
TomThomson (7/16/2014)
Welsh Corgi (7/16/2014)
I can't get it to store the encrypted value.I seem to have missed something out in the decryption. If you change
SELECT CONVERT(varchar(128), DecryptByKey(SSN)) as Plaintext_SSNFROM SSN_CERT where full_name = @Full_name;
to
SELECT CONVERT(VARCHAR(128), DecryptByKey(Key_GUID('SSNKEY'),encodedSSN))FROM SSN_CERT where full_name = @Full_name;
I think it will then find and decrypt the encrypted value.
Sorry for the mistake - just careless typing, and not checking it properly. I should remember to use copy and paste instead of retyping.
No your great thank you.
The code that I posted does not store a value in the SSN column?
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 9:15 am
There are some errors in your code, mainly table an column references (non existing table and/or column)
Here is an example using code I pointed to earlier
😎
USE tempdb;
GO
CREATE TABLE dbo.Name_and_SSN
(Full_Name VARCHAR(50),
encodedSSN VARBinary (128))
USE tempdb;
GO
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES128SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES128SecureSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES128SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
DECLARE @Full_Name VARCHAR (50)
DECLARE @SSN CHAR (12)
SET @Full_Name = 'Egor Mcfuddle'
SET @SSN = '999-00-1234'
INSERT Name_and_SSN(full_name,encodedSSN)
values(@Full_name, EncryptByKey(Key_GUID('AES128SecureSymmetricKey'), convert(varbinary(128), @ssn)));
SELECT * FROM dbo.Name_and_SSN;
SELECT
Full_Name
,CONVERT(varchar(128), DecryptByKey(encodedSSN)) as Plaintext_SSN
FROM dbo.Name_and_SSN where full_name = @Full_name;
CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
DROP SYMMETRIC KEY AES128SecureSymmetricKey;
DROP TABLE dbo.Name_and_SSN;
Setting the technical aspects of encryption and decryption aside, have you though about the other parts of the picture, Key management, Permissions etc.? Just a thought as the technical part is the easy part.
July 16, 2014 at 9:28 am
Thank you so much Sir.
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 9:37 am
TomThomson (7/16/2014)
Welsh Corgi (7/16/2014)
Tom, thank you very much for your help.If you were to execute the code that I posted you will find that it does not not store a value in the SSN Column.
Thanks again.
You have char(9) for the encrypted SSN column. I'm sure AES pads the to blocksize when the plaintext is short, so as you are using AES256 you need at least 256 bits - so the column needs to be declared as BINARY(32) or something larger (SSNs are short, so I thing BINARY(32) will be OK but maybe not; I used varbinary(128) in the examples, so perhaps it would be best to declare the column as VARBINARY(128)).
All encrypted columns have to be declared as BINARY or VARBINARY; BINARY is only valid if the length of the plaintext modulo the key length is teh same for every row in the table; I don't actually know whether Microsoft's T-SQL actually allows BINARY, it may require VARBINARY.
edit: I see Lynn already said mch the same while I was typing this reply.
Be careful on the column size her, a single character encrypted by AES256 can range from 48 to 52 bytes, in fact the column size controls the output, not the size of the value. With a single character value, the binary size jumps from 52 to 68 when the column size changes from varchar(7) to varchar(8). Ergo (belt and suspenders), always test the actual length using the DATALENGTH function.
😎
July 16, 2014 at 10:11 am
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.:-)
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:17 am
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?
July 16, 2014 at 10:52 am
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?
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:57 am
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.
July 16, 2014 at 11:08 am
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.
July 16, 2014 at 11:10 am
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.
No kidding.:laugh:
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:14 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.
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.
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:16 am
Thank you so much for your help. I appreciate 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/
Viewing 15 posts - 31 through 45 (of 94 total)
You must be logged in to reply to this topic. Login to reply