July 18, 2014 at 4:24 am
Welsh Corgi (7/17/2014)
I recreated and opened the key. It decrypts from the same session but when I go to another session it list null.
Every time you create a key, you get a different one. So don't delete it except when you are intentionally replacing it, and even then proceed with caution - perhaps as described below.
If your stuff is encrypted and you lose the key, you can't decrypt it; if you are changing your key and/or your certificate make sure you have the new key, update by decrypting with the old key and encrypting with the new key (and then checking it's all ok) before dropping the old one. And again, add a column so you don't do it in place, then rename the columns so that the old one has a different name and the new one has the name the old one used to have, and check that everything still works; if it does, then you can delete the (renamed) old column and the old key and certificate.
Tom
July 18, 2014 at 6:13 am
Thank you Tom for all of your advise, very much appreciated.:-)
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 10:36 am
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.
[/
[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?
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 10:53 am
It was not but is now returning null values from SSMS. I did was change the encryption 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 18, 2014 at 5:54 pm
s soon as I open The key it fails in SSIS but it works inSSMS?
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 19, 2014 at 6:36 pm
Welsh Corgi (7/18/2014)
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.
[/
[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
July 19, 2014 at 6:42 pm
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
July 19, 2014 at 6:46 pm
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
July 19, 2014 at 7:18 pm
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.
[/
[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/
July 21, 2014 at 6:42 am
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:
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/
July 21, 2014 at 6:58 am
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
July 21, 2014 at 8:23 am
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:
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.
July 21, 2014 at 8:51 am
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/
July 21, 2014 at 8:54 am
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/
July 21, 2014 at 8:58 am
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/
Viewing 15 posts - 76 through 90 (of 94 total)
You must be logged in to reply to this topic. Login to reply