SQL SERVER – Stored Procedure WITH ENCRYPTION and Execution Plan

Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP are encrypted it is not possible to get original text of the SP from SP itself. User who created SP will need to save the text to be used to create SP somewhere safe to reuse it again.

Interesting observation:
What prompted me to write this article is when SP are encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command and does not display the tab of execution plan if only Encrypted SP is called.

To understand this scenario let us create two SPs. First without encryption and second with encryption. Once SPs are created run them one by one with having Actual Execution Plan turned on. To learn how to turn on Actual Execution Plan read my previous article SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan.

/* Create SP without Encryption */
CREATE PROCEDURE #RegularSP
AS
SELECT TOP
10 City
FROM Person.Address
GO
/* Create SP with Encryption */
CREATE PROCEDURE #EncryptSP
WITH ENCRYPTION
AS
SELECT TOP
10 City
FROM Person.Address
GO
/* Execute SP - Execution Plan Tab shows up */
EXEC #RegularSP
GO


/* Execute SP - Execution Plan Tab does not shows up */
EXEC #EncryptSP
GO


/* Clean Up */
DROP PROCEDURE #RegularSP
DROP PROCEDURE #EncryptSP
GO

It is very clear from above example that Encrypted SP does not show the actual execution plan as from Actual Execution Plan user can figure out the logic behind the SP. To avoid this issue for encrypted SP Management Studio does not display the execution plan.

Reference : Pinal Dave (http://www.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – Stored Procedure WITH ENCRYPTION and Execution Plan

  1. Pinal,
    Thanks for this Tip.

    Worth noting is, this concept works only with SQL Server 2005. I tried doing this in SQL Server 2000. Even if you include With encryption in your stored proc, in SQL Server 2000 you will be able to see the execution plan. Since you did not mention this in your article I thought it will work in both.

    @mdma

    Main Idea behind using with encryption is to hide the SQL Code, not the execution plan. Like Pinal mentioned in his article, many business logics are written in stored procedures which companies do not want others to know. This is the case especially with COTS products.

    In SQL Server 2005 SSMS ( Object explorer ), you can see a Lock symbol on stored procedure name indicating this stored procedure is encrypted.

    Thanks,
    Imran.

    Like

  2. Hi Pinal,

    I would like to know the performance impact of using temp tables in stored procedures. I believe temp table cause SP recompilation.

    I would like to why and when this causes the stored procudre to recompile? I would also like to know the improvement in SQL 2005.

    Thank you.

    Like

  3. Hi All,
    There are decrypters available in Net to decrypt all our Encrypted SPs..So how to make our logic protected from those decrypters..we have to use our own encription algo (:'( ) or any loop holes are there ?? any idea?

    Like

  4. Hi,

    I am having about 1000 store procedure and view.
    Is there any way to generate store procedure and view objects encrypted script?

    Thx in advance

    Vinay K

    Like

  5. Where are these SPs stored? I created them in the AdventureWorks db and was able to run them, however when I looked in the AdventureWorks\Programmability\Stored Procedures directory, neither SP was there.

    Also, what are your thoughts on creating an encrypted SP that queries encrypted data? Is this a good/bad way to keep the keys and certificate hidden?

    Thanks for your help.

    Like

      • Well these stored procs in this example were created with a hash in the front of the name, therefore they are TEMP stored procs, just like temp tables. That’s why you can’s see them in the Stored Procedures list on the server

        Like

  6. Hi,
    I would like add one more question here how SQL server internally handle this ?
    what are the performance constraint ?

    although i found many articles that it is not safe enough it can easily be decrypted so is there any other way?

    Thanks
    Rajat

    Like

  7. Hi,
    Good example, But if i know the SP name(EncryptSP) . Use this name, I can able to modify the SP. So, What is the use of ENCRYPTION.

    Like

  8. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  9. Pingback: SQL Server encryption features in SQL Server 2014 | Malaysia Software Reseller | Dealer | PCWare2u

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s