Overview

The SQL Server 2005 and SQL Server 2008 provide a new feature for encrypting data to protect it from unauthorized access or hacker’s attacks. To make the data accessible, it needs to be decrypted with the use of key or password. It is necessary to encrypt critical SQL Server data, connections and stored procedures while storing in the database and transmitting data between client and server across network.

The problem with encryption is that it is not a very secure way of encrypting contents of stored procedures. Most of users rely on source code that is stored in SQL Server instead of moving code to source control application. In order to access the code, we need to decrypt the encrypted database objects. In the article, we will be discussing the way to decrypt the encrypted SQL Server database objects.

How to Decrypt SQL Server Database Objects

The possible solutions to decrypt the encrypted SQL Server database objects are by using codes to decrypt or by using third party tools designed to decrypt the database objects.

Encryption

Firstly, Let us see how encryption of database objects works: SQL server database objects like stored procedures, views, and functions can be encrypted using ‘WITH ENCRYPTION’ option to hide the contents of the database objects from unauthorized access. Specifying WITH ENCRYPTION will prevent unauthorized access, as data will be stored in system table in encrypted form.

If we try to access the content of the encrypted stored procedure using: Error will be generated ‘the object comments have been encrypted’.

Decryption in SQL Server Database

Firstly, we need to open a Dedicated Administrator Connection (DAC) to SQL Server, which will give access to tables and views. It is done by prefixing admin to connection string upon connection of query windows using SQL Server Management studio. DAC mode 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.

The Process of Decryption Comes in Following Steps:

Get the encrypted Definition

The system table where the actual definition is stored is called sys.sysobjvalues and it is not directly accessible. We will access it using DAC mode. The information that we want to retrieve is stored in a VARBINARY (MAX) column called imageval

Use XOR decryption process

During encryption, SQL Server takes the bytes in imageval column and applies bitwise XOR with a bye pattern (called key pattern) on it. XOR is fully symmetric which means if bit pattern A & key pattern B are used, XOR result is A^B=C then, C^B=A is also true. That is we just need to XOR again with same pattern key to decrypt the data.

SQL server uses a pattern generated by object_id and object_type of encrypted object. If we can use the SQL Server to encrypt the object, we can use the same object_id as the object to decrypt by calculating the key pattern using XOR, which will combine plain text and encrypted text. We need to alter encrypted object by temporarily replacing it with known object, grab sys.sysobjvalues.imageval value, and undo the replace operation, which is possible by wrapping all this in a transaction.

We need to get key pattern first and the plaintext of object. Since XOR of two VARBINARY (MAX) values is not possible, we can use loop through the bytes one at a time to apply XOR manually.

Alternatively, the code given below can be used for decrypting SQL Server database object Stored Procedure:

Inside the code, the procedure uses name of object as parameter that needs to be decrypted. It detects the object type first and creates ALTER statement for it. It fills the beginning of statement with enough spaces to cover all cipher text. Then, procedure replaces object inside transaction and capture cipher text of the known plaintext. Lastly, the XOR operation will be carried out in a loop and then return the decrypted object definition.

Conclusion

In the article, we have discussed about the encryption and decryption of SQL Server database objects. In order to decrypt the encrypted SQL Server database object, the most difficult task is to get access to encrypted data. With the help of DAC, we can access the data. Once we get the encrypted data, we can decrypt using key pattern that can be calculated using plaintext and cipher text of known object of same type and with same object_id. If the methods discussed above fail to decrypt SQL Server database then you can try third party commercial tool.