July 21, 2014 at 12:37 pm
Accidental post.
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 12:38 pm
Accidental post.
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 12:39 pm
Accidental post.
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 12:47 pm
Lynn Pettis (7/21/2014)
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.
Lynn,
Thanks for your input but I only had one column in two tables that needed to be encrypted ASAP. Pretty straight forward.
I had a simple task and that was know what it would take to get those two columns encrypted and decrypted.
I have it under control. No worries.
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 27, 2014 at 3:29 am
Lynn,
Thanks for all of the positive feedback.
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 5 posts - 91 through 94 (of 94 total)
You must be logged in to reply to this topic. Login to reply