How To Decrypt An Encrypted Stored Procedure In MS SQL Server

What is Encryption in MS SQL Server

SQL Server encryption is jumbling the data or data objects such as stored procedure using a secret key or unique password. Sometimes it becomes necessary to hide the business logic's due to some security reasons. The keyword WITH ENCRYPTION is used to encrypt the stored procedure in SQL Server databases. When any object gets encrypted then it does not mean that the data is inaccessible. In the SQL Server databases the encryption of stored procedure reduces the chances of data loss and enhance the security if any bypass access permission is granted to the users. You can use encryption in SQL Server database to encrypt connections, data and stored procedures, etc.

But when user lose the encryption key or password then it becomes very difficult to decrypt an encrypted stored procedure in SQL Server.

When any encrypted object or stored procedure come to see then this error message reflects on the screen.

How To Find An Encrypted Stored Procedure in SQL Server

If you want to decrypt an encrypted stored procedure, then firstly you need to find out the encryption. Here is the sample query which can be used to find out an encrypted stored procedure in SQL Server database.

Syntax:

case
    when definition is null then ‘yes’
    else ‘no’
    end as ‘is_encrypted’
    from sys.sql_modules s inner join
    sys.objects o on s.object_id = o.object_id
    where type in (‘p’,’tr’,’FN’)

After this you will able to know, how the objects is encrypted, and where to find the encrypted objects.

How to Decrypt an Encrypted Stored Procedure in SQL Server

To decrypt encrypted SQL Server stored procedure you need to follow the given steps:

  > Open a DAC (Dedicated Administrator Connection) to SQL Server. To use DAC you need to login with database, having sysadmin role permission.

  > This is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option.

After performing these steps you need to write a code for decrypting encrypted SQL Server stored procedure:

1. You will need an obfuscated code to perform next step. Here I have stated an example of that code.

Syntax:

SELECT @secret = imageval
     FROM sys.sysobjvalues
     WHERE objid = OBJECT_ID(@object_name);

2. Replace the objects with similar name, similar object_id and the same length.
3. Now, get the newly obfuscated code the same way as shown above.
4. XOR the values together; obfuscated original, replacement, and obfuscated replacement.

Alternate Solution to Decrypt An Encrypted Stored Procedure in SQL Server

Sometimes the manual method becomes more critical and you may lose your data completely. To avoid the long and frustrating method of decryption it is recommended to get the help of third party software like SQL decryptor tool.

Conclusion

In this article we have discussed about how to decrypt an encrypted stored procedure in SQL Server. Encryption is a technique to secure the database and database objects like stored procedures.