There are lots of great articles that describe how the command for decrypting SQL Server objects when they’re saved with encryption like Encrypting and Decrypting SQL Server Stored Procedures, Views, and User-Defined Functions Simple Way to Decrypt SQL Server Stored Procedure

Most of them rely on T-SQL and SSMS but I wanted to do the same thing with PowerShell. This way I can execute it for several objects at a time and maybe even in multiple databases and instances.

Let me first say that I’m not an encryption guru, but I do understand the technology and how I could develop some code to decrypt an object.

This article describes the inner workings of the command “Invoke-DbaDbDecryptObject”.

Why would you encrypt an object?⌗

Encrypting objects was first introduced in SQL Server 2000 to protect objects. I don’t prefer this method because there are a couple of disadvantages to it.

The first disadvantage is that the security is not very good. It’s very simple to get the original code when you have the right privileges. The code is not protected when for instance the SQL Server Profiler is run or when you catch executed procedures using extended events.

The second disadvantage, but you probably should have this anyway, is that you need to make sure that you have the original code in source control. Once the object is created with encryption there is no normal way to retrieve the code.

The third disadvantage is, and this is becoming more and more popular, that there is no easy way to check differences between objects and therefore is harder to use with CI/CD (Continuous Integration / Continuous Delivery).

I would only use this method if the “VIEW DEFINITION” privilege would not sufficient and there are no other DBAs who can google the solution to retrieve the original code.

How is an object encrypted?⌗

Objects are encrypted using “WITH ENCRYPTION” when creating or altering an object.

This encrypts the object and it’s no longer available using the general methods. If you want to view the code you’ll get an error message like this:

error encrypted object

During the encryption process, SQL Server looks at the column “imageval” in the table “sys.sysobjvalues”. This table can only be queried when you’re connected using the DAC (Dedicated Administrator Connection). This value is a VARBINARY(MAX) value.

The encryption uses an XOR cipher. The XOR cipher work by applying an XOR with a key (B) to a value (A) generating a result (C). This results in the following formula:

$$ C = B \land A $$

The cipher works is also called a modulus 2 addition. If we know the key and the encrypted value we can decrypt the value by applying the same key to the encrypted value. This results in the following formula:

$$ A = B \land C $$

Decrypting an object⌗

To decrypt the database object we need to calculate the secret (A) and apply the XOR cipher to it with known plain text (B) and the known encrypted text (C).

Getting the needed values⌗

(A) Get the secret⌗

The secret is the imageval value in the sys.sysobjvalues table for the object. This can be retrieved like this:

SELECT imageval AS Value FROM sys.sysobjvalues WHERE objid = OBJECT_ID('[schema].[objectname]')

The known object will be an alter statement for that particular type of object which we can use to calculate the key. This known object needs to be a valid create statement like:

ALTER PROCEDURE [schema].[objectname] WITH ENCRYPTION AS RETURN 0;

(B) Get the binary known object⌗

Because we’re dealing with binary data we need to convert the known object to binary (known plain). This can be done by using the System.Text.Encoding class with the function “GetBytes”.

(C) Get the encrypted known object⌗

To get the encrypted known object (known secret) we need to alter our object in the database.

We don’t want the original object to be replaced with our known object. To achieve that a transaction is used that’s rolled back right after the imageval value has been retrieved.

BEGIN TRAN;
    EXEC 'ALTER PROCEDURE [schema].[objectname] WITH ENCRYPTION AS RETURN 0;';
    
    SELECT imageval AS Value
    FROM sys.sysobjvalues
    WHERE objid = OBJECT_ID('schema.objectname');
ROLLBACK

Start decrypting⌗

To get out decrypted text we have to loop through the secret and apply the known plain and known secret. The function below demonstrates how the decryption is put in place:

function Invoke-DecryptData() {
    param(
        [parameter(Mandatory = $true)]
        [byte[]]$Secret,
        [parameter(Mandatory = $true)]
        [byte[]]$KnownPlain,
        [parameter(Mandatory = $true)]
        [byte[]]$KnownSecret
    )

    # Declare pointers
    [int]$i = 0

    # Loop through each of the characters and apply an XOR to decrypt the data
    $result = $(

        # Loop through the byte string
        while ($i -lt $Secret.Length) {

            # Compare the byte string character to the key character using XOR
            if ($i -lt $Secret.Length) {
                $Secret[$i] -bxor $KnownPlain[$i] -bxor $KnownSecret[$i]
            }

            # Increment the byte string indicator
            $i += 2

        } # end while loop

    ) # end data value

    # Get the string value from the data
    $decryptedData = $Encoding.GetString($result)

    # Return the decrypted data
    return $decryptedData
}

The loop increases the integer by two because each character in the secret has a size of 2 bytes. In the end, the decrypted data is still in a binary version. To get the text we need to use the method “GetString” using the Encoding object.

Output Invoke-DbaDbDecryptObject

The object is decrypted. Now what?⌗

First of all, you should save the code somewhere and preferably in a source control system. To make things easier I implemented a feature in the command to export all the results to a folder.

It will separate objects based on the instance and the object type and create a separate file for each object. To execute the script using a directory you have to use the -ExportDestination parameter.

Invoke-DbaDbDecryptObject -SqlInstance SQLDB1 -Database DB1 -ExportDestination C:\\temp\\decrypt

That’s the command to decrypt your encrypted objects. As I said, there are several solutions for this problem using T-SQL which are very good.

I always like to use PowerShell for these kinds of problems because it makes it easier to go through multiple servers, databases, and objects in one script. If you want to try it out, make sure you have the latest version of dbatools.