Decrypt an Encrypted Object in SQL Server

After long enough , I found something interesting .. Decrypting existing encrypted objects of SQL Server !! (Ref : Source )

Step 1: Make sure remote admin connections is enable on server if not enable using following

EXEC sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE

GO

Step 2 : Connect to  SQL Server using DAC (Dedicated Administrator Connection)

• Open Sql Server Management Studio ( close the authentication screen !)
• Click on new query , on that authentication screen will pops up
• For authentication in Server use  “ADMIN:” as prefix in your server name
• Connect to your SQL server using your user name prefix with”ADMIN:”

Note : Make sure your username  credentials for a member should belong to the sysadmin group

Step 2 : Run Following Script BY replacing YOUR DATABASE and YOUR OBJECT

DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX)

SET NOCOUNT ON

USE [YOUR DATABASE] 

DECLARE @object_id INT,@name SYSNAME

SELECT @object_id = [object_id],@name = [name]

FROM sys.all_objects

WHERE name = N’YOUR OBJECT’ 

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,@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,@c+(@c*4000),4000)

SET @c = @c + 1

END

END

Happy Coding !!