Encrypt Char Column

Question

Post reply

Encrypt Char Column

sqlguy-549681

Ten Centuries

Points: 1187
More actions
May 31, 2009 at 7:06 am

#210214

Hi all
I have a simple qusetion ,can I encrypt and decrypt char column ?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

Yes u can encrypt/decrypt character column. But how are you planning to do that?

sqlguy-549681 Ten Centuries Points: 1187 More actions · Answer 2

I using Symmetric Key Encryption Option as following

1.Create DMK

2.Create Certificate

3.Create Symmetric Key

And then use simple update Syntax as following

OPEN SYMMETRIC KEY TestTableKey DECRYPTION

BY CERTIFICATE EncryptTestCert

UPDATE TestTable

SET Mycolumn = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),Mycolumn)

GO

And Decrypt by following script

OPEN SYMMETRIC KEY TestTableKey DECRYPTION

BY CERTIFICATE EncryptTestCert

SELECT DECRYPTBYKEY(EncryptSecondCol) AS DecryptSecondCol

FROM TestTable

GO

First problem, Decryption by using above syntax get NULL value!!!!!

Second problem, encryption not display as normal format when column is VARBINARY

Just a clarify my column data type is Char(19)

Thanks in advance

Lowell SSC Guru Points: 323518 More actions · Answer 3

you'll need to change your encrypted column to a varbinary. I believe the rule is if you are doing a 19 char field, the size for the varbinary is twice the size, so it must be a varbinary(38)( or larger)

the function ENCRYPTBYKEY returns a varbinary with a maximum size of 8,000 bytes.

you are getting weird data and truncation when you decrypt the field; i'd guess NULL is the default value when you the value in the char(19) field does not translate correctly; also not ethe decrypt function expected varbinary input, but implicit conversion from text to varbinary must not raise an error.

http://msdn.microsoft.com/en-us/library/ms174361.aspx

Return Types
varbinary with a maximum size of 8,000 bytes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!