Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Encrypting SQL Code Expand / Collapse
Author
Message
Posted Thursday, April 9, 2009 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 7:43 PM
Points: 33,046, Visits: 15,152
Comments posted to this topic are about the item Encrypting SQL Code






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #694571
Posted Thursday, April 9, 2009 9:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 3, 2014 1:19 PM
Points: 1,035, Visits: 409
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



*****************/
Post #694592
Posted Friday, April 10, 2009 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 19, 2009 12:34 AM
Points: 1, Visits: 11
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.
Post #694640
Posted Friday, April 10, 2009 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 7,001, Visits: 8,438
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".


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #694646
Posted Friday, April 10, 2009 1:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 5:23 AM
Points: 197, Visits: 152
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.


Post #694651
Posted Friday, April 10, 2009 1:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 10, 2012 2:08 AM
Points: 111, Visits: 37
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.
Post #694655
Posted Friday, April 10, 2009 2:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 1,022, Visits: 1,091
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.

Andy
Post #694657
Posted Friday, April 10, 2009 3:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 24, 2011 8:19 AM
Points: 340, Visits: 85
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.
Post #694671
Posted Friday, April 10, 2009 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 11:31 AM
Points: 3, Visits: 15
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....
Post #694672
Posted Friday, April 10, 2009 3:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 10:59 AM
Points: 118, Visits: 223
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.
Post #694695
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse