April 10, 2009 at 4:25 pm
I cannot get the following basic code that does data encryption/decryption to work:
USE encryption_test
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'abcd'
go
CREATE CERTIFICATE
ct_test
WITH
SUBJECT = 'Test Certificate'
GO
CREATE SYMMETRIC KEY
sk_test
WITH
ALGORITHM = DES --AES_256
ENCRYPTION BY
CERTIFICATE ct_test
GO
create table tblTest
(
amount money not null,
card_number varbinary(1000)
)
open symmetric key
sk_test
decryption by
certificate ct_test
insert into tblTest
(amount, card_number)
select
100.00,
EncryptByKey(key_guid('sk_test'), '1234-5678-9012-3456')
close symmetric key sk_test;
-----------
--decrypt data
select
amount
,CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('ct_test') , NULL ,card_number)) as 'Decrypted Card Number'
from
tblTest;
Here is what I get when I try to decrypt the data:
amount Decrypted Card Number
--------------------- ------------------------------
100.00 ㈱㐳㔭㜶ⴸ〹㈱㌭㔴6
(1 row(s) affected)
I get the same result when I try the following:
open symmetric key
sk_test
decryption by certificate
ct_test;
select
amount
,CONVERT(nvarchar, DecryptByKey(card_number)) as 'Decrypted Card Number'
from
tblTest;
What am I missing? Any ideas anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 10, 2009 at 6:57 pm
I found the answer! Instead of converting the decrypted result to NVARCHAR, I should be converting it to VARCHAR; since I enter it as VARCHAR into the table in the first place!
In other words, I should be doing:
select
amount
,CONVERT(VARCHAR, DecryptByKeyAutoCert ( cert_ID('ct_test') , NULL ,card_number)) as 'Decrypted Card Number'
from
tblTest;
This has been driving me crazy for a while... Had no idea VARCHAR vs. NVARCHAR would affect the result like this, but, come to think of it, it now makes sense. These are 2 different character sets and should be affected by incryption differently.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply