How to decrypt stored procedures on multiple SQL databases

Problem

Before we show how to decrypt stored procedures, we need to give some background about this concept. When stored procedures are built in the SQL Server, their body text is reachable to all that have required permissions to access the data. Because of that, it’s simple to expose underlying content during created stored procedures and analyze that content through the SQL Server Management Studio, Windows PowerShell, or any commercial tool. As a result of data transparency, a SQL database can become vulnerable and compromised by any type of cyber-criminal activity. This is where encryption comes into place.

Encryption

One way to protect the data or intellectual property in a company or personal SQL database, from possible attacks, is to encrypt the stored procedures, views, functions or triggers. To apply encryption for mentioned SQL objects use the WITH ENCRYPTION option and it will disguise the data/content of desired procedures, views, functions or triggers from discovering. This is the example of the query that should be run into the SSMS to encrypt the stored procedure:

CREATE PROCEDURE dbo.TestEncrypt
WITH ENCRYPTION
AS
SELECT name
FROM dbo.test
GO

Decryption

The procedure to decrypt stored procedure is not that difficult. A privileged user who can access system tables, over the dedicated administration connection (DAC) to the SQL Server, can see database objects and decrypt them if needed using the SQL Server Management Studio, or any type of application that can connect to the server and is capable to retrieve the procedure.

The first method to decrypt stored procedure, view, function or trigger is to use a dedicated administration connection with sysadmin account. The connection can be established through SSMS using the prefix ADMIN on the desired server. For example ADMIN:SQLInstServer2019:

By default, the connection is only allowed from the SQL Server host, and if the remote admin connection is not enabled with the sp_configure, the error will occur similar to this “A network-related or instance-specific error occurred while establishing a connection to the SQL Server”.

If there is a difficulty to connect and use the dedicated administration connection please visit this article and follow the steps in it: SQL Server Dedicated Admin Connection (DAC) – how to enable, connect and use.

• Note: The error will be shown, and the SQL Server will be unreachable if the user is not using TCP/IP for dedicated admin connection (DAC) (which was explained in the article)

Simple decryption method can be described through few steps: obtain encrypted value, describing the stored procedure, from sys.sysobjvalues while connected via DAC, then, obtain the encrypted value of any blank procedure, and finally, get the statement for that blank procedure in the plaintext format and apply XOR operation between all three values.

The second and easier way to decrypt stored procedure, view, function or trigger from a desired SQL database is with the ApexSQL Decrypt, the SQL decryption tool that can integrate into SSMS or work as standalone application. The tool can be downloaded from this link, and the instructions on how to install and integrate the ApexSQL application into the SSMS or Visual Studio can be found here.

When the tool is installed and integrated into the SQL Server Management Studio, open the SSMS, navigate to the SQL database and procedure that needs to be decrypted, use the right click on the mouse and click the Decrypt object option:

When the process is done it will pop up the window in the SSMS where the message is shown like on the picture below:

Now, any user can read this object’s definition in plain text.

Both of the explained methods, executed via SSMS, are fine if only one object is decrypted at the time.

However, there is an advantage with the ApexSQL Decrypt where the tool is capable to decrypt stored procedures, views, functions and triggers on the multiple SQL databases at the same time. To do that start the ApexSQL Decrypt application as standalone and establish connection to the desired SQL Server. In this case, we are connecting to the SQL Server 2019:

Click the connect button and in the main grid all loaded SQL databases from that server will be shown:

To avoid showing already decrypted objects, on the right side in the main grid under the Object filter panel uncheck the Unencrypted option and expand the desired SQL databases. Select the encrypted objects with the checkbox next to their name:

• Note: If the there are no encrypted objects in the SQL database message will be shown “No objects found” under that SQL database:

  

After selection of SQL objects for decrypting is made, click from the Home tab the Decrypt button to start the Decryption wizard:

The next step in the Decryption wizard window is to choose the action which will create the decryption script or it will directly decrypt the objects in the selected SQL databases. For this example, we will apply decryption directly to the SQL databases and therefore choose the Decrypt now action. There are additional options to set the saving location for the decryption script and saving the backup of those SQL databases before the decryption is applied:

The last step is the Action plan where the list of SQL objects for decryption is shown and in which order will decryption be run against them. The list can also be sorted by Object type:

When everything is set, click the Decrypt button to start the process. When the process is completed the Results window will be shown. In this case, decryption is done successfully:

Conclusion

SQL Server database encryption is important for companies and users who want to protect the data from unwanted access. However, if there is a need to decrypt stored procedures, views, functions, or triggers on multiple SQL databases with a few steps, the ApexSQL Decrypt can be used which will finish the job in a couple of seconds or minutes depending on how large that SQL databases and encrypted objects are.