Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2012 Forums  Transact-SQL (2012)  Is my data hosed?

Author  Topic  CLEE25 Starting Member 14 Posts Posted - 2013-07-31 : 15:47:00 Hoping someone out there might have had the same experience. I have an Asymmetric Key that I use in my SQL server to encrypt a field. I created the Key by running create master key encryption by password ='_just_for_demo' go create asymmetric key MyKey With Algorithm = RSA_1024 go For months, everything was working fine, data was encrypted, and I would run SELECT DecryptByAsymKey(AsymKey_ID('MyKey'),TP) as TP And get back the results (with TP being the encrypted field). Suddenly, for no reason I can discern, this is giving me a null value. There is no error, just a null value. The data is obviously still there, but there doesn't seem to be a way to decrypt it. Ie, the field still has 0x449BD27FEB9CEBB0FD01B77399E423733B78CF061D97942D05A297A000F2C307977DC12FA7AEEC339FAD84E2123JHE33B2D935F07A0C3D160E76DB95E9C2B8368190B28C5080BECEF3F28A227727335AD941EA55621E61FED51630CD^F8F5E87FD6E7385C687F715D0D2E2E3E8F7200870F94C5A3A4C022A587C8625E8DDD4722 Does anyone have any suggestions/thoughts on what I could do to resolve this? Step Tried I copied the DB, and then removed the Key, removed the master key, and then recreated it hoping perhaps that would work, but no joy. I tried removing the 'private key' by running alter asymmetric key. I tried converting the results to Varchar I am stumped and getting quite afraid that there is no way I can recover this data. Also, at this point I don't even care about keeping the encryption or the key, I just want to find a way to get the data out unencrypted, and then I can work on creating a new encryption method. Please help me SQL Team. Thanks for any help.