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 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.
Note: Make sure you have .NET Framework 4.5.2 or higher installed.
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.
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.
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:
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:
If you click the Decryption Wizard option, this is what you will see:
Let me pick a File path and hit the Execute button to see what I get:
Ok, fair enough. That’s probably why the Decrypt in-place option was not enabled initially because that particular object isn’t encrypted.
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:
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:
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:
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!
Now, if I hit the Decrypt in-place option, then it will prompt me with the following:
If I hit No and attempt to go through the Decryption Wizard one more time, this is what I will see:
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:
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.
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:
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 ;)
Top comments (0)