Decrypting stored procedures in SQL Server

This post is over a year old, the information may no longer be up to date.

This post was migrated from an old custom CMS. Some data may not look accurate.

Tutorial • 06 April 2020

I came across an issue recently where I lost the code to a stored procedure created a few years ago, usually our stored procedures are not encrypted however this one was. I’ve never thought about being able to decrypt a stored procedure, as it kind of feels against the point of encrypting it in the first place - but last week I found out that you could indeed decrypt an encrypted stored procedure, and it’s actually super easy.

All you need to do is connect to DAC via SMSS on your SQL Server by clicking on Database Engine Query. This looks like any normal database connection but it’s much cooler, when the window pops up add admin: to the start of your server name, for example: admin:localhost. Once in DAC, paste in the below and edit Line 7 to your schema and procedure name..

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
USE [DatabaseName]
DECLARE @secret VARBINARY(MAX);
DECLARE @known_encrypted VARBINARY(MAX);
DECLARE @known_plain VARBINARY(MAX);
DECLARE @object_type NVARCHAR(MAX);
DECLARE @object_name NVARCHAR(MAX);
SET @object_name = '[schema].[procedure_name]';

/* need to have the schema [dbo] and the procedure name [procedure] */

SELECT @secret = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID(@object_name);
DECLARE @cmd NVARCHAR(MAX);
SELECT @cmd = CASE type_desc
                  WHEN 'SQL_SCALAR_FUNCTION'
                  THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 0;END;'
                  WHEN 'SQL_TABLE_VALUED_FUNCTION'
                  THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS @r TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END;'
                  WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION'
                  THEN 'ALTER FUNCTION ' + @object_name + '()RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 0 i;'
                  WHEN 'SQL_STORED_PROCEDURE'
                  THEN 'ALTER PROCEDURE ' + @object_name + ' WITH ENCRYPTION AS RETURN 0;'
              END
FROM sys.objects
WHERE object_id = OBJECT_ID(@object_name);
SELECT @cmd = REPLICATE(CAST(CHAR(32) AS NVARCHAR(MAX)), DATALENGTH(@secret)) + @cmd;
SELECT @known_plain = CAST(@cmd AS VARBINARY(MAX));
BEGIN TRAN;
EXEC (@cmd);
SELECT @known_encrypted = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID(@object_name);
ROLLBACK;
DECLARE @i INT= 0;
DECLARE @plain VARBINARY(MAX)= 0x;
WHILE @i < DATALENGTH(@secret)
    BEGIN
        SET @plain = @plain + CAST(REVERSE(CAST(CAST(SUBSTRING(@secret, @i, 2) AS SMALLINT)^CAST(SUBSTRING(@known_plain, @i, 2) AS SMALLINT)^CAST(SUBSTRING(@known_encrypted, @i, 2) AS SMALLINT) AS BINARY(2))) AS BINARY(2));
        SET @i+=2;
    END;
SET @cmd = N'SELECT  (SELECT ''--''+CHAR(13)+CHAR(10)+''GO''+CHAR(13)+CHAR(10)+' + N'CAST(@plain AS NVARCHAR(MAX))+CHAR(13)+CHAR(10)+''GO''+CHAR(13)+CHAR(10)+''--''' + N' AS [processing-instruction(sql)] FOR XML PATH(''''),TYPE) AS [object_definition for ' + REPLACE(@object_name, ']', ']]') + ']';
EXEC sp_executesql 
     @cmd, 
     N'@plain VARBINARY(MAX)', 
     @plain;

Now ideally you should only be decrypting procedures you have the right to decrypt (procedures you created or have been given permission to access), but this is super useful if you need to get sql code that you no longer have saved.

Enjoy!