In work I have to use proprietary software, some days ago my boss asked me if I could decrypt some stored procedures in MS SQL 2005, a person whose doesn’t works for the company anymore decided encrypt some essentials stored procedures, so I decided take the challenge

Here’s a little resume how the problem was solved:

First of all is ensure that the DAC option is enabled

Since DAC cannot be accessed via network, you must access locally, you can do this putting ADMIN: before the database address and instance (e.g. admin:myserverdefaultinstance), then you must logon in the regular way (DAC don’t allow the object browser, so you’re alone) after this, simply go to the database where the stored procedures are located and execute the following script:

DECLARE @ObjectOwnerOrSchema NVARCHAR(128)
DECLARE @ObjectName NVARCHAR(128)

SET @ObjectOwnerOrSchema = 'dbo'
SET @ObjectName = 'NameOfTheStoredProcedureHERE'

DECLARE @i INT
DECLARE @ObjectDataLength INT
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
DECLARE @ContentOfDecryptedObject VARCHAR(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('El nombre del objeto provisto no existe en la base de datos.', 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('El objeto provisto existe, sin embargo no esta encriptado. Abortando.', 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 + 8000 < @ObjectDataLength
SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 8000)
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 + 8000 < @ObjectDataLength
SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 8000)
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 + 8000 < @ObjectDataLength
SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 8000)
ELSE
SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2))
END

WHILE @i BEGIN
--xor real & fake & fake encrypted
SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,
NCHAR(
UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
(
UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^
UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
)))

SET @i = @i + 1
END

-- PRINT the content of the decrypted object
select substring(@ContentOfDecryptedObject, len(@ContentOfDecryptedObject) - 1000, 1000)

-- PRINT the content of the decrypted object
PRINT(@ContentOfDecryptedObject)

and that it’s I hope this can be useful to someone.

Best Regards!!