September 3, 2008 at 6:51 pm
I am trying to encrypt my SSNum, taxid and email columns in the Account table and the name of the database is trading. The master key and certificate is created successfully but I have a test script that I ran that should return decrypted. However, all the values are null.
FIRST SCRIPT:
SET NOCOUNT ON;
USE trading;
GO
-- Create the database master key
IF NOT EXISTS (
SELECT 1
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'
) BEGIN
PRINT 'Create master key';
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'baryuy73r_!r-jmrru4huy&Yar=-btrE';
END;
GO
--drop CERTIFICATE TradingEncryptCert1
-- Create the database certificate
IF NOT EXISTS (
SELECT 1
FROM sys.certificates
WHERE name = N'TradingEncryptCert1'
) BEGIN
PRINT 'Create certificate';
CREATE CERTIFICATE TradingEncryptCert1 ENCRYPTION BY password = 'w@12sui&qd*456bef##99' WITH SUBJECT = N'TradingEncryptCert1';
END;
GO
-- Create the symmetric key
if exists (
SELECT 1 FROM sys.symmetric_keys
WHERE name = 'TradingEncryptKey1'
) BEGIN
PRINT 'DROP SYMMETRIC KEY';
DROP SYMMETRIC KEY TradingEncryptKey1;
END;
IF NOT EXISTS (
SELECT 1
FROM sys.symmetric_keys
WHERE name = 'TradingEncryptKey1'
) BEGIN
PRINT 'Create key';
CREATE SYMMETRIC KEY TradingEncryptKey1
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'w@12sui&qd*456bef##88' ;
END;
GO
SECOND SCRIPT or TEST SCRIPT:
use trading
go
OPEN SYMMETRIC KEY TradingEncryptKey1 DECRYPTION BY password = 'w@12sui&qd*456bef##88'
Select CONVERT(char(11),DecryptByKey (sid)) as SSNum ,
CONVERT(char(20),DecryptByKey (tid)) as taxId ,
CONVERT(char(100),DecryptByKey (enemail)) as email , *
From ACCOUNT Where ACCT = 'D00-0219 ' and substring(ACCT,4,1) = '-'
Please help I cannot figure out what I am doing wrong this was working up to 4 days ago.
Thanks
September 8, 2008 at 8:26 am
What is the data type of column "ACCT"?
Your problem may be with the comparison
ACCOUNT Where ACCT = 'D00-0219 ' and substring(ACCT,4,1) = '-'
Perhaps trying (for test purposes only) Where ACCT like 'D000-0219%' and removing the substring comparison (which is redundant as written
Also, try the select statement without any Encrption logic to be sure you are actually getting rows. Then add the Encrption logic.
September 9, 2008 at 9:45 am
What did the encryption script look like? Also did you think about using the other keys to encrypt your keys instead of a password for each. By encrypting say your certificate with the master key then you don't have to worry about keeping all the passwords safe.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply