locked
How To encrypt and decrypt objects in sql server 2005 RRS feed

  • Question

  • Hi,

       I have Tried to encrypt stored procedures by "with encryption" but that was decrypted easily by third party tool

       i need to encrypt in such a way, even clientside DBA is not able to decrypt  


    Who Care! Who Regret! Live Every Moment Of Life.
    Friday, May 28, 2010 10:04 AM

Answers

  • Yogesh,    Sorry that you do not like the answer, but it is still correct.   Of course, writing everything in the CLR makes it a little bit harder, but as Gavin said, there are tools to undo that as well.  

    The only way to keep your stored procedures truly locked is to run the server at your own site and give no administrator rights to any of your clients.  Then you are in full control of access the SQL Server. 

    Of course, you then become a hosting site, if that is a job you want to manage.

    RLF

    Monday, May 31, 2010 12:42 AM

All replies

  • Sorry, but there is no way to do so. 

    The "encryption" needs to be decrypted by the SQL Server query engine in order to be able to execute the code, so "encryption" is actually only "obfuscation".  And what the SQL Server engine can do, someone else can also do.

    You need to view encryption as an obstacle to casual browsing, but not as a wall that prevents it completely.  If you are concerned with the customer making changes to your code, then work that out in the contract / support plan that you have them sign.

    RLF

    Friday, May 28, 2010 2:00 PM
  •  

    With Encryption option is not encryption just hiding.

    But that is showed by 3-Party tools.

    If you want to hide stored procedure logic. you can use SQL CLR.

    you can create SP using .NET and hide logic.

     

     


    Hong-ju
    Sunday, May 30, 2010 9:32 AM
  • If the client-side DBA cares enough, he could use Redgate .NET Reflector to look at your CLR code. If your CLR code interacts with the database, he could use SQL Trace to capture this interaction.

    You should think carefully about what you are really trying to protect here.

    Sunday, May 30, 2010 9:03 PM
  • Yogesh,    Sorry that you do not like the answer, but it is still correct.   Of course, writing everything in the CLR makes it a little bit harder, but as Gavin said, there are tools to undo that as well.  

    The only way to keep your stored procedures truly locked is to run the server at your own site and give no administrator rights to any of your clients.  Then you are in full control of access the SQL Server. 

    Of course, you then become a hosting site, if that is a job you want to manage.

    RLF

    Monday, May 31, 2010 12:42 AM
  • Yogesh,    Sorry that you do not like the answer, but it is still correct.   Of course, writing everything in the CLR makes it a little bit harder, but as Gavin said, there are tools to undo that as well.  

    The only way to keep your stored procedures truly locked is to run the server at your own site and give no administrator rights to any of your clients.  Then you are in full control of access the SQL Server. 

    Of course, you then become a hosting site, if that is a job you want to manage.

    RLF

    thnx Russell,

    I just want to know that there is any way to encrypt/decrypt the store procedure..I have learn and implement the encryption and decryption for data. But I want to know about for store procedure.

     

    Now i realize that even CLR is not given 100% guarantee.

    Even though i make this thread to know other alternative if any.


    Who Care! Who Regret! Live Every Moment Of Life.
    Monday, May 31, 2010 4:27 AM
  • How can I decrypt an encrypted sp?

    MJ_Developer.
    Monday, June 7, 2010 8:40 AM
  • Depends on your version of SQL Server, of course, but here was an article from SQL Server magazine. 

    http://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects.aspx

    Or do an internet search for "SQL Server decrypt stored procedure" and see what you find.

    RLF

    Monday, June 7, 2010 12:23 PM