EncryptByKey is randomly encrypting NULL

I have a large project to encrypt credit cards. We are using SQL column/cell encrypt. We have setup a DMK, Certificate, and then a SymmetricKey to encrypt the CCs. I am all coded and unit tested. Everything worked fine during testing. Now I am working on a different database getting ready for QA testing and I am running into a problem. My code is catching errors in the encrypted data. My code looks for encrypted NULLs.

I have clear CC numbers in a temp table #Temp_SNUpdate. I then join the TempTable to my permanent ods_snCard and update. I can run the Update and Select, without changing anything else, 15 times as fast as I can press F5 and every time there are 10-20 records that have an encrypted NULL in ods_snCard. They are always different record. There are no NULLS in #Temp_SNUpdate. The other 3000 or so CC numbers encrypt and decrypt correctly. Why is the EncryptByKey sometimes encrypting NULL?

update ods_snCard set SNDATA = EncryptByKey(Key_GUID('symmetrickey1'), u.SNDATA)
	from ods_snCard s inner join #Temp_SNUpdate u
	on s.SNTOKEN = u.SNTOKEN 
	where s.sntype in (13)


select cast(DecryptByKeyAutoCert(cert_id('certificate1'), null, [SNData]) as varchar) , * 
from ods_snCard where sntype in (13) and 
	cast(DecryptByKeyAutoCert(cert_id('certificate1'), null, [SNData]) as varchar) is null


March 26th, 2015 4:08pm

Found the answer. I had googled around, but found nothing. But after I posted I noticed a related post over on the left. He said that the issue was that the varbinary column used for the encrypted value was defined with ANSI_PADDING OFF, and guess what .... so was mine. We consolidated scripts to get ready for QA and some yeehaw (ok, my manager) had turned ANSI_PADDING off above my tsql. Got it sorted out now.

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 4:51pm

Don't blame your manager, but blame SSMS that insists on injecting SET ANSI_PADDING OFF at the end of table-creation scripts. I've been trying to tell Microsoft that this is bad, but with little success.

March 26th, 2015 6:23pm

Hi TheBrenda,

Glad to hear that the issue is resolved and thanks for your post to share your solution. That way, other community members could benefit from your sharing.

Regards,
Michelle Li

Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 3:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics