Friday, 20 May 2016

Simple Way to Decrypt SQL Server Stored Procedure

blogger tricks

When stored procedures are created in SQL Server, their entire text body is accessible to all those who have the required permissions for accessing the data. Therefore, it becomes very easy to expose the underlying object content by creating stored procedures and analyzing it content via SQL Server Management Studio, Query Analyzer, Windows PowerShell, or any commercial utility. This data transparency, as a result, poses a risk of data compromises by the potent cyber criminals. Therefore, SQL Server developers consider encryption, the most suitable way to authenticate their data.

Need For Decrypting SQL Server Stored Procedure

Even though, encryption of stored procedures of SQL Server ensures that the objects cannot be accessed and read easily, at times it poses some issues to the users. Being a SQL Admin, I have come across many issues where the users no longer had access to their decryption script and therefore were not able to decrypt the database when required.

In certain scenarios, it happens that the administrators are handed over encrypted, SQL databases to work on. In order to work with them, the first thing that the admin needs is the encryption script and in the absence of it, the admin go for decrypting the database.

Procedure to Decrypt Stored Procedure in SQL Server

The first thing that needs to be done is to open a DAC (Dedicated Administrator Connection) to the SQL Server. It is to be noted that the DAC can only be used in three conditions:

  • The user is logged in the server.
  • The user is using a client on that server.
  • The user holds the sysadmin role.

Keep in mind that the DAC will not work if the user is not using TCP/IP and will show cryptic error if TCP/IP is not used.

The process is mainly divided into three sections:

  1. The first step is to get the encrypted value from sys.sysobjvalues via DAC connection.
  2. The next step is to take out the encrypted value of some blank procedure.
  3. Get the unencrypted blank procedure statement in plaintext format. Apply XOR to all the results. XOR is the simplest decryption procedure and is the basic algorithm used in MD5.
 PRINT ‘This is decrypted database’
SET @encrypted = (
  SELECT imageval
  FROM sys.sysobjvalues
  WHERE OBJECT_NAME(objid) = ‘TestDecryption’)
DECLARE @encrypedLength INT
SET @encryptedLength –DATALENGTH(@encrypted) / 2
DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N ‘ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS’
SET @procedureHeader = @procedureHeader + REPLICATE(N ‘-‘,
(@encryptedLength –LEN(@pocedureHeader)))
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptMessage = ‘’
SET @cnt = 1
WHILE @cnt <> @encryptedLength
 SET @decryptedChar = 
                     @encrypted, @cnt, 1)) ^
                     @procedureHeader, @cnt, 1)) ^
                     @blankEncrypted, @cnt, 1)) ^
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
SELECT @decryptedMessage


SQL Server database encryption ensures database authenticity from unwanted users. However, this may at times pose a problem for the user. With the help of the above-mentioned script, the user can easily decrypt their stored procedures in SQL Server. However, if the above procedure doesn’t work for you, then going with a third party SQL decryptor tool is the best solution.

1 comment:

  1. I'm unclear about what role "the users" in your article is referring to.
    I suspect by "Users" you actually mean SQL DBA's in a company you are consulting to?

    The article could be read that this approach will let you hack into SQL. Which is not true.
    The average user can't even execute a Stored Proc unless they've been granted EXECUTE permission. (either directly or indirectly via a role)
    Even then they don't get access to sp_helptext. To execute that, or to read the system catalog tables, requires they have privileged rights (ie: DBO / Sysdamin)
    At which point their database is already compromised. Any intellectual property in their stored procs should be considered lost.
    In absence of being able to decrypt & read the sp code, the skilled hacker can usually reverse engineer the logic using a blackbox approach.

    For that reason ISV's who wish to protect their IP should also protect themselves legally via strong T&C's in their contract.

    That said. If your article is targeted at DBA's who do not use source control who encrypt their procs. And need to extract them from the database. No doubt your technique may come in handy.

    As would a text book on Best Practice DevOps.