Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

By:   |   Updated: 2013-05-30   |   Comments (17)   |   Related: 1 | > Encryption


Problem

You work in a shop that puts business or application logic in the SQL Server using stored procedures, views and functions to return values to the calling applications or perform tasks. This is not unusual in companies that use the SQL Server layer to perform business tasks, such as finance operations, or incorporate application functionality into the programmability layer. You wish to preserve secrecy on some procedures, views or functions in order to maintain security.

Solution

SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery. The contents are not able to be scripted using conventional means in SQL Server Management Studio; nor do the definitions appear in the definition column of sys.sql_modules. This allows the cautious DBA to keep stored procedures and functions securely in source control and protecting the intellectual property contained therein. This tip will focus on encrypting and decrypting a user-defined function.

Encrypting a UDF

To encrypt a user-defined function, simply add WITH ENCRYPTION to the CREATE FUNCTION statement (after the RETURNS element). Throughout this tip, I will be building an encrypted UDF (and decrypting it) to demonstrate the principle.

First, create the UDF. Here's mine - it's a simple module that accepts an input string and returns the encrypted varbinary hash value. There are two vital pieces of information here that MUST NOT be given away to the user of the function - the encryption standard, and the salt.

CREATE FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000)
AS BEGIN
    DECLARE @salt VARCHAR(32)    
 DECLARE @outputHash VARBINARY(8000)
 SET @salt = '9CE08BE9AB824EEF8ABDF4EBCC8ADB19'
 SET @outputHash = HASHBYTES('SHA2_256', (@inputString + @salt))
RETURN @outputHash
END
GO

In this example the salt is fixed and an attacker, given the encryption standard (SHA-256) and the salt, could be able to decrypt the hash into plaintext. We can view the definition of a function by finding it in SQL Server Management Studio, right-clicking and scripting out the function:


mssqltips 290413 1

The attacker can also use sp_helptext, or query sys.sql_modules if he/she knows the function name:

SELECT definition
FROM sys.sql_modules 
WHERE definition LIKE ('%getHash%')

There is some protection built in; by using role-based security or sensibly allowing the least required privileges to users, the attack surface can be lessened as the VIEW DEFINITION permission is required (or ownership of the function) in SQL Server 2005 upwards. Note in earlier versions, this permission is not required. The user may also have the permission granted implicitly by holding other permissions on the object. (See 'Metadata Visibility Configuration' at http://msdn.microsoft.com/en-GB/library/ms187113.aspx for more detail).

We can amend the function definition like so:

ALTER FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000) WITH ENCRYPTION 
-- rest of function here

Note that WITH ENCRYPTION occurs after RETURNS, not before. With stored procedures, the WITH ENCRYPTION option occurs immediately after the CREATE PROCEDURE x ( @somevar) statement.

With our encrypted function we can attempt to script it out in SQL Server Management Studio again, or look at sys.sql_modules. Here's what we get:


mssqltips 290413 2

Querying sys.sql_modules definition column for this function returns NULL. Executing sp_helptext returns the error:

The text for object 'dbo.getHash' is encrypted.

Note the UDF is exactly as effective as it was before - we can still call it and return a value:


mssqltips 290413 3

I'm using an undocumented stored procedure here called fn_varbintohex to convert the VARBINARY output from my function into a hexadecimal format, for portability between applications and clarity - it's not directly relevant to this example. Normally the VARBINARY output of HASHBYTES is passed directly to the calling application.


Decrypting a Function

Firstly, open a Dedicated Administrator Connection (DAC) to SQL Server. Using SQL Server Management Studio, this is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option. Note: DAC won't work unless you're using TCP/IP; you'll get this rather cryptic error (in both SQLCMD and SSMS):

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish 
connection because an error was encountered during handshakes before login.  Common 
causes include client attempting to connect to an unsupported version of SQL Server, 
server too busy to accept new connections or a resource limitation (memory or 
maximum allowed connections) on the server.

If you can't access the server directly for whatever reason, you can enable remote administrative connections (a remote DAC) as follows. You'll still need to use TCP/IP:

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

The procedure for decryption comes in three steps. First, get the encrypted value of the procedure definition from sys.sysobjvalues (via the DAC connection). Secondly, get the encrypted value of a 'blank' procedure, where the definition is filled in by '-'. Thirdly, get the plaintext blank procedure statement(unencrypted). Now XOR them together (XOR is the simplest of decryption procedures and forms the basis of many algorithms including MD5) as shown below to retrieve the output of the procedure. You'll find my take on this algorithm in the code example below:

-- Connect using the DAC then execute the below

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )

SET @procedureHeader = N'CREATE PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

If you still cannot access via DAC or prefer a code-based approach, then you can use one of a number of freeware third-party .NET-based decryption software packages to do this for you. The blog Sqljunkieshare also purports to have a method of doing this (code untested) that looks viable - you can find the link here:

http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/ (use at your own risk)

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Derek Colley Dr. Derek Colley is a UK-based DBA and BI Developer with more than a decade of experience working with SQL Server, Oracle and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2013-05-30

Comments For This Article




Friday, July 21, 2023 - 7:45:46 AM - Seb Back To Top (91416)
Hi, but how decrypt table-valued function?

Tuesday, February 9, 2021 - 1:22:46 PM - aalex Back To Top (88199)
Please be warned that the provided script will execute an ALTER command that will overwrite your SPs contents with dashes (------)
These lines should be commented out or removed to avoid catastrophe:
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader

Friday, November 27, 2020 - 11:45:13 AM - Claire Back To Top (87857)
Derek, I couldn't get the code to work from original article. But your script works wonder. Thank you!!

Tuesday, October 2, 2018 - 10:15:56 AM - Kev Back To Top (77783)

Thanks a lot Drew, this was really useful!

One additional tip - so it also works for stored procedures longer than 8000 characters, need to alter each:

REPLICATE('-',  ...
to
REPLICATE(CAST('-' AS NVARCHAR(MAX)),  ...

Else the REPLICATE function truncates the string to 8000 characters.
That was a bugger to debug!

HTH
Kevin

Friday, May 4, 2018 - 2:12:26 PM - Drew Back To Top (75868)

I created a script based on the information found in the post by Joseph Gamma. Here it is. It works for SPs, Scalar Functions, Views and Triggers.

/*
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

Monday, April 2, 2018 - 1:04:09 AM - Kal Youssef Back To Top (75574)

Dear Derek

Thank you for the post

Is there a way to encrypt all stored procedures say per database? Or all views inside a database? Or is the with ENCRYPTION per object?

 

have a good day

Kal

Thursday, April 27, 2017 - 10:21:09 AM - Greg Robidoux Back To Top (55244)

Hi Brenda,

take a look at this tip for information on the DAC - https://www.mssqltips.com/sqlservertip/1801/enable-sql-server-dedicated-administrator-connection/

Thanks
Greg

Thursday, April 27, 2017 - 9:36:41 AM - brenda Back To Top (55241)

 How do you do this?

Firstly, open a Dedicated Administrator Connection (DAC) to SQL Server. Using SQL Server Management Studio, this is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option. Note: DAC won't work unless you're using TCP/IP; you'll get this rather cryptic error (in both SQLCMD and SSMS):

Tuesday, July 19, 2016 - 7:14:29 AM - Mizanur Rahman Back To Top (41915)

 Dear Author,

 

i have some inportant stored procedure code in my sql server database 2012. i need very badly to lock the code or encryted those code.Even i cal also not to able to view the code even not SA user. how i can do this. please assist me.

Remember, if i do encryption the code, then very easy to decryption the code, i dont want this.

Regards

Mizan

 

Wednesday, August 5, 2015 - 12:49:03 PM - JC Back To Top (38385)

Thank you for the great tips.

Have one question regarding the decryption though.

I came acrossed few stored procedures that were encrypted by previous developers that were no longer here by adding 'with encryption' in the store procedures.

Is there a way to decrypt the stored procedures at all? (without any keys or some sort).

 

Thank you.

 

Saturday, July 25, 2015 - 8:00:19 PM - Peter Back To Top (38291)

Script for decrypting function works great!  However, can you modify the code to ROLLBACK the transaction so that the stored procedure is reverted back to normal?  Otherwise after running the code I'm left with

CREATE PROCEDURE dbo.procedure WITH ENCRYPTION AS ----------------

I can just copy the decrypted code and paste it again to ALTER it to get it back but it's an extra step.

Thanks!

Saturday, July 5, 2014 - 4:05:12 AM - satya thakur Back To Top (32555)

 

THANKS FOR HELPING USER LIKE US.............

Wednesday, March 12, 2014 - 9:58:52 AM - Jason Back To Top (29726)

Brilliant. I was using Theo Ekelmans' sp since many years ago. It worked well.

Friday, July 12, 2013 - 12:17:00 AM - suryam raju Back To Top (25807)

Dear sir / Madam,

 

                  Good Morning...

   I have some doubts and please clarify me as early as possible why because working as a database developer in a   private limited company.

Questions: 

1. What is the Use of co - related subqueries.

2. Can i Write one trigger on all datbase tables, If yes how can i do that.

3. Use of with check option in creating views.

 

Tuesday, June 4, 2013 - 10:07:50 AM - Derek Colley Back To Top (25276)

*** TIP UPDATED *** I have written a correction to the original tip as per the comments above.  Apologies for any confusion and thanks to James for pointing out the omissions.

Thursday, May 30, 2013 - 9:51:53 AM - Derek Colley Back To Top (25198)

@James Lean - Thanks for your comments.  I've re-reviewed what I've written about the DAC and I'm a bit confused to be honest, as I cannot now replicate the last steps in the article above to read the decrypted data.  I also get NULL values, same as you.  Looking elsewhere,  I can see details of the decryption algorithm used (XORs character-by-character of the cryptotext to get the plaintext) via the DAC but no evidence the DAC itself is the key.

If this is the case then the final points I made about using the DAC are incorrect and I apologise for this (I'm also baffled as to how I got the plaintext out in my screenshot!).  I would highly recommend the StackOverflow link above for details of the algorithm required for decryption.

 

Thursday, May 30, 2013 - 5:33:36 AM - James Lean Back To Top (25193)

I may be missing something, but I can't get this to work just by using the DAC to view the encrypted definition.  I just get a NULL definition, same as if I use a normal connection (I've tried this on SQL2005 and 2012 just in case the behaviour changed).

To be honest, I would be a bit worried if it *was* this easy to view an encrypted definition.  I know it is fairly trivial to use third-party tools anyway, but at least you have to go to a little bit of effort that way ;-)














get free sql tips
agree to terms