April 10, 2009 at 6:03 am
We encrypt our sql stored procs. One of the main reasons is so that we don't have a support nightmare. For the causal client, they might try to change our stored procs to "improve" them, only to really mess things up. This way we can be relatively sure that what we have in source control for their version is most likely what they have on their database. So when tracking down bugs or issues for clients at least we don't have to worry about if they messed with our stored proc code.
Ben
April 10, 2009 at 6:18 am
I wish encryption did work. Not to prevent against updates, because in most cases I think it's having an app built on stored procs that gives it extra value to the client - they CAN change it if needed, or tweak performance, but to give us the ability to protect intellectual property. I'll agree that real IP in a proc is rare, but maybe it's rarer than it should be because we don't have the encryption. Same for DTS/SSIS.
I get the pain it causes from the DBA side, but as someone who has built a thing or two, it would be nice to be able to secure a few key ideas. More often the IP gets loaded on the app side of things, and as you point out it's hard even there to keep secrets.
It's not on my top 10 list of things I wish worked better, but there are people who legitimately need to protect IP and I wish we had a way to give it to them.
April 10, 2009 at 6:20 am
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...
April 10, 2009 at 6:26 am
Jason Miller (4/10/2009)
Best wishes for Passover, Good Friday, NichLactemyer, or whatever holiday it is that you celebrate or not...
Ok, I'll bite, not even Google knows what "NichLactemyer" is.
Happy Good Friday.
<><
Livin' down on the cube farm. Left, left, then a right.
April 10, 2009 at 6:42 am
We encrypt to keep clients from shooting themselves in the foot and causing support nightmares. (Which in some determined cases they still manage to do!)
:w00t:
April 10, 2009 at 6:46 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...
I always thought that the "both heads and tails" argument rang hollow, it's a cop-out.
However, the application of this idea to cryptography is incredible. I've already bored enough people, so let's leave that research open to the interested few.
Honor Super Omnia-
Jason Miller
April 10, 2009 at 6:47 am
We used it purely to make sure that the developers would not change the stored procedures on the database and then forget to check their changes back into the source control. So if you wanted to change something you would have to go to source control to do it.
As far as the customers go it is so easy to decrypt them if they wanted to, and they did, it did not bother us.
April 10, 2009 at 6:54 am
Tobar (4/10/2009)
Jason Miller (4/10/2009)
Best wishes for Passover, Good Friday, NichLactemyer, or whatever holiday it is that you celebrate or not...Ok, I'll bite, not even Google knows what "NichLactemyer" is.
Happy Good Friday.
Honestly, I don't recall the exact origins. I just remember sitting around, consuming prodigious quantities Yuengling, and making up a bunch of crap. We tried to create new holidays, more reasons to throw a party back in college. Granted, didn't need much more than "Hey, it's Friday." or "Hey, it's Saturday", or "Hey, She's Cute!"
NichLactemyer is a purely fictitious holiday dreamed up by a group of drunken college boys.
Honor Super Omnia-
Jason Miller
April 10, 2009 at 7:03 am
have you heard of Schrödinger's cat???
Heard of it? I'm in the box with it!!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 10, 2009 at 7:05 am
I worked for a company where we ended up optimizing the application code for a third party app that was critical to our operations. The applications support was not very good and it was easier for us to figure out what it was doing and to fix or change it ourselves. We took backups of the production database before making any changes in the event that we actually messed things up but in 6 years of dealing with it, we never performed any changes that caused any real issues.
I think as long as the right people are snooping through the code, then encryption really isn't necessary. Of course, if you build a good enough product and support it well, people probably won't find the need to snoop at all!
April 10, 2009 at 7:20 am
A few years ago I worked as a consultant for a large firm that did all their development in house. The enterprise application was massive. It spanned 3 separate databases and had well over 5k sprocs in all. Additionally there were many many DTS packages to synch data across the different databases. They had a rule that dynamic sql was not allowed and that ALL data manipulation was to be performed via sprocs. The reason I ended up trying to help bail them out was that prior to my engagement they had a rule that all sprocs had to use "with encryption". In addition to the 3 databases they ran 4 different environments (dev, qa, prod support, prod). They did not do an adequate job of version control and as such the sprocs all got out of sync. The real challenge was in trying to figure out which version was running on which server. Because all the code was encrypted it was nearly impossible to ensure that we had it straightened out. Basically we ended up using the version in VSS and deployed that to all the systems after deleting "with encryption". It took nearly 4 years and lots and lots of system bugs until we got it right.
The whole time we all thought it was very silly to encrypt all the sprocs since the application was entirely used in house. The only people that had access to the server were the very people that were struggling to get it right. There were lots of other issues with this system but that is a topic for another day.
I do think there are times where protecting IP is important. But if you decide to put your knowledge in sql code and decide to encrypt it make darn sure that you have a very solid plan for source control. If you don't it can run you over and you spend more money trying to fix your own problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2009 at 7:22 am
No, never had the need or felt the need to encrypt stored procs. Could be a good thing though, from stopping people from stealing your code. What I am about to say might just freak some of you out. Whenever one of my clients needs a script I write it (got a copy of their database) and e-mail it to them. Then they phone me and I help them copy it into their database. I also help them, telephonically, to create/change a query for stats etc. that they want if it's not too difficult. So, if I encrypted the stored procedures that wouldn't be possible.
Happy Good Friday to all!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 10, 2009 at 7:34 am
Over the years I have evaluated many software systems for purchase and I have yet to recommend one that used encrypted procs. I assume that if they encrypt their procs they are somewhat ignorant of how things really work or are just trying to hide something. With only one exception, we have opted not to purchase applications that use encryption on their procs, and that was because it was a highly specialized application that really had no competition in that particular space. I have not yet seen a product that we considered "best of breed" to employ this tactic...ever!
The license agreements all state that we cannot make changes to the application, but being able to look at the procs does make it quite a bit easier to troubleshoot and solve performance issues.
/*****************
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
*****************/
April 10, 2009 at 7:36 am
athurgar (4/10/2009)
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 had this happen, sort of, in my first Denver job. The previous DBA had encrypted all procs on the v6.5 server, which wasn't a big deal, except that he got fired. He, along with the asst DBA had been using SourceSafe, which was good, but they had it configured to leave copies of the procs on their machines even though they'd checked things into VSS.
What's worse?
They also had multiple copes of many procs on their machines in different folders. So we had no idea which was the "current version" on the servers. I ended up decrypting hundreds of procs, which was a time consuming weekend project, and reloading VSS.
April 10, 2009 at 7:43 am
We provide a suite of functions replicating EXCEL functions for SQL Server using SQL CLR. Based on the traffic to our web site (www.westclintech.com) it is clear that there are a small number of people who download the software for the purposes of reverse engineering it. We are certainly not concerned about real users of the our software reverse engineering the code and violating the terms of the EULA, but not everyone is so inclined.
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply