Using SQL Decryptor to Work With Encrypted SQL Server Objects

Here on the Diagram blog, we regularly look at the ins and outs of managing SQL Server and the tools that are available for systems administrators, including SQL Server Profiler and SQL Server Management Studio Activity Monitor. Today, we’re looking at a tool that admins can use to work with encrypted data.

From time to time when working with SQL Server, you may need to look at or edit encrypted stored-procedures, views, triggers, or functions that you do not have the source T-SQL for. This can sometimes throw developers and database administrators for a loop if they have not dealt with this in the past. Thankfully, there is a free and easy to use tool from Devart called dbForge SQL Decryptor that can help you deal with encrypted SQL Server objects.

SQL Encryption

For developers or companies that rely on SQL Server to support their applications, it is not unusual to put business and application logic into the database layer. One way to help protect this logic from prying eye or unwanted changes is to encrypt stored-procedures, views, triggers, or functions. This is done by using the WITH ENCRYPTION option when creating the object in SQL Server.

While encrypting SQL Server objects makes a lot of sense for application developers when they are building the application, it can be a hurdle for developers and database administrators (DBAs) that need to update or support the application in the field.

An example of this kind of issue happened recently at our company. The Diagram team was helping to move a client from the Ektron CMS to the Episerver CMS and needed to update and move some custom stored-procedures from the old CMS database to the new one. We had not originally developed the site for the client, and a few of the stored-procedures were encrypted by the original developers. When we tried to contact the company that had written the encrypted stored-procedures to work with them on updating and moving the stored-procedures to the new CMS, we found that they were no longer in business. Our only option was to decrypt the stored-procedures and do the work ourselves.

SQL Server’s built-in way to decrypt stored-procedures requires setting up a Dedicated Administrator Connection (DAC) to the SQL Server instance from SQL Server Management Studio on the server. You then need to extract the encrypted value of the procedure definition from sys.sysobjvalues over the DAC and follow a few steps to decrypt the stored-procedures definition. While this is not necessarily very hard to do, it can be a somewhat time consuming process.

SQL Decryptor

The SQL Decryptor tool removes the need for the DAC and provides a nice object explorer for easy browsing of the database, similar to SQL Server Management Studio. With SQL Decryptor, you can view a decrypted version of the object’s create statement, save a decrypted version of the object’s create statement to file, or decrypt the object in place. It also allows for batch decryption of objects and saving the output of the batch to file. To install the SQL Decryptor tool, download it and follow the instructions on the Devart site.

Once you have installed SQL Decryptor, decrypting an object like a stored-procedure is fast and simple. To get started, open SQL Decryptor and connect to the SQL Server instance that contains the database with the encrypted stored-procedures you want to decrypt. Then browse to the stored-procedure in question.

Next, right click on the encrypted stored-procedure and select ‘Show DDL script’ to view the T-SQL of the stored-procedure in plain text. In the right hand panel of SQL Decryptor, the T-SQL of the stored-procedure can be read, edited, or saved to file by going to the File menu and selecting ‘Save DDL to file…’.

If you need to decrypt a number of stored-procedures or any other encrypted objects, I recommend using the Decryption Wizard. To do this, go to the Action menu and select ‘Decryption Wizard…’. Once the wizard has opened, you can select all the objects you want to decrypt at once and what to do with the output of the wizard. You can have the T-SQL output go into a single file, create one file per object, or decrypt all the objects in place. After you have selected the options you want for your batch, click the Execute button.

With the T-SQL source for the encrypted objects you are working with, you can decide if you want to keep them encrypted or not. To remove the encryption from an object, remove the WITH ENCRYPTION statement for the T-SQL output, then alter or drop and recreate the object. I recommend reviewing the decision to remove any encryption with your development team or the vendor of the application before making this change.

Devart’s dbForge SQL Decryptor tool makes working with encrypted objects simple and easy. It allows you to see the T-SQL source of encrypted objects and provides well-formed scripts that can be used to recreate or move the object.

For more information about encrypting and decrypting SQL Server objects, I recommend reading this article by Derek Colley.

We hope this blog series on SQL Server has been helpful; if you have any questions or tips of your own to share, please feel free to use the form below. Please stay tuned for more blogs as we continue to look at the ins and outs of SQL Server.