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.
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.
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.
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:
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:
When logging in, ensure that admin: server\instance name is entered. If login is successful, the Users database is shown as below:
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.
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.
Again, perform the same action for spGetActiveUsersByDateCreated in order to view the decrypted T-SQL as shown below:
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.
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.
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.
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.
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:
alter view vwUserGroupPermissions
with encryption as
select
u.ID as 'UserID',
u.Firstname,
u.Surname,
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.
declare @startdate datetime = '2014.01.01'
declare @enddate datetime = convert( varchar(20), getdate(), 102 )
exec spGetActiveUsersByDateCreated @DateCreatedStart = @startdate ,@DateCreatedEnd = @enddate
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
- If the DAC connection fails, it may be necessary to close any open connections to the database and try again.
- 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.
- It is not possible to open a DAC connection in SSMS.
- 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
