October 3, 2007 at 10:57 am
Comments posted to this topic are about the item Decrypt Stored Procedures, Views and Triggers
August 30, 2009 at 12:11 am
May 25, 2011 at 8:37 am
Used the stored procedure to decrypt another stored procedure of mine. It dropped it.
Caveat emptor.
March 28, 2012 at 2:24 pm
It also dropped my test stored procedure. It also gave me errors stating that thjere were more than one row returned for SET @a=(SELECT ctext FROM syscomments WHERE id = object_id('proc_LETTER_SERIES')). It also did not put quotes around the stored procedure, so I manually tried to run the steps after correcting the syntax. That's when it dropped the original and never created the decrypted procedure.
May 4, 2018 at 12:16 pm
I created this script based in part on the content found on this site. Enjoy.
/*
Adapted from a stored proc called DECRYPT2K by author Joseph Gamma (http://www.sqlservercentral.com/scripts/SQLInsider+Scripts/30622/)
Also used this http://www.itprotoday.com/microsoft-sql-server/decrypt-sql-server-objects
and this https://www.mssqltips.com/sqlservertip/2964/encrypting-and-decrypting-sql-server-stored-procedures-views-and-userdefined-functions/
to make modifications to the original script.
You need to connect using DAC (Dedicated Admin Connection) to access sys.sysobjvalues.
DAC info here: https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
Created By: Drew Holloway
Date: 5/4/2018
Purpose: decrypting triggers, views, functions, and stored procedures
Notes: This script can be converted to a stored procedure if you find that more helpful.
I've added in the ability to decrypt functions, but only scalar functions have been tested at this point.
Parameters:
@objName: Name of object. Functions, views, and stored procedure names should be in the format schema.objName. But triggers exclude the schema
and are just in the format triggerName.
@type: 'F' for function, 'V' for View, 'S' for Stored Procedure, and 'T' for Trigger.
@printOutput: 1 for true. Turns on Print statements
*/
DECLARE @objName VARCHAR(50), @type CHAR(1), @printOutput BIT
--SET @objName = '[dbo].[!TestFunction]'
--SET @Type = 'F'
--SET @objName = 'dbo.[!TestView]'
--SET @Type ='V'
SET @objName = 'TestTriggerEncrypted'
SET @type = 'T'
--SET @objName = 'dbo.uspMyProc'
--SET @type = 'S'
SET @printOutput = 1
DECLARE @encryptedText NVARCHAR(MAX)
, @bogusObjectEncryptedText NVARCHAR(MAX)
, @alterBogusObject NVARCHAR(MAX)
, @createBogusObject NVARCHAR(MAX)
, @decryptedText NVARCHAR(MAX)
, @i INT
, @tablename VARCHAR(255)
, @encryptedTextDataLength INT
, @decryptedChar NCHAR(1)
IF NOT EXISTS (SELECT * FROM SYS.objects WHERE object_id = object_id(@objName))
BEGIN
PRINT @objName +' cannot be found in the sys.objects table'
RETURN
END
IF EXISTS (SELECT * FROM sys.sql_modules WHERE definition IS NOT NULL AND object_id = OBJECT_ID(@objName))
BEGIN
PRINT @objName +' is not encrypted'
RETURN
END
SET @type=UPPER(@type)
IF @type='T'
BEGIN
SET @tablename=(SELECT sysobjects_1.name
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
IF @printOutput = 1
PRINT '@tablename:' + @tablename
END
SET @encryptedText=(SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@objName)
AND imageval IS NOT NULL) --sys.sysobjvalues stores the encrypted text for SQL Server 2005 and up.
-- this line of code can be run only from the DAC (Dedicated Admin Connection).
IF @printOutput = 1
PRINT '@encryptedText:' + @encryptedText
SET @encryptedTextDataLength = DATALENGTH(@encryptedText) / 2
IF @printOutput = 1
PRINT '@encryptedTextDataLength:' + CAST(@encryptedTextDataLength AS VARCHAR)
SET @alterBogusObject=case @type
WHEN 'S' THEN 'ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', @encryptedTextDataLength)
WHEN 'V' THEN 'ALTER VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', @encryptedTextDataLength)
WHEN 'T' THEN 'ALTER TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR INSERT AS PRINT ''a'''+REPLICATE('-', @encryptedTextDataLength)
WHEN 'F' THEN 'ALTER FUNCTION '+@objName+' (@param1 int) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN @param1 '
+ REPLICATE('-', @encryptedTextDataLength) + CHAR(13) + CHAR(10) + 'END'
END
EXECUTE (@alterBogusObject)
IF @printOutput = 1
PRINT '@alterBogusObject:' + @alterBogusObject
SET @bogusObjectEncryptedText=(SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@objName)
AND imageval IS NOT NULL) --sys.sysobjvalues stores the encrypted text for SQL Server 2005 and up.
-- this line of code can be run only from the DAC (Dedicated Admin Connection).
IF @printOutput = 1
PRINT '@bogusObjectEncryptedText:' + @bogusObjectEncryptedText
SET @createBogusObject=case @type
WHEN 'S' THEN 'CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', @encryptedTextDataLength)
WHEN 'V' THEN 'CREATE VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', @encryptedTextDataLength)
WHEN 'T' THEN 'CREATE TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR INSERT AS PRINT ''a'''+REPLICATE('-', @encryptedTextDataLength)
WHEN 'F' THEN 'CREATE FUNCTION '+@objName+' (@param1 int) RETURNS INT WITH ENCRYPTION AS BEGIN RETURN @param1 '
+ REPLICATE('-',@encryptedTextDataLength) + CHAR(13) + CHAR(10) + 'END'
END
IF @printOutput = 1
PRINT '@createBogusObject:' + @createBogusObject
SET @i=1
SET @decryptedText = ''
WHILE @i< = @encryptedTextDataLength
BEGIN
/*
xor original encrypted text, with bogus create statement (unencrypted) and bogus object's encrypted text
explanation: we are using the fact that we know what the encrypted and unencrypted text are for the bogus object to
back into the encryption key. Then we are using that encryption key to decrypt the original encrypted text.
This explains why the length of the bogus string must be at least as long as the length of the encrypted string:
so that we can find all of the key values that will be applied to the encrypted string.
Bitwise encryption is easy to use because applying the key once encrypts and applying the same key again decrypts.
more on xor and encryption: https://www.researchgate.net/post/Why_should_we_use_xor_in_encryption_and_decryption_stream_cipher_What_is_the_wisdom_of_using_it
*/
SET @decryptedChar =
NCHAR(
UNICODE(SUBSTRING(@encryptedText, @i, 1)) ^
( UNICODE(SUBSTRING(@createBogusObject, @i, 1)) ^
UNICODE(SUBSTRING(@bogusObjectEncryptedText, @i, 1))
)
)
SET @decryptedText = @decryptedText + @decryptedChar
SET @i=@i+1
END
IF @printOutput = 1
PRINT '@decryptedText:' + @decryptedText
--drop original object
IF @type='S'
EXECUTE ('DROP PROCEDURE '+ @objName)
ELSE IF @type='V'
EXECUTE ('DROP VIEW '+ @objName)
ELSE IF @type='T'
EXECUTE ('drop TRIGGER '+ @objName)
ELSE IF @type = 'F'
EXECUTE ('DROP FUNCTION ' + @objName)
--remove 'WITH ENCRYPTION' substring from encrypted text
--try to preserve case of the the original object's create statement
SET @decryptedText=REPLACE((@decryptedText),'WITH ENCRYPTION', '')
SET @decryptedText=REPLACE((@decryptedText),'With Encryption', '')
SET @decryptedText=REPLACE((@decryptedText),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@decryptedText) )>0
SET @decryptedText=REPLACE(UPPER(@decryptedText),'WITH ENCRYPTION', '')
IF @printOutput = 1
PRINT '@decryptedText:' + @decryptedText
--create object without encryption
EXECUTE( @decryptedText)
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply