Ever wished you could decrypt a database object in SQL Server? The good news is, you can, even in newer versions of SQL Server! This article will take you through the basics of how to decrypt a database object, and it will hopefully give you some deeper knowledge of how encrypted objects are stored in the database, and how to access them.

Why use encryption at all?

Encryption allows you to put sensitive information in a procedure, like passwords to application roles, business logic, etc, that you don’t want visible even to developers or other power users on the server. It can also serve as a first line of defense against people making changes to your code without proper knowledge just because they have access to the server.

How procedure encryption works in SQL Server

SQL Server stores encrypted objects using a simple XOR encryption scheme (actually, “encryption” is a stretch, but I’ll leave the semantics for another time).

Let’s assume that your plaintext is CHAR(47) and your key is CHAR(30) – the ^ character is used to perform XOR operations in SQL Server. This means that the encrypted code will be CHAR(47^30)=CHAR(49). So to get the plaintext from this, you can simply apply the key to the encrypted code: CHAR(49^30)=CHAR(47).

It may be well worth mentioning that this XOR encryption of procedure code is not at all the same as that used to encrypt data and connections – that is a different matter entirely, based on industry standards such as AES and triple-DES.

Dedicated administrator connection (DAC)

Once upon a time, in SQL Server 2000, the encrypted code would be readily available to anyone with sufficient permissions to read the dbo.syscomments table, but as of SQL Server 2005, the encrypted text has moved to sys.sysobjvalues, and this table is only visible in a so-called “dedicated administrator connection“.

A dedicated administrative connection carries special privileges and can only be made one at a time, and unless you’ve specifically reconfigured the server to allow remote DAC connections, you need to log on locally.

To open a dedicated administrator connection in SQL Server Management Studio, simply add “ADMIN:” before the name/instance of your server.

If you’re connecting with the sqlcmd utility, add the -A switch.

The T-SQL

The following code is a loose aggregate of various code examples on the net, all with the same purpose. You should know, however, what you are doing. This code is made available to you without any warranty whatsoever.

The decryption process works like this:

• Get the encrypted code.
• Generate a dummy object, encrypt it.
• Get the encrypted dummy code.
• The encrypted and unencrypted dummy code will give you the key.
• Use the key to decrypt the real encrypted object.

First off, declaring the variables, validating the input and setting up the stuff we need.

SET NOCOUNT ON
DECLARE @owner sysname='dbo', @name sysname='sp_someprocedure';

-----------------------------------------------------------
--- Declarations:

DECLARE @offset            int=1;
DECLARE @datalength        int;
DECLARE @encrypted_object  nvarchar(max);
DECLARE @decrypted_object  nvarchar(max)=N'';
DECLARE @fake_object       nvarchar(max);
DECLARE @fake_encrypted_object nvarchar(max);
DECLARE @lf                nvarchar(max)=NCHAR(13)+NCHAR(10);
DECLARE @type              varchar(128);
DECLARE @object_id         int=OBJECT_ID('['+@owner+'].['+@name+']');
DECLARE @a int, @b int, @c int;

--- Check that the object exists
IF (@object_id IS NULL) BEGIN;
    RAISERROR('Object does not exist.', 16, 1);
    RETURN;
END;

--- Check that the object really is encrypted.
IF (NOT EXISTS (SELECT TOP 1 * FROM sys.sql_modules
        WHERE [object_id]=@object_id AND [definition] IS NULL)) BEGIN;
    RAISERROR('Object is not encrypted.', 16, 1);
    RETURN;
END;

--- Store the SQL type name of the object in @type
SELECT @type=(CASE [type]
    WHEN 'P' THEN 'PROCEDURE'
    WHEN 'TR' THEN 'TRIGGER'
    WHEN 'V' THEN 'VIEW'
    ELSE 'FUNCTION' END)
FROM sys.objects
WHERE [object_id]=@object_id;

--- @encrypted_object is the encrypted, binary, version of the code:
SELECT TOP 1 @encrypted_object=imageval
FROM sys.sysobjvalues
WHERE [objid]=@object_id AND valclass=1 and subobjid=1;

SET @datalength=DATALENGTH(@encrypted_object)/2;

We now have the encrypted object in the @encrypted_object variable.

Creating a fake encrypted object

Now we’re going to replace the encrypted object with an encrypted object of our own, but only in a transaction that we’re going to roll back. The reason for this is that we can then derive the XOR key using the (known) plaintext and encrypted code for our dummy object.

--- We're going to ALTER the existing object to a "known plaintext"
--- with encryption. That way, we can reverse-engineer the encryption
--- key, using the new encrypted object.
--- All of this is done in a transaction that we'll roll back when
--- we're done with it.
SET @fake_object=N'ALTER '+@type+N' ['+@owner+N'].['+@name+N']
WITH ENCRYPTION AS
';

--- Fill the fake object with dashes ("-") until it's as long as
--- the encrypted object.
WHILE (DATALENGTH(@fake_object)/2<@datalength) BEGIN;
    IF (DATALENGTH(@fake_object)/2+4000<@datalength)
        SET @fake_object=@fake_object+REPLICATE(N'-', 4000);
    ELSE
        SET @fake_object=@fake_object+REPLICATE(N'-',
            @datalength-(DATALENGTH(@fake_object)/2));
END;

BEGIN TRANSACTION;
    --- Implement the fake encrypted object:
    EXEC(@fake_object);

    --- Retrieve the encrypted version of the "known plaintext".
    SELECT TOP 1 @fake_encrypted_object=imageval
    FROM sys.sysobjvalues
    WHERE [objid]=@object_id AND valclass=1 and subobjid=1;

    --- Now that we have the encrypted fake object, roll back
    --- the transaction, so we don't break the original object.
ROLLBACK TRANSACTION;

The encrypted code of our dummy object now resides in @fake_encrypted_object.

Decrypting the encrypted object

The last step of the decryption process is to loop through the encrypted original object and decrypt it using the key that we’re derived.

--- Change the @fake_object from ALTER to CREATE (because this is
--- how the encrypted objects are stored in the database!)
SET @fake_object='CREATE'+SUBSTRING(@fake_object, 6, LEN(@fake_object));

-----------------------------------------------------------
--- Perform decryption using the three versions: the encrypted
--- code, the plaintext fake code and the encrypted fake code.

WHILE (@offset<=@datalength) BEGIN;

    SELECT
        @a=UNICODE(SUBSTRING(@encrypted_object,      @offset, 1)),
        @b=UNICODE(SUBSTRING(@fake_object,           @offset, 1)),
        @c=UNICODE(SUBSTRING(@fake_encrypted_object, @offset, 1));

    SELECT @decrypted_object=@decrypted_object+NCHAR(@a^(@b^@c)),
        @offset=@offset+1;
END;

And the final result should now be in @decrypted_object!

Printing the results

This step, obviously, is not part of the actual decryption process, but I’ve written a way to print a long string value line by line, so SQL Server Management Studio won’t truncate it. That way, you can copy the code and paste it into another connection.

-----------------------------------------------------------
--- Print the results:

WHILE (@decrypted_object IS NOT NULL) BEGIN;
    PRINT LEFT(@decrypted_object,
        CHARINDEX(@lf, @decrypted_object+@lf)-1);

    SET @decrypted_object=NULLIF(
        SUBSTRING(@decrypted_object,
        CHARINDEX(@lf, @decrypted_object+@lf)+LEN(@lf),
            LEN(@decrypted_object)), '');
END;

I hope this code comes in useful. As usual, if you have any comments or questions, leave them in the comments field below!