I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.
In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.
This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.
Decrypting a Batch
The setup is in the previous post, and its the same here. Once that is complete, connect to SQL Compare and set up a project with the databases that have the encrypted procedures as the source. The destination doesn’t matter. You could pick any empty database.
Once that’s done, run the comparison. You ought to get results like this. Notice on this SQL 2017 instance, both procs are decrypted.
These do contain the WITH ENCRYPTION text, as this is an exact decryption. If I deploy this to another database, the procedures will be decrypted there.
Moving Procs Without Encryption
For many of us, if we were copying this procs, we want them decrypted. Fortunately, SQL Compare makes this easy. There are project options for this. Notice below I’ve searched for options related to encryption. I selected the “Decrypt encrypted objects” to allow SQL Compare to show the code above.
When I check the “Ignore WITH ENCRYPTION”, this will give me the code without that option. I check this and refresh my project and notice the code below. No WITH ENCRYPTION added.
I can now decrypt a lot of procedures in a batch, and do this as needed since if some are encrypted by a developer, likely you’ll find lots of random places as that developer tried to protect code in every database they touched./
I tested this on SQL Server 2017/2019/2022 and it worked. SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an eval today.
Voice of the DBA 
Isn’t decrypting anything in code supposed to be pain in the arse? It’s mean to obfuscate teh code so if it was easy to decrypt what would be the point of encryption. In fact I’m surprised to hear how SQL Compare can so easily decrypt these things. I’ve never had to use encryption on SQL but I was expecting the WITH ENCRYPTION feature to be something not so easily undone. Even if you’re the DBA you could be tinkering with a 3rd party DB and they want to keep hidden how their code works.
LikeLike
The idea was that this was supposed to protect code that was distributed to environments that you didn’t trust the sysadmin/dbo/etc. However, in practice, the way the encryption works, it wasn’t great. There are multiple algorithms out on the Internet that will do this. See the post I linked in this one where I did this manually. SQL Compare makes it easy.
The reality is that once you give the code to anyone, whether compiled C# or encrypted procs, people can decrypt or disassemble it.
LikeLike
Pingback: Decrypting Stored Procedures with SQL Compare – Curated SQL