December 26, 2007 at 9:06 am
I am trying to encrypt less than 110 chars of plaintext with TRIPLE_DES, but SQL gives me errors or autmatically truncates my ciphertext/plaintext to 30 plaintext characters.
My table structure is as follows:
create table MyTable
(
ID int not null identity(1,1) primary key clustered,
Tracking_Number varchar(19) not null,
secretdata varbinary(max)
);
This is how I am entering the data:
-- insert data directly into table
OPEN SYMMETRIC KEY skey_mycert DECRYPTION BY CERTIFICATE mycert;
INSERT INTO MyTable (
Tracking_Number,
secretdata
)
VALUES (
'12345678',
EncryptByKey(Key_GUID('skey_mycert'), 'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'));
CLOSE ALL SYMMETRIC KEYS;
The encryption is done via an INSTEAD OF INSERT trigger, which encrypts the plaintext, before storing it.
snippet
EncryptByKey(Key_GUID('skey_mycert'), [secretdata])
The 30 character result I get is:
select * from view_decrypted_MyTable
ID Tracking_Number secretdata
1 12345678 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
Does anyone have some suggestions?
Thanks.
December 31, 2007 at 5:24 pm
Of course, this may not be the right answer, but let's assume first that SQL Server is working ...
In Query Analyzer, choose Tools->Options and select the Results tab. Make sure the "Maximum Characters per column" value is more than your expected data ...
😀
Steve G.
P.S. Why does this text entry widget flag "SQL" as an unknown word??? 😛
January 17, 2008 at 9:45 pm
I had the same problem. when using the query
Select convert(varchar, decryptbykey(fieldname))
From view_decrypted_MyTable
I found the solution. Try this query
Select convert(varchar(200), decryptbykey(fieldname))
From view_decrypted_MyTable
This works
February 17, 2008 at 5:34 am
My issue (unsurprisingly) was that I had not allocated sufficient space to my data type.
I had fixed this 2 months ago - and should have posted a response then. Thanks for your own inpout though.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply