How to Decrypt Stored Procedure in SQL Server 2008

How to decrypt stored procedure in SQL Server 2008

The SQL Server Pro article "Decrypt SQL Server Objects" still works in SQL Server 2008.

You need to connect via the DAC. See the file "Decrypt SQL 2005 stored procedures, functions, triggers, views.sql" in the download.

Just to summarise the steps that it performs for the following stored procedure definition

CREATE PROC dbo.myproc
WITH ENCRYPTION
AS
SELECT 'FOO'

1. Retrieves the encrypted object text from the imageval column in sys.sysobjvalues and stores it in a variable @ContentOfEncryptedObject
2. Calculates @ObjectDataLength from DATALENGTH(@ContentOfEncryptedObject)/2.
3. Generates an ALTER PROCEDURE statement padded out to the correct length with the - character (so in this case ALTER PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS------------)
4. Executes the ALTER statement, retrieves the encrypted version from sys.sysobjvalues and stores that in the variable @ContentOfFakeEncryptedObject then rolls back the change.
5. Generates a CREATE PROCEDURE statement padded out to the correct length with the - character (so in this case CREATE PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS-----------). This gets stored in the variable @ContentOfFakeObject

It then loops through for @i = 1 to @ObjectDataLength and decrypts the definition a character at a time using the following XOR calculation.

NCHAR(
      UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
      (
          UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^
          UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
      )
     )

UPDATE

Paul White has written a very nice article that goes into details on
why the above works, and that gives an alternate method that doesn't
rely on altering the object: The Internals of WITH ENCRYPTION

Anyway to decrypt an encrypted sql server stored procedure?

Or for free (Google is your friend):

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869_mem1,00.html

Create a Stored Procedure for AES Encryption in MS SQL Server 2008

Okay, so I managed to convince the DBAs to have the data transferred to a new column with varbinary(max) data type. Then I transferred the values into this new column after encrypting them, and then dropped the older column and renamed the new one to the old one's name. Took some work, but everything is running smoothly now. I managed to create a stored procedure and two functions to further modularize the scripts.

For opening the symmetric key

CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        OPEN SYMMETRIC KEY Encryption_Symmetric_Key  
        DECRYPTION BY CERTIFICATE Encryption_Certificate 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

For encrypting:

CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  
    SET @Result = EncryptByKey(Key_GUID('My_Encryption_Symmetric_Key'), @ValueToEncrypt)  
    -- Return the result of the function  
    RETURN @Result  
END

For decrypting:

CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  
    -- Return the result of the function  
    RETURN @Result  
END

For inserting

exec sp_OpenEncryptionKeys  
INSERT INTO tbl VALUES ('Name', Encrypt('some text here'))

For fetching

exec sp_OpenEncryptionKeys  
SELECT ID, Decrypt(LongText) from tbl

Hope this helps someone.

---

Related Topics