The story about cunning ... Indian, encrypted procedures, DAC and "God mode"

That week I had to understand the logic of the work of one free tool. An almost detective story came out with its author, who later turned out to be a Hindu of Canadian origin living in South America.

Of course, the practical value was not in the biography of the author, but in the requests that were sent by the application to the server.

Installed. Launched. I started as a small stand and started building everything with the profiler that the application should have “pleased” the server. I dare to disappoint - in the next two hours I did not see anything joyful. Basically there were all sorts of pearls in requests that clearly did not claim compliments:
')

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' ), ... 

Since they can be written an order of magnitude easier and reduce the logical reads from the table:

 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 

This could be the end ... But almost at the end I saw that the application calls the user stored procedures from tempdb . I caught myself thinking: “When the application managed to create them ... and most importantly, why?”

It turns out that the installer quietly found the default instance of SQL Server on my local machine and created the storage there. I tried to work with this tool on the named instance ... Error Message !

The architectural decision on the verge of fiction. By the way, I’ll say, every time the server is restarted, the tempdb database is re-created ... so should I reinstall the program every time? Rave! Brad ... as my parrot would say.

Okie ... Let's deploy these storage on the named server, and at the same time we will see what is so valuable in them. Open the stored procedures in the Database Explorer and see the "oil painting":



A lock on objects ... which means that the stored procedures are created with the WITH ENCRYPTION parameter and the CREATE or ALTER command cannot be generated in SSMS :

Property TextHeader is not available for StoredProcedure '[dbo]. [Shb_get_waitstats']'. This is a retrieved due to insufficient access rights. The text is encrypted.

In the metadata, we also cannot get the source of encrypted objects:

 SELECT o.name, s.[definition] FROM sys.objects o JOIN 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 

Such a livefack will not work either:

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

I did not want to reinstall the application and therefore decided to cheat. Turn on "God mode", which allows you to connect to the server via 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 

First we add the ADMIN: connections in the SQL document and check that the user we are connecting to is sysadmin :



If the connection is successful, then we will have absolute power on the server. But what will change in practice? After all, we already have the rights of sysadmin , which allow everything that is possible.

As it turns out, the restriction is still there. Have you ever tried to read from the system tables? In SQL Server 2000, this behavior was allowed. With the arrival of the 2005 version, the metadata security has undergone significant changes and there is no longer access to the system tables directly.

Basically, tables with metadata can be accessed implicitly, through system representations, which in 99% of cases are sufficient. But not in our case. There is a separate sys.sysobjvalues table in which encrypted objects are stored:

 SELECT * FROM sys.sysobjvalues 

Under normal conditions, it is impossible to read from it:

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

but when connected via DAC, it is possible to retrieve from any system table and from it in particular:

 SELECT * FROM sys.sysobjvalues WHERE [objid] = OBJECT_ID('[dbo].[shb_get_waitstats]') 

 valclass objid subobjid valnum value imageval -------- ----------- ----------- ----------- --------- -------------------------------- 1 1429580131 1 0 0 0x037112F3D7F8C09E11A1A8FB.... 

With the encrypted body of the stored procedure in hand, you can decipher it ...

To begin with, we get a binary representation of the encoded in encrypted form. We create a blank with the identical name and the WITH ENCRYPTION parameter, but instead of the body we substitute hyphens:

 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)') 

Further, using XOR transformation over the received strings, we can decipher the required object:

 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 

You can easily check everything with 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 to be so elementary, but what if there is no opportunity to connect through the DAC ? Or you need to decipher a scalar function or a view ... There is an option to use the samopisny CLR assembly, which can be searched on CodeProject or use ready-made solutions.

Speaking of the last version ... There has long been a free product - dbForge SQL Decryptor , which allows decoding all script objects in batch mode:



And most importantly, for his work does not require DAC connection. It became very interesting to me how to select data from the system table without using DAC ... everything turned out to be very simple. First, SQL Decryptor retrieves the list of pages where data from sys.sysobjvalues ​​is stored :

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

Then it goes around all the 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) 

Looks at their contents:

 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... 

And on it gets a binary representation of the objects in encrypted form. And then you already know how it happens - a simple XOR conversion.

Brief conclusions:

What to say about this? If you need to automate the process of decrypting objects, you can write a script by analogy with my decision. But in most cases it will be enough just to run dbForge SQL Decryptor , select an object and get its source:



After this CodeReview, I talked to a Hindu. He listened to all my comments on the part of the problems found with queries and no longer got in touch ... That was such a productive week.

If you want to share this article with an English-speaking audience:
DAC and God Mode = ON