Encrypting SQL Code

  • Comments posted to this topic are about the item Encrypting SQL Code

  • Encrypting stored procedures is almost as annoying as it is pointless.


    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek


  • One of our developers decided to encrypt everything before deploying a release several years ago, and even put half the stuff in SourceSafe with 'With Encryption' not commented out. I could have cheerfully strangled him. It's been nothing but a huge bother ever since. As has already been pointed out, anyone savvy enough to want to play with stored procedures and the like can easily find a decryption tool. Meanwhile, we're still getting unwelcome surprises when we check items out that haven't been touched since that release because it's altogether too easy to forget to look and see if 'With Encryption' is there or not and, if present, if it's been commented out. For the life of me, I can't think of a single good reason to encrypt.

  • It is as useful as putting a fence around your house.

    Anyone wanting to walk on your territory will have to do a bit more effort to get there.

    So, maybe they'll think twice before walking on your grass. 😉

    I have implemented some encrypted sprocs because in these procs I use stuff I wouldn't want a developer to use (e.g. sp_OA** stuff to send smtp mail on sql2k ). Just to avoid copy/paste usage without any consideration. Off course these sprocs are documented.

    They may come up using sp_OA themselves, but then it will be rejected and they will have to program another workaround.

    Our dev teams are not allowed to use "with encryption".


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Encryption besides being annoying and pointless, it can also be dangerous if you lose the source code (a malicious attack by a disgruntled employee about to leave). Also I always thought that there must be some performance overhead of encryption/decryption- even if ever so minor.

  • I've seen encryption implemented on a number of stored procedures at places I've worked in the past, but this has rarely ever stopped an intrepid client from trying to edit or examine something themselves. Aside from ensuring that someone cannot easily cut and paste the code into their own procedures or queries, encryption has provided little to no value in the grand scheme of things.

  • A long time ago, I needed to figure out what a DTS package was doing. Unfortunately, the vendor had password-protected the DTS package; ok, not quite the same thing an encryption, but it had the same effect. A change to our network meant that the DTS package was failing, and I couldn't even start to debug it. Frustrating for us, but I guess it provided a certain amount of vendor-lock-in.


  • Absolutely agree, that "If you can deliver a well performing, and good looking application, no one cares about the code."

    But are there a lot of such applications ordered by middle and small size companies?

    I think the absolute majority of applications are tested to "good-enough" state where developers assume maintenance issues.

  • Our firm develops data warehouse solutions for 'off-the-shelf' accounting systems. Our IP is therefore contained in the code that we have developed. The last thing we want is to allow the accounting system developers to simply take our code and incorporate it into their products.

    In addition, we are expected to provide ongoing support for our solutions. We have recently had one well meaning client that has started to play around with the code, causing more problems than trying to solve.

    We are currently looking at encryption tools such as SQL Shield 4 as an effective encryption tool for SQL 2005. I would be interested to read any feedback on such tools....

  • I work for a software vendor and we have both encrypted and non-encrypted procedures in our db. Usually we use it to denote something that is critical to the operation of the UI and should not be changed except by our staff or someone else that has the ability to do so properly (having the ability to get the code out through encryption seems like a good litmus test of this). There are, however, some procedures that we encrypt to help prevent the risk that an employee of the client (or one of our own) decides they could build a nicer front end or enhance the product somewhat and re-brand it as their own with minimal effort. It also helps us to determine what a client may have changed if we are called in for support; when we come in and see a normally encrypted procedure is unlocked, it sends up a red flag to start asking questions about what has been changed. The vast majority of our procedures are not encrypted, possibly only 10% of the standard procedures and virtually none of the implementation-specific customizations.

    Personally, I avoid encryption. If someone else can make it better/faster/more flexible more power to them (that probably sounds arrogant, but it isn't meant to). I keep my source scripts and if necessary I can send them out or re-apply them.

  • Most of the work I do here supports "internal" clients. The parts that support external clients is only accessible via web based reports (CE). The work for the internal clients is rarely encrypted. BUT, there have been instances where encryption was warranted. Mostly to protect the end-users from hurting themselves. And by that I mean, garnering the wrath of our team for their experiments.

    This was implemented after one very nice, and well meaning accountant saw what I was typing and decided it would be nice to report on income and positions, but didn't join the information correctly. The resulting Cartesian set (20M x 21M) brought our production server to a standstill. (These were fairly wide tables, 350+ columns. They've been reworked in the years since.)

    This problem has been mitigated by SQL training classes, and by a more friendly relationship with the departments. Though I still have the reputation as a hostile, petulant @rsehole, my coworkers actually come over occasionally to say hello and ask a question such as "how do I...". They've come up with a nickname that I actually like... I'm Mister Black & White. This came about during a meeting in which I took the position that X is either right or wrong. "Like being pregnant or dead, either you are, or you aren't."

    I agree with the previous posts, for the most part it is unnecessary, but there exist specific circumstances where it is desirable. Not so much to stop a hacker, but to stop the well intentioned coworker. I guess this is analogous to the dead bolt on my doors, they'll slow the intruder. But a determined hacker/intruder can circumvent the protection.

    Best wishes for Passover, Good Friday, NichLactemyer, or whatever holiday it is that you celebrate or not...

    Honor Super Omnia-
    Jason Miller

  • Like being pregnant or dead, either you are, or you aren't.

    have you heard of Schrödinger's cat??? 😛

  • Almost no one cares about this.

    People by applications and use them.

  • At previous employers I worked in Oracle and we used the "Wrap" function to obfuscate (love that word) the code. "Wrap"ped code was only readable by the Oracle runtime engine, I have not seen a hack of it, but there might be. Anyway ... we wrapped or code because we were in a very competitive "new-idea" market. Letting our competitors see how we accomplished what we did would place us at a dis-advantage. Don't now if that was really true, but that was our story.

    Livin' down on the cube farm. Left, left, then a right.

  • AndyD (4/10/2009)

    Like being pregnant or dead, either you are, or you aren't.

    have you heard of Schrödinger's cat??? 😛

    I have, but it was 20 years ago. My progression through physics only took me through 1-4, Modern, and a few that bordered on engineering (Statics, Dynamics, Modeling of Linear and Dynamic Systems, etc)

    With regard to quantum mechanics, I tend to think along the same lines as Einstein. The quantum guys say there is no way to predict the flip of a coin. Einstein would argue there is. IFF you knew all of the forces and assumptions, you could predict the result. I interpret that as saying, we simply don't have enough information, yet.

    It's a similar discussion as the multiple universe, infinitive universe argument.

    Honor Super Omnia-
    Jason Miller

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply