The ability to “decrypt” an object in SQL Server be it stored procedure, view, function or trigger is something that I have run into a few times. In this post I will show you how you can “decrypt” any sql object using a simple script and highlight the things to be aware of.
Firstly my annoying use of “air quotes” is something that you would have noticed and that is because when you use “WITH ENCRYPTION” you are actually only obfuscating the object definition. The process to “decrypt” is very straight forward and requires only one pre-requiste the Dedicated Admin Connection (DAC) which I will explain later, but first I want to talk about how this solution came to be needed.
When I first needed to decrypt objects for SQL Server 2000 I had always used a solution posted by Joseph Gama which is based on work by shoeboy. Here is a link to that SQL 2000 solution. However when I went to use this on SQL 2005 it no longer worked…
The reason why the SQL 2000 solution no longer functioned was because the encrypted object values were no longer present in the ctext column of the syscomments table. They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).
The Dedicated Admin Connection (DAC) is a single connection for the administrator of SQL Server to access the instance. This is important because it gives access to objects that are otherwise hidden.
So that is the basic background to this updated solution. I needed to decrypt objects in later version of SQL Server and so I rewrote a solution using the same decryption methods of the original, but using the new location for the encrypted objects defintions. I also wanted to avoid dropping the encrypted object(s) and so changed this to an alter with rollback, as well as adding in the new object types.
I had kept my rewrite to myself for sometime until it came up on the forum in 2010 http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330/ .
I have since come to realize that perhaps this is not as visible as it could be and so I have given it a new home here where hopefully I can get feedback and address any bugs (undocumented features) without resurrecting that post.
How it works
The decryption method works as it has done in all similar versions of this code.
Encrypted Object ^ Template Object ^ Dummy Encrypted Object
For those interested in exactly what is going on please see the XOR_cipher wikipedia entry.
Getting it to work
In order to decrypt objects as I said you will need to access an object that is not available via a standard connection so for this you will need to enable Dedicated Admin Connection (DAC) if not already enabled.
EXEC sp_configure 'remote admin connections', 1 GO RECONFIGURE GO
Additionally if you are using SQL Express you will need to enable trace flag 7806.
Then with this done open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName
The last step is to change the code lines to reference the object you want the underlying text for and then check the messages tab.
Below is the code:
--http://jongurgul.com/blog/sql-object-decryption/ DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX) SET NOCOUNT ON /* --You must be using a DAC. SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e on (ec.[endpoint_id]=e.[endpoint_id]) WHERE e.[name]='Dedicated Admin Connection' AND ec.[session_id] = @@SPID */ USE [master] --change to where your encrypted object resides DECLARE @object_id INT,@name sysname SELECT @object_id = [object_id],@name = [name] FROM sys.all_objects WHERE name = N'jjj' --<=Either put your object name here or make sure @object_id is set, and that the object it relates to is encrypted. SELECT TOP 1 @ObjectNameStmTemplate = [ObjectStmTemplate] ,@EncObj = [imageval] FROM ( SELECT SPACE(1)+ ( CASE WHEN [type] = 'P' THEN N'PROCEDURE' WHEN [type] = 'V' THEN 'VIEW' WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' WHEN [type] IN ('TR') THEN N'TRIGGER' ELSE [type] END ) +SPACE(1)+QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME(ao.[name])+SPACE(1)+ ( CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' WHEN [type] IN ('FN') THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' WHEN [type] IN ('TF') THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END' WHEN [type] IN ('IF') THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N' WHEN [type] IN ('TR') THEN N' ON ' + OBJECT_NAME(ao.[parent_object_id]) + ' WITH ENCRYPTION FOR DELETE AS SELECT 1 N' ELSE [type] END ) +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.all_objects ao INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid] WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF','TR') AND ao.[object_id] = @object_id UNION ALL --Server Triggers SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(st.[name])+SPACE(1)+N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.server_triggers st INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[object_id] = sov.[objid] WHERE st.[object_id] = @object_id --Database Triggers UNION ALL SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(dt.[name])+SPACE(1)+N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.triggers dt INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[object_id] = sov.[objid] AND dt.[parent_class_desc] = 'DATABASE' WHERE dt.[object_id] = @object_id ) x([ObjectStmTemplate],[imageval]) --Alter the existing object, then revert so that we have the dummy object encrypted value BEGIN TRANSACTION DECLARE @sql NVARCHAR(MAX) SET @sql = N'ALTER'+@ObjectNameStmTemplate EXEC sp_executesql @sql SELECT @DummyEncObj = sov.[imageval] FROM sys.all_objects ao INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid] WHERE ao.[object_id] = @object_id ROLLBACK TRANSACTION DECLARE @Final NVARCHAR(MAX) SET @Final = N'' DECLARE @Pos INT SET @Pos = 1 WHILE @Pos <= DATALENGTH(@EncObj)/2 BEGIN SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate COLLATE DATABASE_DEFAULT,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1)))) SET @Pos = @Pos + 1 END --If the object is small then just print, else print in chunks IF DATALENGTH(@Final) <= 8000 BEGIN PRINT '--SMALL--' PRINT @Final END ELSE BEGIN PRINT '--BIG--' DECLARE @c INT SET @c = 0 WHILE @c <= (DATALENGTH(@Final)/8000) BEGIN PRINT SUBSTRING(@Final,1+(@c*4000),4000) SET @c = @c + 1 END END