Decrypt Stored Procedures, Views and Triggers

  • Comments posted to this topic are about the item Decrypt Stored Procedures, Views and Triggers

  • Hi All

    here is my version of a script that ENCRYPTS all stored procedures:

    http://www.sqlservercentral.com/scripts/WITH+ENCRYPTION/65041/[/url]

    Enjoy!

  • Used the stored procedure to decrypt another stored procedure of mine. It dropped it.

    Caveat emptor.

  • 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.

  • 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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply