Decrypt SQL 2005 Stored Procedures, Functions, Views, and Triggers

| 12/24/2008 | 17 Comments

Note: This method has to be handled with utmost care only in critical situations, and my experienced database administrators to provide critical support services in case of a SQL emergency.

To decrypt the SQL Server 2005 objects, you need to login to SQL Server 2005 into the DAC (Dedicated Administrator Control) access.

SQL Server 2005 provides a special diagnostic connection for administrators when standard connections to the server are not possible. It allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted.

DAC (Dedicated Admin Connection) in SQL Server 2005

In versions prior to SQL Server 2005, if the server had maxed out on the resources, had a spinloop or maybe had corruption, it was impossible to get access to the server via remote desktop, Enterprise Manager, Query Analyzer, osql, third party tools etc. As a result, the last resort that most of the database developers/DBAs adopted was “REBOOT” :-) But that is not the holy grail. At times, SQL Server was able to do a checkpoint process and shutdown gracefully but at other instances, due to to a shutdown and subsequent start-up and recovery phase, corruption or AVs (Access Violations) could have resulted not to mention that the recovery process could be a long one depending upon the amount of work. In order to remedy this situation, in SQL Server 2005, one has a Dedicated Admin Connection option. This option (when configured) allows the developer/DBA to connect to the instance and issue T-SQL commands to troubleshoot the issue – maybe killing the offending process as a last resort.

Please note that by default, this option is available only from the client on the server. In order to enable the remote clients to use the DAC option, you can enable this option using the surface area configuration option:

image

–>

<!–[if !vml]–><!–[endif]–>

This can also be done using sp_configure command with the “remote admin connections” option being set to 1.

In order to use this option, you can either access it from the command prompt by using the new sqlcmd utility or through the Management Studio by using the ADMIN: option when connecting to the instance. Example:

Using sqlcmd:

sqlcmd -A -d testDB -E -S testinstance\namedinstance

-A is for the DAC

-d is for the database

-E is for integrated security

-S is for the instance (in this example, a named instance)

Management Studio:

a) Connect using the “Database Engine Query” option.

b) Put “ADMIN:” before the “testinstance\namedinstance”

c) Provide the connection information and connect.

Once you connect using DAC, you can execute any T-SQL commands to troubleshoot the issue and find the offending process.

Stored Procedure for Decrypting the SQL Objects

Create this stored procedure in the database where you need to decrypt the SQL objects.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*===============================================================
 
NAME: Decrypt SQL 2005 stored procedures, functions, views, 
and triggers
DESCRIPTION:           
HEADS UP: In order to run this script you must log in
to the server in DAC mode: To do so, type
ADMIN: as your server name and use the "sa"
or any other server admin user with the appropriate password.
 
CAUTION! DAC (dedicated admin access) will kick out all other
server users.
 
The script below accepts an object (schema name + object name)
that were created using the WITH ENCRYPTION option and returns
the decrypted script that creates the object. This script
is useful to decrypt stored procedures, views, functions,
and triggers that were created WITH ENCRYPTION.
 
The algorithm used below is the following:
1. Check that the object exists and that it is encrypted.
2. In order to decrypt the object, the script ALTER (!!!) it
and later restores the object to its original one. This is
required as part of the decryption process: The object
is altered to contain dummy text (the ALTER uses WITH ENCRYPTION)
and then compared to the CREATE statement of the same dummy
content.
 
Note: The object is altered in a transaction, which is rolled
back immediately after the object is changed to restore
all previous settings.
 
3. A XOR operation between the original binary stream of the
enrypted object with the binary representation of the dummy
object and the binary version of the object in clear-text
is used to decrypt the original object.
 
USER PARAMETERS:        @ObjectOwnerOrSchema
@ObjectName
 
RESULTSET:              NA
 
RESULTSET SORT:         NA
 
USING TABLES/VIEWS:     sys.sysobjvalues
syscomments
 
================================================================*/
CREATE PROCEDURE [dbo].[SQLDecryptor]
    (
      @ObjectOwnerOrSchema NVARCHAR(128) ,
      @ObjectName NVARCHAR(128)
    )
AS 
	--SET @ObjectOwnerOrSchema = 'dbo'
	--SET @ObjectName = 'myproc'
 
    DECLARE @i INT
    DECLARE @ObjectDataLength INT
    DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
    DECLARE @ContentOfDecryptedObject NVARCHAR(MAX)
    DECLARE @ContentOfFakeObject NVARCHAR(MAX)
    DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX)
    DECLARE @ObjectType NVARCHAR(128)
    DECLARE @ObjectID INT
 
    SET NOCOUNT ON
 
    SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
                              + ']')
 
	-- Check that the provided object exists in the database.
    IF @ObjectID IS NULL 
        BEGIN
            RAISERROR ( 'The object name or schema provided does not exist in the database',
                16, 1 )
            RETURN
        END
 
	-- Check that the provided object is encrypted.
    IF NOT EXISTS ( SELECT TOP 1
                            *
                    FROM    syscomments
                    WHERE   id = @ObjectID
                            AND encrypted = 1 ) 
        BEGIN
            RAISERROR ( 'The object provided exists however it is not encrypted. Aborting.',
                16, 1 )
            RETURN
        END
 
	-- Determine the type of the object
    IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']',
                 'PROCEDURE') IS NOT NULL 
        SET @ObjectType = 'PROCEDURE'
    ELSE 
        IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']',
                     'TRIGGER') IS NOT NULL 
            SET @ObjectType = 'TRIGGER'
        ELSE 
            IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
                         + ']', 'VIEW') IS NOT NULL 
                SET @ObjectType = 'VIEW'
            ELSE 
                SET @ObjectType = 'FUNCTION'
 
	-- Get the binary representation of the object- syscomments no longer holds
	-- the content of encrypted object.
    SELECT TOP 1
            @ContentOfEncryptedObject = imageval
    FROM    sys.sysobjvalues
    WHERE   objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
                              + ']')
            AND valclass = 1
            AND subobjid = 1
 
    SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject) / 2
 
	-- We need to alter the existing object and make it into a dummy object
	-- in order to decrypt its content. This is done in a transaction
	-- (which is later rolled back) to ensure that all changes have a minimal
	-- impact on the database.
    SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' ['
        + @ObjectOwnerOrSchema + N'].[' + @ObjectName
        + N'] WITH ENCRYPTION AS'
 
    WHILE DATALENGTH(@ContentOfFakeObject) / 2 < @ObjectDataLength 
        BEGIN
            IF DATALENGTH(@ContentOfFakeObject) / 2 + 4000 < @ObjectDataLength 
                SET @ContentOfFakeObject = @ContentOfFakeObject
                    + REPLICATE(N'-', 4000)
            ELSE 
                SET @ContentOfFakeObject = @ContentOfFakeObject
                    + REPLICATE(N'-',
                                @ObjectDataLength
                                - ( DATALENGTH(@ContentOfFakeObject) / 2 ))
        END
 
	-- Since we need to alter the object in order to decrypt it, this is done
	-- in a transaction
    SET XACT_ABORT OFF
    BEGIN TRAN
 
    EXEC ( @ContentOfFakeObject
    )
 
    IF @@ERROR <> 0 
        ROLLBACK TRAN
 
	-- Get the encrypted content of the new "fake" object.
    SELECT TOP 1
            @ContentOfFakeEncryptedObject = imageval
    FROM    sys.sysobjvalues
    WHERE   objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
                              + ']')
            AND valclass = 1
            AND subobjid = 1
 
    IF @@TRANCOUNT > 0 
        ROLLBACK TRAN
 
	-- Generate a CREATE script for the dummy object text.
    SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' ['
        + @ObjectOwnerOrSchema + N'].[' + @ObjectName
        + N'] WITH ENCRYPTION AS'
 
    WHILE DATALENGTH(@ContentOfFakeObject) / 2 < @ObjectDataLength 
        BEGIN
            IF DATALENGTH(@ContentOfFakeObject) / 2 + 4000 < @ObjectDataLength 
                SET @ContentOfFakeObject = @ContentOfFakeObject
                    + REPLICATE(N'-', 4000)
            ELSE 
                SET @ContentOfFakeObject = @ContentOfFakeObject
                    + REPLICATE(N'-',
                                @ObjectDataLength
                                - ( DATALENGTH(@ContentOfFakeObject) / 2 ))
        END
 
    SET @i = 1
 
	--Fill the variable that holds the decrypted data with a filler character
    SET @ContentOfDecryptedObject = N''
 
    WHILE DATALENGTH(@ContentOfDecryptedObject) / 2 < @ObjectDataLength 
        BEGIN
            IF DATALENGTH(@ContentOfDecryptedObject) / 2 + 4000 < @ObjectDataLength 
                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject
                    + REPLICATE(N'A', 4000)
            ELSE 
                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject
                    + REPLICATE(N'A',
                                @ObjectDataLength
                                - ( DATALENGTH(@ContentOfDecryptedObject) / 2 ))
        END
 
    WHILE @i <= @ObjectDataLength 
        BEGIN
		--xor real & fake & fake encrypted
            SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject,
                                                  @i, 1,
                                                  NCHAR(UNICODE(SUBSTRING(@ContentOfEncryptedObject,
                                                              @i, 1))
                                                        ^ ( UNICODE(SUBSTRING(@ContentOfFakeObject,
                                                              @i, 1))
                                                            ^ UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject,
                                                              @i, 1)) )))
 
            SET @i = @i + 1
        END
 
	-- PRINT the content of the decrypted object in chunks of 2K characters
 
    SET @i = 0
 
    WHILE DATALENGTH(@ContentOfDecryptedObject) / 2 > ( @i + 1 ) * 2000 
        BEGIN
            PRINT ( SUBSTRING(@ContentOfDecryptedObject, 1 + 2000 * @i,
                              2000 * ( @i + 1 )) )
            SET @i = @i + 1
        END
 
	-- Now print the last chunk, or the only chunk
	-- (if @ContentOfDecryptedObject does not exceed 2K characters)
    PRINT ( SUBSTRING(@ContentOfDecryptedObject, 1 + 2000 * @i,
                      2000 * ( @i + 1 )) )

Once this stored procedure is created, and you have logged into the SQL Server 2005 using the DAC access, you can execute this stored procedure with the necessary parameters to get the decrypted code returned as a string on the command prompt window which you can copy into notepad (or) an SQL query window.

VN:F [1.9.22_1171]
Rating: 7.7/10 (10 votes cast)
VN:F [1.9.22_1171]
Rating: +7 (from 9 votes)
Decrypt SQL 2005 Stored Procedures, Functions, Views, and Triggers, 7.7 out of 10 based on 10 ratings

Tags: ,

Category: SQL Server, SQL Server 2005

About the Author ()

  • DBA_JAI

    Thanx a lot Dear…

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  • Amit

    I can't decrypt a UDF with this.

    Can you please help? Thanks!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)