Checking out dbForge’s Decryptor for SQL Server
Tags: devart, sqlserver, database, security
Published: 2022-06-15 15:33:54

There’s no mystery about the fact that Microsoft takes security very seriously, and SQL Server provides several different features to protect the databases of end-users. Now, just by hearing the name “Decryptor for SQL Server” it gives the impression that somehow it must be related to a security feature, so stick around to discover what Decryptor for SQL Server can do for the professional SQL Server database administrator.

**Installation**
You can get dbForge [SQL Decryptor](https://www.devart.com/dbforge/sql/sqldecryptor/download.html) by clicking the _**Get For Free**_ button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e20wcd49ynz4f200hywy.png) 
 
_**Note:** Make sure you have .NET Framework 4.5.2 or higher installed._

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s473vr584yeqhi570a8u.png)  

I will be showing the standalone version of the tool, so feel free to follow along either using the same version or through the SQL Server Management Studio extension.

After opening the application for the first time, it will immediately detect the SQL Server instance in your server, so pick one and fill in the connection string parameters to move on. You don’t need to tick the _Dedicated Administrator Connection mode_, I’m just showing that the option is there in case you ever need to use it.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gmfmsk405quw8zml3vda.png)    

Once you pick your SQL Server instance, you will see something like this:

It looks a lot like SQL Server Management Studio, so I have expanded all my user databases to show you that you will see the same directory layout in all of them: Procedures, Functions, Views, DML Triggers, and DDL Triggers.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/chchu69uuishv380x54n.png)  

To see what options the tool provides, let me pick a random _Views_ directory, expand it, and see what it does after right-clicking any of the existing objects:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4r05pdtfh9wwuim1xy1g.png)   

The _Connect_ and _Disconnect_ ones are pretty much obvious, so I’m going straight for the other ones. This is what you get after clicking **Show DDL script**:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n4ogcmhauswjdhkj60f6.png)  

If you click the _**Decryption Wizard**_ option, this is what you will see:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igzqc0t9o8sdzvly49ng.png)  

Let me pick a _**File path**_ and hit the _**Execute**_ button to see what I get:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h411eqrel444u6znrxc7.png)  

Ok, fair enough. That’s probably why the _**Decrypt in-place**_ option was not enabled initially because that particular object isn’t encrypted.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvjsxq483dr4qiwwq7qa.png)  

With that said, let me take a step back and prepare a better example. So I’m quickly going to switch to SQL Server Management Studio to alter the definition of the view I tried in my previous attempt, and will _**add encryption**_ to it:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a3q1kr4ihx43urgb741d.png)  

Let me take a pause here to expand a bit on this WITH ENCRYPTION option. It turns out that right after altering the definition of the view and adding the encryption option, I can no longer see its definition, even if my user is the owner and I have sysadmin privilege granted:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z34qqjcc1gf6nb26kzd0.png)  

By the way, it doesn’t matter if you try to get the definition of the view through the help of the sp_helptext Stored Procedure, because you will reach the same dead-end:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g96bmczm1qiinjwxpbzi.png)  

Now, to revert this, you need to have the source code of the ALTER VIEW command for the specific view that you want to modify, but what if you don’t? Well, without a backup that contains the original definition of the view, you are pretty much fried. But rest assured, because that’s precisely where SQL Decryptor will shine, so let me go to the tool again and re-attempt my previously failed demonstration:

As you can see, not only the _**Decrypt in-place**_ option is enabled, but I can also see the definition of the view that I couldn’t back in SQL Server Management Studio, cool!
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4eh0b0rp3bnyhz0sqkfg.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ewiqur9rtyd623sal196.png)  

Now, if I hit the _Decrypt in-place_ option, then it will prompt me with the following:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x4itvrqy0oxn2erpruge.png)  

If I hit _No_ and attempt to go through the _Decryption Wizard_ one more time, this is what I will see:
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a2zu960qkvihub9pqi4r.png)  

The cool thing about the Decryption Wizard is that you can either generate the DDL script of the objects that you want to recover or revert the encryption right in the database:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1pfghhs8kos2aao7bwg8.png)
    
For simplicity, I’m going to pick the _Decrypt in-place (alter objects)_ option so that the object is modified directly in my database:

As you can see, the tool will put a green round-check icon next to the object that has been successfully altered.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1g84v2jp2eodimuoe7ul.png)  

If I go back to SQL Server Management Studio, I can retrieve the definition of the view once again, either through the GUI or through the sp_helptext Stored Procedure:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4r90s8cn210zkemfotf5.png)  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8gvh5glkqlhmbmk2m6yr.png) 

The same behavior/functionality will be provided for Stored Procedures, Functions, and Triggers, which is very cool!

This was a rough introduction to the tool, but I think that if you reached this point, you now have a very good idea of the specific problem that it solves for you.

**Final thoughts**
●	I can already see a big use case for this tool: imagine that you are a new database administrator that has just joined the team and has inherited a set of SQL Server instances to provide support to. Now, a DBA that previously was in the team had the magnificent idea to encrypt many database objects to hide their definition even from the DBAs with the highest possible privileges; but guess what, there’s currently no available database backup where the original definition of those objects can be found, so you cannot even give proper maintenance to those hidden structures. Luckily for you, Decryptor for SQL Server can save the day with a few clicks to avoid the completion of the perfect recipe for a wonderful disaster.

●	Using the WITH ENCRYPTION hint within the definition of your database objects can make all the sense in the world, but you have to be very careful on how you manage things within your environment because if not done properly it’s like locking the door of your home, walking out and leaving the keys inside… but Decryptor for SQL Server is that spare key under the carpet to save the day ;)