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