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.
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:
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’:
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!!! =)
patrociniomaia 