January 14, 2011 at 10:40 am
I am having an interesting issue with encrypting data in SQL 2005. In process of loading the data up to a SQL 2008 server to see if it also has the same issue.
The problem I am having is when I run a query to encrypt a large amount of records at once that some of the rows wind up with invalid encrypted data that when decrypted comes back NULL.
Is there some known issue with doing an update to many rows that the encryption randomly fails? I say randomly as I've tried this over and over and out of about 3k records about 0.1% to 0.6% fail to encrypt properly. And not the same records every time.
Below are details and some dumbed down code to show the basics of what I am doing. The table has a lot more data that given and the rows already exist (sample doesn't show any sample data).
I don't receive any errors during encryption... but when decrypting the value it returns is just NULL in some cases even though the original value was not null.
Server Details:
SQL Server 2005 Service Pack 2, 32bit
Setup:
create master key encryption by password = 'password'
GO
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Sample Certificate';
GO
grant control, alter on CERTIFICATE::MyCertificate to MyGroup
GO
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCertificate;
grant control, alter on SYMMETRIC KEY::MySymmetricKey to MyGroup
GO
CREATE TABLE MyTable (
temp_id INTEGER IDENTITY(1,1) NOT NULL,
...
Orig_data CHAR(25) NULL,
encrypt_data varbinary(200) NULL,
...
)
GO
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate ;
UPDATE MyTable
SET encrypt_data = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), Orig_data, 1,
CONVERT(VARBINARY, temp_id))
WHERE Orig_data IS NOT NULL ;
-- Locate those that did not encrypt properly
SELECT Orig_data,
CONVERT(CHAR(25), DecryptByKey(encrypt_data, 1,
CONVERT(VARBINARY, temp_id)))
FROM MyTable
WHERE encrypt_data IS NOT NULL
AND ISNULL(Orig_data, 'XXX') <> ISNULL(CONVERT(CHAR(25), DecryptByKey(encrypt_data,
1,
CONVERT(VARBINARY, temp_id))),
'XXX')
CLOSE SYMMETRIC KEY MySymmetricKey;
GO
January 14, 2011 at 12:38 pm
And I have reproduced on a SQL 2008 R2 64bit server as well. Exact same issue.
January 17, 2011 at 9:20 am
Ok I finally resolved my issue. The problem was that the original table's varbinary column was created while "SET ANSI_PADDING" was turned OFF. This caused the value that was being saved for the encrypted data to not save correctly when there were trailing zeros, then the decryption process did not accept the invalid value and return NULL etc.
January 17, 2011 at 9:22 am
Thanks for replying and letting us know the root cause.
I actually just ran into a similar issue and this fixed it for me 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply