Descriptografando Procedures e Functions

Bom dia Pessoal!

Então venho abordar sobre um assunto um pouco polêmico da área de encriptação de objetos do SQL Server.

Até onde eu sabia, não era possível descriptografar procedures ou functions declarados com a cláusula WITH ENCRYPTION.

Ai que eu estava enganado. Existe sim uma solução, mas para que ela funcione é necessário que você conecte-se no SSMS via DAC (Dedicated Administrator Connection).

Opa, mas o que é o DAC?

Breve resumo do DAC

O DAC é uma conexão com o propósito de diagnósticos e resolução de problemas no SQL Server em casos onde a instância esteja em uma situação extrema e rara.

Conforme a documentação http://msdn.microsoft.com/pt-br/library/ms189595.aspx: “O SQL Server esforça-se o máximo para o êxito de uma conexão DAC, mas pode falhar em situações extremas”.

Ou seja o SQL Server guarda recursos para a conexão DAC.

Um exemplo prático é através da DMV (Dynamic Management View)  sys.dm_os_schedulers:

select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE (DAC)'

É de suma importância que em sua instância esteja habilitado a opção a nível server “Remote Admin Connections”:

--Habilita o DAC (Remote admin connections)
EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE

Para se conectar ao DAC é necessário logar com o nome admin:NomeServidor.

Lembrando que não há suporte para logar no DAC através do Object Explorer. Para logar é necessário abrir uma New Query no SSMS, sem o Object Explorer. Se preferir você pode se conectar via CMD através do comando sqlcmd -S NomeServidor -U sa -P Senha -A. A conta que você se conectar deve ser obrigatoriamente SYSADMIN.

Para identificar uma conexão DAC é através do enpoint_id = 1 da sys.dm_exec_sessions ou através da porta 1434 na sys.dm_exec_connections. Só é possível conectar uma sessão via DAC.

admin

Endpoint

 Voltando a solução de Descriptografia

Após estar conectado via DAC você poderá fazer a consulta na System Table sys.sysobjvalues coluna varbinary imageval. Quando você não está conectado via DAC, não é possível consultar algumas system tables. Está system table funciona no contexto de banco, igual a sys.procedures.

Passo a Passo

1. Crie a procedure model.dbo.SP_TESTE:

use model
GO
CREATE PROCEDURE dbo.SP_TESTE
WITH ENCRYPTION
AS
SELECT 1,2,3

 2. Faça as consultas abaixo via DAC:

objvalues


USE model
GO
SELECT object_name(objid) as NomeObjeto,objid as "Object_Id", ImageVal, 'sys.sysobjvalues' as TableSystem FROM sys.sysobjvalues
WHERE object_name(objid) = 'SP_TESTE'

SELECT object_name(Object_Id) as NomeObjeto,Object_Id, definition, 'sys.sql_modules' as TableSystem FROM sys.sql_modules
WHERE object_name(Object_Id) = 'SP_TESTE'

SELECT routine_name as NomeObjeto,2105058535 as "Object_Id" , routine_definition, 'information_schema.routines' as TableSystem FROM information_schema.routines
WHERE routine_name = 'SP_TESTE'

 

Obs.: Repare que a sys.sql_modules e information_schema.routines retornam NULL do código encriptado. Somente a sys.sysobjvalues retorna o varbinary.

3. Crie a procedure master.dbo.SP_Descriptografa_Objeto, que fará descriptografia do objeto SP_TESTE, banco model.

USE master
GO
CREATE PROC dbo.SP_Descriptografa_Objeto

/*******************************************************************************************************
 Nome: dbo.SP_Descriptografa_Objeto
 Adaptações: Patrocinio Maia
 Link Referência: http://jongurgul.com/blog/sql-object-decryption/
 DESCRIÇÃO: Faz a Descriptografia de qualquer objeto no Banco de Dados. Para rodá-la é necessário estar conectado via DAC (Dedicated Administrator Connection) "admin:NomeServer".
 Basicamente esta procedure faz um SELECT na "sys.sysobjvalues", coluna imageval do banco e objeto especificado.
 MODO DE USO: EXEC master.dbo.SP_Descriptografa_Objeto 'NomeBanco', 'NomeObjeto'
 Compilação...: SQL SERVER 2008 R2
 ******************************************************************************************************/ 

@NomeBanco varchar(300),@NomeObjeto varchar(300)

WITH ENCRYPTION

AS

DECLARE @ImprimeCodigoEncriptado NVARCHAR(MAX) =

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

SET NOCOUNT ON

 USE '+@NomeBanco+'

DECLARE @object_id INT
 ,@name SYSNAME

SELECT @object_id = [object_id]
 ,@name = [name]
FROM sys.all_objects
WHERE NAME = N'''+@NomeObjeto+'''

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

--Altera o objeto existente, em seguida, reverte para que possamos ter o valor do objeto criptografado dummy
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 DATALENGTH(@Final) <= 8000
BEGIN
 PRINT @Final
END
ELSE
BEGIN
 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'

--Faz a mágica
EXEC(@ImprimeCodigoEncriptado)

4. Execute a procedure  master.dbo.SP_Descriptografa_Objeto com os parâmetros ‘model’, ‘SP_TESTE’:

sp_descriptografa_objeto

Pronto! Habemus o Código!

A procedure master.dbo.SP_Descriptografa_Objeto é baseada no varbinary  (imageval) retornado  da System Table sys.sysobjvalues. Ela basicamente faz a conversão do varbinary em uma String (Varchar).

Galera, desvendado o mistério, agora os DBAs poderão abrir os códigos encriptados, Heheheheheheheh!!!!

Jesus os abençoe!!! =)

 

Anúncios

6 comentários sobre “Descriptografando Procedures e Functions

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s