Home / T-SQL / Decrypt Stored Procedure, Views, Functions in SQL Server

Decrypt Stored Procedure, Views, Functions in SQL Server

Last week I had a stored procedure in my SQL database which was encrypted. On the internet, you can find a lot of (payed) tools to decrypt a stored procedure but I also found this script. This script can decrypt Stored Procedures, Functions, Views  and Triggers decrypten. You can find the script below. You need to change the databasename first!

/*==================================================================================
Connectie in DAC-mode vereist!!
Connect eerst met de locale server, doe dan de connectie in je query window veranderen en vul in bij servernaam:
ADMIN:SERVERNAMEINSTANCE 
BIJV: ADMIN:LOCALHOST  (indien maar 1 instance)
Vul bij @ObjectName het object in dat je wilt decrypten
Vul bij @ObjectOwnerOrSchema het schema in, meestal dbo
*/
 
USE VULHIERDATABASEIN
GO
 
DECLARE @ObjectOwnerOrSchema NVARCHAR(128)
DECLARE @ObjectName NVARCHAR(128)
 
SET @ObjectOwnerOrSchema = 'dbo'
SET @ObjectName = 'VULHIERSTOREDPROCEDURE IN'
 
DECLARE @i INT
DECLARE @ObjectDataLength INT
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
DECLARE @ContentOfDecryptedObject NVARCHAR(MAX)
DECLARE @ContentOfFakeObject NVARCHAR(MAX)
DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(128)
DECLARE @ObjectID INT
 
SET NOCOUNT ON
 
SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
 
-- Check that the provided object exists in the database.
IF @ObjectID IS NULL
BEGIN
 RAISERROR('The object name or schema provided does not exist in the database', 16, 1)
 RETURN
END
 
-- Check that the provided object is encrypted.
IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)
BEGIN
 RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)
 RETURN
END
 
-- Determine the type of the object
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL
 SET @ObjectType = 'PROCEDURE'
ELSE
 IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL
  SET @ObjectType = 'TRIGGER'
 ELSE
  IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL
   SET @ObjectType = 'VIEW'
  ELSE
   SET @ObjectType = 'FUNCTION'
 
-- Get the binary representation of the object- syscomments no longer holds
-- the content of encrypted object.
SELECT TOP 1 @ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
        AND valclass = 1 AND subobjid = 1
 
SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2
 
-- We need to alter the existing object and make it into a dummy object
-- in order to decrypt its content. This is done in a transaction
-- (which is later rolled back) to ensure that all changes have a minimal
-- impact on the database.
SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + 
@ObjectName + N'] WITH ENCRYPTION AS'
 
WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength
BEGIN
        IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength
                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
        ELSE
                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 
@ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))
END
 
-- Since we need to alter the object in order to decrypt it, this is done
-- in a transaction
SET XACT_ABORT OFF
BEGIN TRAN
 
EXEC(@ContentOfFakeObject)
 
IF @@ERROR <> 0
        ROLLBACK TRAN
 
-- Get the encrypted content of the new "fake" object.
SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
        AND valclass = 1 AND subobjid = 1
 
IF @@TRANCOUNT > 0
        ROLLBACK TRAN
 
-- Generate a CREATE script for the dummy object text.
SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' 
+ @ObjectName + N'] WITH ENCRYPTION AS'
 
WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength
BEGIN
        IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength
                SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
        ELSE
                SET @ContentOfFakeObject = @ContentOfFakeObject + 
REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))
END
 
SET @i = 1
 
--Fill the variable that holds the decrypted data with a filler character
SET @ContentOfDecryptedObject = N''
 
WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength
BEGIN
        IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength
                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)
        ELSE
                SET @ContentOfDecryptedObject = @ContentOfDecryptedObject 
+ REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2))
END
 
WHILE @i  (@i + 1)*2000
BEGIN
 PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))
 SET @i = @i + 1
END
 
-- Now print the last chunk, or the only chunk
-- (if @ContentOfDecryptedObject does not exceed 2K characters)
PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))
 

 Decrypten_SPs

Check Also

Book Review: Unlocking the Power of DAX: A Deep Dive into Marco Russo’s Definitive Guide

The Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, …

Geef een reactie

Je e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *