The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON – Experts Opinion

This is one of the most interesting story written by my friend Syrovatchenko Sergey. He is an expert on SQL Server and works at Devart. This article is one of the most creative and interesting story I have read in the recent past. It is beautiful and very interesting. I suggest you keep sometime aside from your busy schedule to read it and I promise that you will enjoy it.


A few weeks ago, I was puzzling out the logic of a free tool. The thing with the author of the tool turned out to be almost a detective story: he happened to be a developer living in South America. Certainly, the practical effect was not in his biography, but in queries, which his app was sent to the server. Let us see a story of Encrypted Procedures.

The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON - Experts Opinion expert-1

So, I installed and ran the application, and then I got ready to catch everything that can “delight” server by profiler. I have to disappoint you right off the reel: in the next two hours, I haven’t seen anything delightful at all. Generally, I faced various bloopers in queries which hardly deserve a single fair word:

SELECT
LogTruncations = (
SELECT TOP 1 SUM(cntr_value)
FROM ##tbl_cnt
WHERE counter_name = 'Log Truncations'
),
LogShrinks = (
SELECT TOP 1 SUM(cntr_value)
FROM ##tbl_cnt
WHERE counter_name = 'Log Shrinks'
),
LogGrowths = (
SELECT TOP 1 SUM(cntr_value)
FROM ##tbl_cnt
WHERE counter_name = 'Log Growths'
),
...

These queries can be written in much simpler way, and logical reads from the table will be shortened up:

SELECT
LogTruncations = SUM(CASE WHEN counter_name = 'Log Truncations' THEN cntr_value END),
LogShrinks = SUM(CASE WHEN counter_name = 'Log Shrinks' THEN cntr_value END),
LogGrowths = SUM(CASE WHEN counter_name = 'Log Growths' THEN cntr_value END),
    ...
FROM ##tbl_cnt

I could stop right at this point, but almost at the very end, I noticed that application kept calling user stored procedures from tempdb. I caught myself thinking: when did the application manage to create them, and most importantly, why?

It turned out that the installer quietly found, a default instance of SQL Server on my local machine and created stored procedures there. I tried working with this tool on the named instance and got the Error Message!

The architectural solution is not less than just fantastic… By the way, at each restart of the server the tempdb database was being recreated… so should I reinstall the program every single time?

Anyways, let’s deploy these stored procedures on the named server and at the same time, we’ll see what they got in store. When I opened the stored procedures in Database Explorer, I saw the following bad scene:

The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON - Experts Opinion expert-2

Lock on the objects… that means the stored procedures were created with the ENCRYPTION  parameter. Thus, we won’t be able to generate CREATE or ALTER in SSMS:

Property TextHeader is not available for StoredProcedure ‘[dbo].[shb_get_waitstats’]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.

We can’t get the source of encrypted objects in metadata as well:

SELECT o.name, s.[definition]
FROM sys.objects oJOIN sys.sql_modules s 
ON o.[object_id] = s.[object_id]WHERE [type] = 'P'
name                      definition
------------------------- ------------
shb_generate_waitstats    NULL
shb_get_waitstats         NULL
shb_get_waitstats_all     NULL
shb_avg_waiting_task      NULL
shb_expensiveqry          NULL
shb_get_querystats        NULL
shb_agent_log             NULL
shb_error_log             NULL
shb_default_trace         NULL
shb_spConfigure           NULL

The following Lifehack won’t work out either:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.shb_get_waitstats'))

I didn’t want to reinstall the application and decided to cheat a little. I turned on the “God Mode”, that allows to connect to the server via a DAC (Dedicated Administrator Connection):

EXEC sys.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE
GO

At the beginning of Server Name, we need to add ADMIN:, and then check that the user under which we connect is sysadmin:

The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON - Experts Opinion expert-3

If the connection goes right, we will have the absolute power over the server. But what will actually change? We already have sysadmin rights, that allow the whole enchilada.

As it turns out, there are still limitations. Have you ever tried to read from system tables? In SQL Server 2000, such behavior was allowed. With the release of version 2005, metadata security has been changed drastically, and the access to system tables has been closed.

Generally, you can implicitly access metadata tables via system views that help in 99% of cases. But not in this case. There is a separate sys.sysobjvalues table that stores encrypted objects:

SELECT * FROM sys.sysobjvalues

Normally, you can’t read it:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysobjvalues'.

But, when connecting via DAC, you can make a selection from any system table:

SELECT *
FROM sys.sysobjvalues
WHERE [objid] = OBJECT_ID('[dbo].[shb_get_waitstats]')
valclass objid       subobjid    valnum      value     imageval
-------- ----------- ----------- ----------- --------- --------------------------------
1        1429580131  1           0           0         0x037112F3D7F8C09E11A1A8FB....

Having the encrypted body of the stored procedure, we can decrypt it.

Firstly, we get binary view of the stored procedure in encrypted form. Then we create a template of the stored procedure with ENCRYPTION parameter, but instead of the body, we put the hyphen symbols:

DECLARE
      @obj SYSNAME = '[dbo].[shb_get_waitstats]'
    , @enc NVARCHAR(MAX)
    , @enc_length INT
    , @obj_type NVARCHAR(100)
    , @obj_name SYSNAME

SELECT
      @enc = imageval
    , @enc_length = (DATALENGTH(imageval) / 2) + 1
    , @obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + N'.' + QUOTENAME(o.name)
FROM sys.sysobjvalues v
JOIN sys.objects o ON v.[objid] = o.[object_id]
WHERE [objid] = OBJECT_ID(@obj, 'P')
    AND imageval IS NOT NULL

DECLARE @header NVARCHAR(MAX)
SET @header = N'ALTER PROCEDURE ' + @obj_name + N' WITH ENCRYPTION AS '
SET @header = @header + 
REPLICATE(CAST('-' AS NVARCHAR(MAX)), (@enc_length - LEN(@header)))

DECLARE @tmp TABLE (enc NVARCHAR(MAX))
BEGIN TRANSACTION

EXEC sys.sp_executesql @header

INSERT INTO @tmp (enc)
SELECT imageval 
FROM sys.sysobjvalues
WHERE [objid] = OBJECT_ID(@obj)

ROLLBACK TRANSACTION

DECLARE @blank_enc NVARCHAR(MAX)
SELECT @blank_enc = enc
FROM @tmp

SET @header = N'CREATE PROCEDURE ' + @obj_name + N' WITH ENCRYPTION AS '
SET @header = @header + 
REPLICATE(CAST('-' AS NVARCHAR(MAX)), (@enc_length - LEN(@header)))

;WITH
    E1(N) AS (
        SELECT * FROM (
            VALUES
                (1),(1),(1),(1),(1),
                (1),(1),(1),(1),(1)
        ) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b),
    E8(N) AS (SELECT 1 FROM E4 a, E4 b)
SELECT (
    SELECT 
        NCHAR(
            UNICODE(SUBSTRING(@enc, RowNum, 1)) ^
            UNICODE(SUBSTRING(@header, RowNum, 1)) ^
            UNICODE(SUBSTRING(@blank_enc, RowNum, 1))
        )
    FROM (
        SELECT TOP(@enc_length) RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
        FROM E8
    ) t
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

Then we can decrypt the required object with help of XOR transformation over the received lines:

CREATE PROCEDURE shb_get_waitstats
    WITH ENCRYPTION
AS 
    BEGIN    
	SET NOCOUNT ON;
        EXEC shb_generate_waitstats
        SELECT DISTINCT
          GETDATE() AS collection_time,
          a.category_name AS [Wait Category] ,
          ISNULL(dt.[Wait Time (ms/sec)], 0) [Wait Time (ms/sec)] ,
          ISNULL(dt.[Recent Wait Time (ms/sec)], 0) [Recent Wait Time (ms/sec)] ,
          ISNULL(dt.[Average Waiter Count], 0) [Average Waiter Count] ,
          ISNULL(dt.[Cumulative Wait Time], 0) [Cumulative Wait Time] ,
          ISNULL(dt.[avg_waiting_task_count], 0) AS [Avg Waiting Task]
        FROM    #am_wait_types a
                LEFT JOIN 
               ( SELECT  category_name ,
                 SUM(interval_wait_time_per_sec) AS [Wait Time (ms/sec)] ,
                 SUM(weighted_average_wait_time_per_sec) 
                           AS [Recent Wait Time (ms/sec)] ,
                 SUM(interval_avg_waiter_count) AS [Average Waiter Count] ,
                 SUM(resource_wait_time_cumulative) AS [Cumulative Wait Time] ,
                 SUM(interval_wait_time_per_sec) / 1000 AS avg_waiting_task_count
                 FROM    #am_resource_mon_snap						
                 GROUP BY category_name
                 ) dt ON a.category_name = dt.[category_name] 
    END

We can make a simple check using a simple example:

IF OBJECT_ID('dbo.test') IS NOT NULL
    DROP PROCEDURE dbo.test 
GO
CREATE PROCEDURE dbo.test (@a INT)
WITH ENCRYPTION
AS BEGIN
    RETURN 123
END
GO

Everything seems as simple as ABC, but what if you can’t connect via DAC, or you need to decrypt a scalar function or view… One of the options is to use a self-written CLR assembly, which you can find at CodeProject or use ready solutions.

As for the latter option, a free tool dbForge SQL Decryptor can decrypt all encrypted objects in the batch mode:

The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON - Experts Opinion expert-4

And above all, the tool does not require DAC-connection. I became interested in how to select data from the system table without DAC, but it turned out to be quiet simple. Firstly, SQL Decryptor gets the list of the pages, which store data from sys.sysobjvalues:

DBCC TRACEON(3604)
DBCC IND (tempdb, [sys.sysobjvalues], 1) WITH TABLERESULTS, NO_INFOMSGS
DBCC TRACEOFF(3604)

Then the tool scans all pages:

DBCC TRACEON(3604)
DBCC PAGE (tempdb, 1, 128, 3) 
DBCC PAGE (tempdb, 1, 132, 3)
DBCC PAGE (tempdb, 1, 132, 3)
DBCC PAGE (tempdb, 1, 138, 3)
DBCC PAGE (tempdb, 1, 23, 3)
DBCC TRACEOFF(3604)

and checks their content:

0000000053B5F8C0:   07000000 a209d600 ea9b0000 66000000 00000000  ....¢ Ö.ê›..f.......
0000000053B5F8D4:   66000000 00000000 16c4643f 0317383c a1a0203c  f........Äd?..81¡  1
0000000053B5F8E8:   a1a0203c a1a0203c a1a0203c 00000000 00000000  ¡  1¡  1¡  1........
0000000053B5F8FC:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5F910:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5F924:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5F938:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5F94C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5F960:   00000000 2f000000 2f000000 05000000 14000000  ..../.../...........
0000000053B5F974:   00008841 0000cc42 00000000 00008040 0000803f  ...A..ÌB......€@..€?
0000000053B5F988:   00008040 00008040 00008040 00000000 00000000  ..€@..€@..€@........
0000000053B5F99C:   00000000 00000000 00000000 00000000 00000000  ....................
0000000053B5FA14:   b1050000 00000000 78010000 00000000 8f010000  ±.......x..........
0000000053B5FA28:   00000000 a6010000 00000000 bd010000 00000000  ....¦.......½.......
0000000053B5FA3C:   d4010000 00000000 eb010000 00000000 02020000  Ô.......ë...........
0000000053B5FA50:   00000000 19020000 00000000 30020000 00000000  ............0.......
0000000053B5FA64:   47020000 00000000 5e020000 00000000 75020000  G.......^.......u...

Basing on the content, the tool gets the binary object view in encrypted form. You know what goes next – a simple XOR transformation.

Summary

If you need to automate the process of data decryption, you can write a script that is similar to my solution. However, in most cases, you just need to run dbForge SQL Decryptor, select an object and its source:

The Tale of the Cunning Dev, Encrypted Procedures, DAC and God Mode = ON - Experts Opinion expert-5

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Team Database Development and Version Control with SQL Source Control
Next Post
PowerShell Script – Backup Every Database In SQL Server

Related Posts

1 Comment. Leave new

  • Would it would possible to get a more detailed information on how to decrypt without DAC and without dbForge? With the output from the dbcc page what do I do from there?

    Reply

Leave a Reply

Menu