Simple Way to Decrypt SQL Server Stored Procedure

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.

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 @decryptedMessage

Conclusion

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads