Introduction

Sometimes you are trying to “hide” your secret sauce from the prying eyes of others. You have something truly genius (a.k.a. Intellectual Property) that you developed and you want to control the code? Encrypt it! Encrypt your code (encrypting data is a different topic). This blog is going to discuss how to encrypt your code in SQL Server, but more importantly how to decrypt it and how do it fast using a free software using ApexSQL Decrypt.

If you didn’t encrypt your code in stored procedures in your technical career, please don’t feel lonely and pressured to do so. You are not alone. Most if not all database professionals are not encrypting their code. On the contrary, they are sharing their code on places like github.com. Most of the time, you would see encrypted stored procedures in vendor databases – those would be the off-the-shelf on-premises software solutions, such as: Enterprise Resource Planning (ERP), Customer Relationship Manager (CRM), Warehouse and Learning platforms.

Problem

Just in case that you would need to encrypt your stored procedure – SQL Server provides an easy way to encrypt your code inside the stored procedure. All you need to is to add WITH ENCRYPTION keyword to the end of CREATE/ALTER PROCEDURE statement (Fig #1).

Fig #1

Once you ran an ALTER PROCEDURE statement with WITH ENCRYPTION, you would see a lock icon next to your stored procedure (Fig #2).

Fig #2

While adding an encryption is a truly no brainer, decryption is far from easy. As long as encrypted procedure is working, you are safe. The problem starts when it is not working. Consider a totally made up example below of a broken procedure, something that only happens in a run-time (Fig #3).

Fig #3

That encrypted stored procedure is no longer working and you need to see the code to try to fix it. While normally you would just right-click (connected via SSMS) the stored procedure and choose modify or create, this time you don’t have that option. While SSMS modify option is grayed out (Fig #4), choosing Script Stored Procedure as Create To (Fig #5) will not help you either (Fig #6).

Fig #4
Fig #5
Fig #6

So you know there is problem in the encrypted stored procedure, but you have no way to see the code and attempt to fix it.

Solution

While you can decode encrypted stored procedure using T-SQL is it a lot of effort – you would need a Dedicated Administrator Connection (DAC) , copy-and-paste things and run code with MD5 to decrypt it (read: way too much effort). Introducing a FREE ApexSQL Decrypt solution ( https://www.apexsql.com/sql-tools-decrypt.aspx) that decrypts stored procedure with a click of a button (I wish Microsoft would just bundle it SSMS).

All you need to do is download, install, connect to server/database and click on Decrypt button (Fig #7) to see the content of the stored procedure.

Fig #7

That is all it takes to decrypt the stored procedure and see what inside (see Fig #8).

Fig #8

Disclaimer

This blog post is partially based on the following resources:

https://www.sqlservercentral.com/blogs/a-guide-for-decrypting-sql-server-database-objects

https://www.sqlservercentral.com/blogs/simple-way-to-decrypt-sql-server-stored-procedure

https://www.mssqltips.com/sqlservertip/1046/decrypting-sql-server-database-objects/