SQL2005 Encryption problem
I am getting null values when i decrypt the encrypted column in my table. As of now, i have no idea where i went wrong. Here is the sql statement i used:
if not exists (select * from sys.symmetric_keys where symmetric_key_id=101)
create master key encryption by password =abc123456&*(’
go
create certificate myportal
with subject =’Credit Card Certificate’
go
create symmetric key CommentKey
with algorithm = DES
encryption by certificate myportal
go
open symmetric key CommentKey
decryption by certificate myportal
go
update carddata
set creditcard= encryptbykey(key_guid(’CommentKey’),’creditcard’)
go
/* now it shows the data in the creditcard column is encrypted */
select * from carddata
/* to reveal the data in the creditcard column */
open symmetric key CommentKey
decryption by certificate myportal
go
select firstname, lastname, convert(char,decryptbykey(creditcard)) as creditopen
from carddata
————————————
When the last command is run, i only get null values in the encrypted column….
Filed under: Computing
Additional info:
When i created the certificate, i got this warning:
Warning: The certificate you created is not yet valid; its start date is in the future.
A further research reveals:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125262
Essentially it is a bug between comparing UTC and local time. This warning can be ignored and will be fixed in the next release.
Aha! The command:
set creditcard= encryptbykey(key_guid(’CommentKey’),’creditcard’)
There should be no quote characters in the creditcard keyword. It should read as follows:
set creditcard= encryptbykey(key_guid(’CommentKey’),creditcard)
Also, the return type of encrypt and decrypt is varchar(8000), the original field had only char(10), so i added an extra column called encrypted with varchar(8000) specified to hold the encrypted contents. The resulting commands:
use sales
if not exists (select * from sys.symmetric_keys where symmetric_key_id=101)
create master key encryption by password =’myportal.ph123!@#’
go
create certificate myportal
with subject =’Credit Card Certificate’
go
create symmetric key CommentKey
with algorithm = DES
encryption by certificate myportal
go
open symmetric key CommentKey
decryption by certificate myportal
go
update carddata
set encrypted= encryptbykey(key_guid(’CommentKey’),creditcard)
go
/* now it shows the data in the creditcard column is encrypted */
select * from carddata
/* to reveal the data in the creditcard column */
open symmetric key CommentKey
decryption by certificate myportal
select firstname, lastname, creditcard, convert(varchar,decryptbykey(encrypted)) as credit_decrypted
from carddata
go
Would yield the following correct output:
wilson chua abcdefg abcdefg
jane ang 123456789 123456789
first execute “use database ;” withdout the “” and den do the other commands again.