Click here to Skip to main content
15,867,453 members
Articles / Security / Encryption

Decrypt SQL Server Objects

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
7 Dec 2016CPOL7 min read 33.9K   435   9   4
Decrypt Stored Procedures, Views, Functions, and Triggers in SQL Server

This article appears in the Third Party Products and Tools section. Articles in this section are for the members only and must not be used to promote or advertise products in any way, shape or form. Please report any spam or advertising.

Introduction

There is a fast and simple approach to decrypting objects in SQL Server as an alternative to performing this task programmatically. The Optillect SQL Decryptor tool is a free tool which can be used for such a purpose, and allows Functions, Triggers and Views to be decrypted from the same database.

Background

When working on legacy databases, there may be occasions when a developer comes across encrypted objects which cannot be viewed or modified. This can be a problem when debugging or reverse engineering SQL Server objects. The purpose of this article is to demonstrate a quick way to handle encrypted objects in SQL server using the Optillect SQL Decryptor tool.

The tool supports the following versions:

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2011

The Process

The approach taken to demonstrate the process involves creating a schema, followed by creating an encrypted stored procedure which calls an encrypted view. The two objects will be decrypted using Optillect, then altered in SSMS, and the changes applied to the database.

Create the Database Schema

The script for creating this schema and the associated SQL Server objects used in this article can be downloaded from this zip file.

Database Schema

The table relationship is as follows:

  • A User can belong to more than one Group
  • A Group can have more than one User
  • A Group can have more than one Permission
  • A Permission can be associated with more than one Group

In simple terms, there is a many-to-many relationship between a User and a Group, which is implemented here using a linking table named UserGroup. Similarly, the GroupPermisison table is used to establish a many-to-many relationship between the Group and Permission tables.

Create Encrypted Objects

Once the above database has been created, the following encrypted SQL Server objects also need to be created.

View

This view returns all active users from the User table along with the group(s) they belong to, and each permission associated with the Group.

SQL
create view vwUserGroupPermissions
with encryption as
select
    u.ID as 'UserID',
    u.Firstname,
    u.Surname,
    u.Email,
    u.Active,
    u.DateCreated,
    g.Name as 'Group',
    g.ID as 'GroupID',
    p.Name as 'Permission',
    p.ID as 'PermssionID'

from [user] u

inner join usergroup ug on u.id = ug.UserID
inner join [group] g on g.ID = ug.GroupID
inner join GroupPermission gp on gp.GroupID = g.ID
inner join [Permisson] p on p.ID = gp.PermissionID

where u.Active = 1

Stored Procedure

The stored procedure below calls the vwUserGroupPermissions view and filters the data returned on the DateCreated column, i.e., all rows that fall within a date range of @DateCreateStart and @DateCreatedEnd are returned.

SQL
create procedure spGetActiveUsersByDateCreated
    @DateCreatedStart datetime = null,
    @DateCreatedEnd datetime = null
with encryption as

    select * from vwUserGroupPermissions v
    where
        (v.DateCreated  BETWEEN  @DateCreatedStart  AND @DateCreatedEnd) 

The Database

Once the database and the two objects are created, the server explorer window in SSMS is expected to show the following:

Server Explorer Showing Encrypted Objects

Both objects indicate a lock icon to denote that they are encrypted. When a developer selects Script Stored Procedure As -> Alter To -> New Query Editor Window, the following expected error is displayed as a result of the encrption:

Property TextHeader is not available for StoredProcedure '[dbo].[spGetActiveUsersByDateCreated]'.
This property may not exist for this object, or may not be retrievable due to insufficient access 
rights. The text is encrypted. (Microsoft.SqlServer.Smo)

Decrypt SQL Server Objects

Install and run the Optillect Decryptor tool by clicking on the Direct Download Link on this site.

Once this is installed, create a dedicated administrator connection (DAC) to access a running instance of SQL Server Database Engine as shown below:

Login to Optillect

When logging in, ensure that admin: server\instance name is entered. If login is successful, the Users database is shown as below:

Server Explorer on Login

The above screen cap shows that a DAC connection to the SQL Sever has been established. Now, we will expand the Users database, then expand the Procedures and Views nodes respectively as shown below.

Server Explorer showing encrypted objects

Right-click on the vwUserGroupPermission and select Show DDL Script option from the menu. The following decrypted T-SQL is shown in the right hand pane.

decrypted view

Again, perform the same action for spGetActiveUsersByDateCreated in order to view the decrypted T-SQL as shown below:

decrypted proc

A limitation of Optillect is that it is not possible to modify the T-SQL and apply the changes, however the script can be saved to a file from the File menu option and opened in SSMS for changes.

Select File -> Save DLL Script to File and click the Save button.

save script

Click on the tab which shows the T-SQL for spGetActiveUsersByDateCreated, and save the script in the same manner.

In the Apply changes section, we will take a look at making changes to the saved scripts and applying these changes using SSMS.

Removing Encryption

Right-click on the spGetActiveUserByDateRange stored procedure and select the Decrypt in-place menu option. Now right-click on the stored procedure and select Show DDL Script.

in place

The with encryption clause has been removed in the database.

Handle Multiple SQL Server Objects

It is also possible to decrypt multiple objects in the database and save the scripts to one file, or to a separate file per object. This is a handy option if we wish to take an encrypted database and save all the objects to the script file or set of script files. The scripts can then be used to create another database such as for a Development, Test or UAT environment.

Right-click on the Users database and select the Decryption Wizard.

decrypt wizard

The wizard lists all objects in the database including stored procedures, views, functions, triggers at the database and table level by default. In order to restrict the list to specific type of objects, the buttons on the right hand side can be used to turn the filters on and off.

The Output type dropdown list allows the T-SQL for all selected objects to be saved to one file or to a separate file. Selecting the Decrypt in-place option from the same dropdown list enables the user to remove the encryption on all of the selected objects and applies this change directly to the database.

wizard output type options

Apply Script Changes

Open the two files decrypted earlier in SSMS.

Modify the spGetActiveUsersByDateCreated stored procedure as shown below:

alter procedure spGetActiveUsersByDateCreated
    @DateCreatedStart datetime = null,
    @DateCreatedEnd datetime = null
with encryption as

    select * from vwUserGroupPermissions v
    where
        (v.DateCreated  BETWEEN  @DateCreatedStart  AND @DateCreatedEnd)
        order by v.DateCreated desc

The above change applies an order by clause to list the users most recently created in the database. The Create clause has also changed to an Alter clause.

Modify the vwUserGroupPermissions View as shown below:

SQL
alter view vwUserGroupPermissions
with encryption as
select
    u.ID as 'UserID',
    u.Firstname,
    u.Surname,
--    u.Email,
    u.Active,
    u.DateCreated,
    g.Name as 'Group',
    g.ID as 'GroupID',
    p.Name as 'Permission',
    p.ID as 'PermssionID'

from [user] u

inner join usergroup ug on u.id = ug.UserID
inner join [group] g on g.ID = ug.GroupID
inner join GroupPermission gp on gp.GroupID = g.ID
inner join [Permisson] p on p.ID = gp.PermissionID

where u.Active = 1

The View has simply changed to remove the email column. Again, the Create clause has changed to an Alter clause.

Now run both scripts in SSMS to apply the change to the Users database.

Test Script Changes

In order to test that the changes have been applied, run the following statements in SSMS.

SQL
declare @startdate datetime = '2014.01.01'
declare @enddate datetime = convert( varchar(20), getdate(), 102 )

exec spGetActiveUsersByDateCreated  @DateCreatedStart = @startdate ,@DateCreatedEnd =  @enddate

results

The email column has been removed and the results are ordered by the DateCreated column in Descending order.

As a final confirmation, open both objects in Optillect, and check the DLL to see if the changes are visible in the modified T-SQL.

Points of Interest

  1. If the DAC connection fails, it may be necessary to close any open connections to the database and try again.
  2. When expanding the procedures node, this may take a while to list all the stored procedures if there are many of them. The same applies to the Wizard, and in some cases Optillect shows a timeout error.
  3. It is not possible to open a DAC connection in SSMS.
  4. Although it is possible to remove encryption on selected object from within Optillect, changes cannot be made to the T-SQL.

Conclusion

The article presented a fast and simple approach to decrypting SQL Server Objects using a free third party tool called Optillect SQL Decryptor, without having to write complex decrypting algorithms. This allows the developer to use the time saved on actually focusing on the content of the scripts.

There are situations when a developer is maintaining and debugging legacy code, only to learn that an the error is originating from a specific stored procedure. Attempting to the open this stored procedure in SSMS results in an error because the object is encrypted. Optillect SQL Decryptor is a quick way to overcome this obstacle and provides a useful glimpse into the DDL script of the problematic stored procedure. If it is a complex routine which is difficult to debug, the encryption can be temporarily removed in the database for the object. This allows the stored procedure to be opened in Visual Studio and debugged by setting a breakpoint and stepping through the T-SQL in order to gain a better understanding of the routine, and pinpointing the cause of the error.

The tool is also handy in removing encryption from all objects in the database without having to create a new database. On the other hand, an existing production database can be preserved, and the DDL scripts for the objects exported to a script file. A new database can be created from this script file for a Development environment, allowing a team of developers to apply modifications such as adding error handling, dropping redundant tables, modifying views to account for dropped objects, transaction handling, and general bug fixing such as resolving null errors. Once the changes are completed, the database can be ported to a Test environment, and later moved to a UAT environment. If there is a green light from the Test Team, the original production database can be replaced by the new database with the enhanced functionality.

Revision History

Version 1.0

  • First version created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
Kuv Patel is a Senior Software Developer and Solutions Architect, specializing in .NET and Javascript frameworks development using MVC, ASP.NET, C#, WCF, Web API, Entity Framework, React, Angular.

Comments and Discussions

 
QuestionDont use this garbage utility Pin
Konstantin Taranov8-Dec-16 23:17
professionalKonstantin Taranov8-Dec-16 23:17 
QuestionAbout the 3rd party tool Pin
Nelek8-Dec-16 0:47
protectorNelek8-Dec-16 0:47 
QuestionPictures Pin
Eric Lapouge5-Feb-16 18:37
Eric Lapouge5-Feb-16 18:37 
AnswerRe: Pictures Pin
Kuv Patel6-Feb-16 1:53
Kuv Patel6-Feb-16 1:53 
Thanks, applied changes for image url and zip file download of scripts to create db and objects for playing with optillect. This should appear in a few hours.
Kuv Patel

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.