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:
- The first step is to get the encrypted value from sys.sysobjvalues via DAC connection.
- The next step is to take out the encrypted value of some blank procedure.
- 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.
SET NOCOUNT ON
GO
ALTER PROCEDURE dbo.MyDatabase WITH ENCRYPTION AS
BEGIN
PRINT ‘This is decrypted database’
END
GO
DECLARE @encrypted NVARCHAR(MAX)
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
BEGIN
SET @decryptedChar =
NCHAR(
UNICODE(SUBSTRING(
@encrypted, @cnt, 1)) ^
UNICODE(SUBSTRING(
@procedureHeader, @cnt, 1)) ^
UNICODE(SUBSTRING(
@blankEncrypted, @cnt, 1)) ^
)
SET @decryptedMessage = @decryptedMessage + @decryptedChar
SET @cnt = @cnt + 1
END
SELECT @decryptedMessageConclusion
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.
