Decrypting stored procedures in SQL Server
This post was migrated from my legacy custom CMS. For more details please read here.
This post is over a year old, the information may no longer be up to date.
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.