July 17, 2014 at 8:53 am
So the scope of the key is limited to a session.
That is why it does not work.
If anyone knows what I need to differently so decrypt from another session it would be greatly appreciated.
I'm still looking.:-)
Thank you.
Edit: I found this link but I'm looking for a more straight forward explanation:
http://blogs.msdn.com/b/sqlsecurity/archive/2007/11/29/open-symmetric-key-scope-in-sql-server.aspx">
http://blogs.msdn.com/b/sqlsecurity/archive/2007/11/29/open-symmetric-key-scope-in-sql-server.aspx
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 9:42 am
You said in an earlier post that you re-created the key. How did you do that? If you dropped the key then I suspect that is why you can not decrypt the column. Can you try again with your test but don't drop the key? You can close it and open it though.
July 17, 2014 at 9:47 am
tripleAxe (7/17/2014)
You said in an earlier post that you re-created the key. How did you do that? If you dropped the key then I suspect that is why you can not decrypt the column. Can you try again with your test but don't drop the key? You can close it and open it though.
I have tried without not dropping the key. There is an issue scope key.
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 10:14 am
Welsh Corgi (7/17/2014)
tripleAxe (7/17/2014)
You said in an earlier post that you re-created the key. How did you do that? If you dropped the key then I suspect that is why you can not decrypt the column. Can you try again with your test but don't drop the key? You can close it and open it though.I have tried without not dropping the key. There is an issue scope key.
Before people start spinning wheels please post the following:
1. The steps you followed (all the code in order) to get it to work.
2. The steps you followed subsequently where it failed.
Note: Leave nothing out.
July 17, 2014 at 10:18 am
I did not open the Key in the new window.
That was the problem.
Thanks for all 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 10:32 am
Welsh Corgi (7/17/2014)
I did not open the Key in the new window.That was the problem.
Thanks for all the help.
Oooops! Hate to be the one to tell you but this is the wrong answer! POST THE CODE AND INDICADE WHERE IT FAILS!
😎
July 17, 2014 at 11:00 am
Eirikur Eiriksson (7/17/2014)
Welsh Corgi (7/17/2014)
I did not open the Key in the new window.That was the problem.
Thanks for all the help.
Oooops! Hate to be the one to tell you but this is the wrong answer! POST THE CODE AND INDICADE WHERE IT FAILS!
😎
ok, sorry about that.
I did not Execute the following in the new window before I executed the SELECT Statement.
OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
This is the Select Statement from the code that you provided me:
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;
Is this enough information?
Thanks again.
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 12:26 pm
Welsh Corgi (7/17/2014)
Eirikur Eiriksson (7/17/2014)
Welsh Corgi (7/17/2014)
I did not open the Key in the new window.That was the problem.
Thanks for all the help.
Oooops! Hate to be the one to tell you but this is the wrong answer! POST THE CODE AND INDICADE WHERE IT FAILS!
😎
ok, sorry about that.
I did not Execute the following in the new window before I executed the SELECT Statement.
OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
This is the Select Statement from the code that you provided me:
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;
Is this enough information?
Thanks again.
Good stuff, now lets work out the process;
😎
1. create the key, do not drop it, back it up and carve it in stone in a safe place!!!
2. before any operation, open the key
3. do normal dml operations using the DecryptByKey.
4. when finished (by the end of the batch), close the key.
Here is an example, first run the previous example but make certain that all the drop statements are commented out!
USE tempdb;
GO
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
SELECT
NS.Full_Name
,CONVERT(varchar(128), DecryptByKey(NS.ENCR_SSN)) as Plaintext_SSN
,DATALENGTH(NS.ENCR_SSN)
FROM dbo.Name_and_SSN NS;
/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
This works like a charm;-)
July 17, 2014 at 12:32 pm
I'm having bad luck.
The first time I execute the following code I get the decrypted 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;
The second time I execute it with or without the Update I get the following:
Full_NamePlaintext_SSND_LEN
Egor Mcfuddle**********68
Frederic Mcfuddle**********68
Helga Mcfuddle**********68
Hermine Mcfuddle**********68
I have to drop the table for it to work.
Very strange.
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 12:49 pm
Now we do a step by step
😎
run this
USE tempdb;
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'Name_and_SSN'
AND TABLE_SCHEMA = N'dbo')
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 */
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = N'ENCR_SSN'
AND TABLE_NAME = N'Name_and_SSN'
AND TABLE_SCHEMA = N'dbo')
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)
FROM dbo.Name_and_SSN NS;
/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
Then open up another session and run this
USE tempdb;
GO
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES256SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
SELECT
NS.Full_Name
,CONVERT(varchar(128), DecryptByKey(NS.ENCR_SSN)) as Plaintext_SSN
,DATALENGTH(NS.ENCR_SSN)
FROM dbo.Name_and_SSN NS;
/* CLEAN UP */
CLOSE SYMMETRIC KEY AES256SecureSymmetricKey;
July 17, 2014 at 1:08 pm
Why is it necessary to open a new session?
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 1:16 pm
Welsh Corgi (7/17/2014)
To make certain it works across sessions, logins etc.. The solution is no good if it doesn't
😎
Just a word of advice, read all the comments and posts posted so far and raise questions on any issue where in doubt, we certainly do not want to find you in the situation where you have encrypted all the values, overwritten the clear text and not being able to decrypt/read the values!:exclamation:
July 17, 2014 at 1:43 pm
Thanks for the advise.
I will do so.
In this case I'm Truncating and re-loading the Data from an AS400 nightly.
Thanks again. 🙂
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 18, 2014 at 4:06 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?
Yes.
Tom
July 18, 2014 at 4:12 am
Ed Wagner (7/16/2014)
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. 😛
But the one thing to remember is not to encrypt in place: if you have teh unencrypted SSNs, the first step is to add a column for the encrypted SSNs. Next put the encrypted SSNs in the new column without changing the old column, then check that everything works using the encrypted column, and when it does all work (and not before) delete the old column.
Tom
Viewing 15 posts - 61 through 75 (of 94 total)
You must be logged in to reply to this topic. Login to reply