April 10, 2009 at 7:43 am
I see that almost all responses are against encrypting. The few that have done so were to basically try to secure critical code for their system.
My perspective is that even the critical code should not be encrypted. If something is critical to your system and the company running your application is having a problem AND has a knowledgeable person that could investigate but not change anything, they might be able to see what is tripping by looking at how the processes are designed. If it's encrypted then the company is dead in the water until they get support from the vendor who encrypted it. In today's age, this company might be out of business or the person took off that encrypted it.
Folks, I've seen this with chemical instrument vendor in Milwaukee. They sold the business once, lost all of the developers, then someone bought this other company. So all of the knowledge of the design was gone. Your software may survive longer than the company you work for, or you built it for.
The other reason some people encrypt is for more or less job security. I have two words for that - YOU'RE FIRED. The best way to have job security is to be knowledgeable, helpful, and share your experience to help others. Someone who has to squirrel away their knowledge is useless.
April 10, 2009 at 7:47 am
azz (4/10/2009)
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.
You think so? I've worked in small and medium companies, and we've rarely been allowed to touch a 3rd party app. Even changing indexes for performance is frowned upon because of support.
April 10, 2009 at 7:57 am
Steve Jones - Editor (4/10/2009)
You think so? I've worked in small and medium companies, and we've rarely been allowed to touch a 3rd party app. Even changing indexes for performance is frowned upon because of support.
Absolutely. It took an act of god, otherwise known as the Head Legal Counsel, and our corporate checkbook for our investment accounting vendor to allow us 1 minor change... To work-around a bug in the vendor's own code!
Honor Super Omnia-
Jason Miller
April 10, 2009 at 8:03 am
Steve Jones - Editor (4/10/2009)
azz (4/10/2009)
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.
You think so? I've worked in small and medium companies, and we've rarely been allowed to touch a 3rd party app. Even changing indexes for performance is frowned upon because of support.
I work for a software company. We deal with both Oracle and SQL Server databases (fun and games, I can tell you). One time we had a client pay a consultant to tune one of our queries. They did this with something called an Oracle overlay - I never did work out exactly how they did this - that basically waited for a query with a specific signature to be used by our application, then would automatically replace it with their own tuned query.
I was amazed by how much money they spent. It had never occured to them to call us to tell us they were having a problem. When we did find out about it, we of course fixed it - as part of their support agreement. In other words, at no extra cost than what they were already paying.
April 10, 2009 at 8:16 am
I am against encrypting procedures. I understand people being concerned IP rights, but if you are that concerned, put that logic in the application, not the database. The only experience I had with a product that encrypted ALL the procs was negative, because it kept me from helping them debug the code. Yes, I decrypted one proc that was causing a problem, and I made a suggestion based on what I learned, without telling them I had seen the code. It would have been a lot easier for everyone if I could have said, "You have this problem on line X of proc A".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 10, 2009 at 8:26 am
I guess I'm peculiar. To my mind stored procs are for *database* logic, not application logic. Therefore I can't see a need to encrypt your sprocs since that's just obfusticating (and not very well) the design of the data--which is already complicated enough thank you, even following KISS principles.
In other words, to me sprocs are there to support integrity and not much more than that. I like having database logic in the database when the app doesn't need to mess with it, but it's all more or less mechanical logic. The real value of code lies on the application side, not the data side.
(Can you tell I'm from an app development background :))
April 10, 2009 at 8:32 am
Honestly, having worked with startups, and for software vendors, there isn't a lot of IP that's worth protecting. Most of it is common procedures that are rewritten. An accounting application having IP? Come on, those are set, published rules for handling finances. They're publicly published, otherwise you couldn't implement the system.
If it is that critical and unique, you'll know if a competitor steals it and you can sue them.
If clients cause support issues, that's good, right? You charge them more if they constantly call because of "tuning" and sooner or later the guy writing the check tells the guy touching the code to stop.
April 10, 2009 at 8:33 am
AndyD (4/10/2009)
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.
I think it is a little more interesting than this. The Quantum Guy says the coin is both heads and tails at the same time; only when someone looks at the coin does it change from an indeterminate state to either heads or tails.
Now, how does this relate to encrypting our SQL code? hmm...
This is a little 2 sided now isn't it? Just to add a bit of Oh No's to this, we do live in a 3D world, not a 2D world. So, a coin *technically* does have a third option other than heads or tails. It also has an edge side. It just happens to be the rarest option to be hit, but still it is possible.
April 10, 2009 at 8:46 am
A few thoughts from a simple-minded DBA with near fatal deficiencies of caffeine at the moment.
1. Anybody that purchases an application accepts a software licensing agreement, which typically includes a clause like "you can't repackage this code and sell it." Stealing code to resell is seriously stupid and I have to believe most companies will not risk it.
2. Encrypting stored procedure code, in my experience, has been more harm than good.
April 10, 2009 at 9:25 am
The only time I have seen SQL Server procedure encryption used was for a vendor product.
I decrypted and looked at a few of the procedures. They appeared to have been written by very unskilled developers, so I would say in their case that encryption was used to try to hide what crap they were selling. There was certainly nothing that they had to worry about me stealing.
April 10, 2009 at 9:28 am
"WITH ENCRYPTION" should properly be called "WITH OBFUSCATION". It is a pretty simple XOR decryption routine to reverse the function and stored procedure "encryption" feature of SQL Server. This was true for SQL 7, 2000, and 2005. I imagine it hasn't changed for SQL 2008. In fact, SQL 2008 BOL says this about the WITH ENCRYPTION clause: "Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format." In SQL 2000 you needed to have SA level permissions to reverse the WITH ENCRYPTION obfuscation. In SQL 2005 I was not able to do it unless I could log in with the DAC and access a true system table. So it is a little more difficult to reverse it in SQL 2005 (and presumably SQL 2008).
Regarding SQL CLR, it is compiled .NET code and is quite easily reversed if you haven't used some sort of obfuscation routine. I found that DotFuscator Professional product did a fairly decent job of obfuscating the SQL CLR code. I knew what the code was and was not able to reverse it back.
April 10, 2009 at 9:34 am
Theunis Viljoen (4/10/2009)...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....
We are using an earlier version of xp_crypt/SQL Shield (not my idea!) in an internal application to protect code used to encrypt and decrypt sensitive data and I find it to be very annoying. Every time we apply a SQL Server service pack or move a database to another server, we have to reapply all the encrypted source code. I have also found their support to be much less than what is required for an important piece of software that is critical for an application.
Given this, I would automatically reject any vendor software that required the use of xp_crypt/SQL Shield.
April 10, 2009 at 10:22 am
As a software vendor, let me tell you that what we are talking about here is not encrypting at all, just obfuscation. In order for code to run it must be decrypted. For it to be decrypted the key to decrypt must be included. That defeats the very purpose of encryption. Compiling MAY help in hiding your code as it is mostly a one way process, but the value of encryption in hiding your source code is zero and is just a nuisance. Now if there was a compile sql code option it would have helped both to hide code and to make it run faster but merely obfuscating is of no help at all.
April 10, 2009 at 10:44 am
I agree with Jack Corbett that if the intent is to secure IP, then leave your logic in your code. I have been mainly a developer for the past 20 years and after 10 years of part-time administering databases, I'm now taking on full DBA duties. There is an inherent paranoia with developers that everyone is going to steal your code. The fact is, if they want it, they will get it and if you hide it, people will think you're hiding something worth stealing. Leave your logic in the app, compile the app and leave the rest alone.
There's an IT manager here that was really pushing me to encrypt usernames for a web app - citing that because information is saved in a cookie on the client side, someone could hack their computer and find their username for the app. This from the same guy that made the windows login password scheme so complex that users are putting their passwords on post-it notes on their monitors so that they can remember them. I think it's real easy to go too far on the encryption/security bit until the benefits become blurry.
I get the support point of view, but problem users are going to tamper no matter what. I think security should be approached from keeping outside hackers from getting in and not keeping customers, users, developers, etc. out. There are enough things to worry about...
Just my .02
April 10, 2009 at 11:23 am
Michael Valentine Jones (4/10/2009)
The only time I have seen SQL Server procedure encryption used was for a vendor product.I decrypted and looked at a few of the procedures. They appeared to have been written by very unskilled developers, so I would say in their case that encryption was used to try to hide what crap they were selling. There was certainly nothing that they had to worry about me stealing.
No, but they had a lot to worry about you telling them how messed up their code is. In most cases I have seen, the object of encrypting SQL code is to hide how bad the code really is - and, in some cases to make sure the company has a revenue stream available for fixing their own broken code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply