SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions

This article is written to answer following two questions I have received in last one week.

Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?

Answers 1) Use WITH ENCRYPTION while creating Stored Procedure or User Defined Function. 2) Sorry, unfortunately there is no simple way to decrypt the code. Hard way is too hard to even attempt.

Explanations of WITH ENCRYPTION clause If SP or UDF are created WITH ENCRYPTION it is one way street and it is not possible to decrypt it using SQL Server commands. It is always advised to save a copy of the script used to create the SP or UDF on other media than SQL Server. There is no way to get the original source code once it is executed on Server. Only privileged users who can access system tables over the DAC port or directly access database files as well as can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

CLR SP and UDF can not be encrypted. SQL Server replication can not replicate encrypted SP or UDF.

I believe in fair programming techniques and strongly recommend against encryption. There should be no need to encrypt any code. If there is need to hide any code from certain users in that case user should be restricted using user login permissions.

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

About these ads

37 thoughts on “SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions

  1. Hi,
    I am working on SQL 2000 n 2005,Can you plz send me the major differences b/w them. And when will the cold fusion be applicable..Wat kind of scenarios

  2. Answers
    1) Use WITH ENCRYPTION while creating Stored Procedure or User Defined Function.
    2) Sorry, unfortunately there is no simple way to decrypt the code. Hard way is too hard to even attempt.

    ————————————————

    No2, I have a simple code for this, I’m not sure about Micro$oft Security.

    In fact, I’ve spend alot of money for this buG.

  3. Well, in my opinion. Instead of encrypt ur stored with FULL and CLEAR code, you need have a “key unlock” column, and a “indexed filter” column. Use “key column” with random algorithms on “indexed filter column” when you do criteria thing. Remember “RANDOM”.

    Problem IS: how to genarate Key Col and Indx Col? Depend your decide.

    Sorry about my bad English.

  4. In fact, due to a flaw in how SQL Server encrypts an altered procedure, decrypting the original procedure text is easy.

    SQL Server uses the RC4 cipher algorithm to encrypt the procedure text. This algorithm generates a stream of key data (called the keystream) from a starting key, then encrypts the plaintext by using the XOR operation. The beauty of the XOR operation is that to reverse it, you simply XOR the ciphertext with the keystream.

    Unfortunately, when you use ALTER PROCEDURE, SQL Server encrypts the new text using the same key as it did when encrypting the original text. This means it uses the same keystream. To recover the keystream when you know the ciphertext and the plaintext, you can simply XOR the ciphertext and plaintext together. You can then recover the original plaintext by XORing the recovered keystream with the original ciphertext.

    You can find copies of scripts that do this in numerous places, for example at http://education.sqlfarms.com/education/ShowPost.aspx?PostID=783.

    I’m not sure how SQL Server decides what the encryption key should be – it may be as simple as deriving a key from the procedure’s objectid.

    SQL Server 2005′s documentation describes the result as ‘obfuscated text’ rather than ‘encrypted’.

  5. Hello,

    There is a tool which can decrypt the encrypted stored procedure. Is there any way with which we can restrict decryption of stored procedures? In my situation the database would be in the client side and I would like to enrypt the Stored procedure. Please help.

    Thanks,
    Abhilash

  6. Hai
    can anyone tell me how to create a stored procedure with an encryption and password to decrypt it back again when ever requrired.

  7. Hai
    can anyone tell me how to create a stored procedure with an encryption and password to decrypt it back again when ever requrired.

  8. how to protect table from viewing its data and columns in sql server 2005, we can encrypt SP’s and UDF, what abt table, i read an article on this but its for data encryption in table, but i want to encrypt table columns itself, i want to restrict the user from seeing its design

    • Hi Giri,

      Did you get any solution for encrypting table structures (not table data)? Please forward me here.

      Thanks in Advance.

  9. Hello Krishna,

    In user database set the user’s property to remove the View Definition permision on selected or all stored procedures. You can also remove the Execute permission on stored procedures. For this go to database > Security > Users > right click a user > Properties > Securables > here add object and set the permissions on that object.

    Regards,
    Pinal Dave

  10. Hi Pinal,

    Only privileged users who can access system tables over the DAC port or directly access database files as well as can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

    Is that mean administrator can view the decrypted procedure? Is there any way to hide or prevent from retrieving the decryted script from the administrator?

    I also read on some articles that there are some softwares to decrypt the encrypted SQL scripts. Can we prevent such softwares from decrypting the encrypted SQL scripts?

  11. Hi Pinal Dave,
    If i create one encrypted SP then please tale me how to give permission to selected login user like sa or other user which i created

  12. Hi Pinal Dave,
    I understand the risk of Encrypting the sp’s, but As i am experiencing the security risk of my database as it can be easily copied to other system and get attached to another instance, I feel need of encryption. Even creating the user won’t help in these conditions.
    Though i am new to MS SQL, I strongly fee that MS should incorporate the user specific database files for strong security of the database.

  13. Hello guys,
    We strongly recommend NOT to use WITH ENCRYPTION option, because as easy to encrypt, as easy to decrypt. Unfortunatelly, SQL Server uses database’s GUID and object’s ID as a key for deprecated RC4, and then simple byte-wise XOR. We hope Microsoft SQL Server’s team will eliminate this flaw or just desist support this option. As a solution we provide our FREEWARE named SQL Decryptor. Check it out on our web-site.

  14. Well, it’s pretty easy. Run Optillect SQL Decryptor Freeware, decrypt a stored procedure you need, and copy the DDL script. Then run Microsoft SQL Server Management Studio, create new SQL editor with the same connection, paste the DDL script from the clipboard. Now remove “WITH ENCRYPTION” option, replace “CREATE” keyword with “ALTER” and execute the script. Thus you’ll get your stored procedure stored with no ecryption.
    Unfortunately, SQL Decryptor v1.1 cannot do it automatically at the moment, but we are planning to support this feature in the next release.

  15. hi

    i was working on SQL SERVER DBA

    i faced one problem from ENCRYPTED STORED PROCEDURES

    HOW TO RETRIEVE AND HOW TO READ THE ENCRYPTED STORED PROCEDURE FROM SQL DATABASE

    THANK U

  16. I have onlin exam how i do encrypt /decrypt database files or tables like ( question , answer . and result …etc) please can you explenation me step by step

    thanks

  17. is there any to get the SP main query part only…for example

    create proc getinfo
    (
    @id int
    ) select *from infotable where id=@id

    we can the sp parametres and data type with help of sys tables…but how i can get the sp main script part i.e. select *from infotable where id=@id only???

  18. Update Optillect decrypter was able to decrypt the SQL server 2008, 2008r2,

    encrypted

    when I tried to use on SQL server 2012 RC0 it is giving me an error

    Error : No Object definition found

    I guess the SQL team might have changed the encryption algorithm for the new

    sql server 2012

  19. akhil393,
    Another update, Optillect’s web site is not available any more. Would it be possible fou you to send me the tool?

  20. Hi
    As you said that “With Encryption” cannot be decrypted when i use alter command to modify sp without using “With Encryption” in that alter command then definitely it will remove the encryption for that sp.
    This may cause Readable of stored procedure…
    The code which i used for Encryption is

    create proc proc123
    With Encryption
    As
    Begin
    select Title as [Name of Song],director as [Director] from videos
    End
    Go
    —–Encryption is done here—-
    *Alter proc proc123

    As Begin
    select * from videos
    End
    Go
    *
    —–Returned
    Please give me another way to use the encryption for UDF and SP’s.

  21. Hi
    I have few views which is encrypted in my database , is there any way to open and alter the view. or I should Drop and recreate the view,
    Pls Do Help me in this

  22. Hello Pinal,

    We are hosting our DB at our client’s machine in their premise. They have full access of the database, which we cannot stop. In this case, can we hide SP code and trigger code?
    Thanks,

  23. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

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