August 18, 2015 at 11:29 am
We had a column encrypted. We need to find what's the algorithm used in it. Its not readable. we don't know how it's created. Is there any way we can find out more info on this? There's no certificate created. It's just a function.
August 18, 2015 at 11:41 am
After the fact, no, probably not.
If you have an app that's still inserting/updating the column with encrypted values, then a server-side trace may show you what function and key is being used. I say 'may', because if it's EncryptByPassPhrase, then the function will be masked in the trace to ensure you don't see a password.
Also, if it's been encrypted by the application and the encrypted value passed to SQL, then all you'll see in the trace is the encrypted value. In that case, chat with the application developers.
You've checked for certificates, what about symmetric and asymmetric keys?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2015 at 12:22 pm
GilaMonster (8/18/2015)
After the fact, no, probably not.If you have an app that's still inserting/updating the column with encrypted values, then a server-side trace may show you what function and key is being used. I say 'may', because if it's EncryptByPassPhrase, then the function will be masked in the trace to ensure you don't see a password.
Also, if it's been encrypted by the application and the encrypted value passed to SQL, then all you'll see in the trace is the encrypted value. In that case, chat with the application developers.
You've checked for certificates, what about symmetric and asymmetric keys?
yes, I did. No keys at db level.
August 18, 2015 at 1:26 pm
Then it's either EncryptByPassPhrase, which unless you get the password you won't be able to decrypt, or it's encrypted in the application and the encrypted data sent to SQL.
Go chat with the developers, ask them what they're doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply