Encrypted Stored Procedures and Their Effect on my Rug

This is a letter, or a rant, to any ISVs in the world who encrypt their stored procedures. It is, by no means, a condemnation of this horrible feature of SQL Server. The feature condemns itself.

I saw something in a blog the other day (that I wish I could find it again) where the author essentially said that encrypting stored procedures is an absolute necessity. I couldn’t help but incredulously think “Really? What part of your code is so cunning that I can’t see it?”

Yes, I understand that someone might comment here and say “Well, certain encryption algorithms might need to be encrypted in stored procedures so that people don’t discover and break them.” To which I must respond: grow up. Nobody is writing encryption code in T-SQL. If they are, I don’t want to read it.

The fact is, Mr. ISV, your ideas are not so precious or original. I don’t have a degree in Computer Science, but I do know the basics of algorithms and design patterns. There are only so many ways to skin a particular cat. I can watch the behavior of my SQL Server and figure out just how craptacular your code is and make a pretty educated guess that you’re using cursors written by someone whose understanding of programming concepts stopped when VB was a threat to PowerBuilder.

Please, for all of us, just stop. Your insistence that your secret sauce is important is laughable. Nobody cares. Your competitors don’t care. They’re either too busy catching up or else too busy lighting cigars with hundred dollar bills to care. Nobody that I work with is going to spend their free time reverse engineering your software so we can stop paying maintenance fees – we pay you because paying you is cheaper than doing this work in house.

When you encrypt your stored procedures, I can’t help you make it better. It’s like you only come home drunk, throw microwave burritos at the cat, crap on the rug, and then leave. There’s no possibility for discourse about what you could do better. I don’t want to talk about what you’re doing wrong, I want to make this deal sweet for both of us – I want to make my system faster and help you make your other customers’ systems faster.

Please, for the sake of me and your other customers, stop shitting on my rug.


19 Comments so far. Comments are closed.
  1. Ouch. So, here’s the thing – we (and I’m kinda halfway speaking for Quest here) aren’t as worried about you seeing the code as we are about somebody modifying the code. I get so frustrated when we get support calls from users who have completely hosed their Quest product repository because they changed our code. Encryption slows them down from being able to just script out a stored proc, change a few lines to add their brilliant suggestions, and then alter the production repository.

    If I could stop them from adding triggers, that’d be great too, but I haven’t figured out a way to do that when they’re SA on their own servers. (sigh)

    • Ford won’t provide warranty repairs on your Festiva when you somehow manage to cram a turbo into that little engine and replace the factory exhaust with a giant fart can. Why should an ISV warrant support for customer modifications?

      We have the full source code to some web controls at work, it’s part of our licensing. But once we modify that source code we’re on our own.

      Of course, you could always just throw things in SQL CLR. At least then there’s no chance of a jerk like me breaking the shoddy encryption and modifying things myself. Instead I’ll decompile it and break things.

      • When the Ford mechanic pops the hood on your Fiesta, he can see the turbo. When we get a support call, we can’t immediately tell if any stored procs were modified. Often, we’re not even allowed to run queries against the customer’s system for troubleshooting. Sealing the stored procs makes troubleshooting faster because it’s one less thing to check. It cuts support costs, and we pass the savings on to you. (Not really. I use up the savings lighting cigars with hundred dollar bills.)

      • You have a valid point.

        Interestingly enough, Quest’s products aren’t the products I’m ranting about. I didn’t even know you resorted to such shenanigans. And, frankly, if monitoring software is what I’m tuning, we probably have bigger things to be talking about. ;)

        Personally, I take a very draconian view of this because 1) I’m not an ISV, 2) were I an ISV this is the route I’d probably take, and 3) I actually expect software vendors to treat me this way. If I purchase a license to modify and I decide to go off the range, that’s my own damn fault.

        At least you’re only getting cigar ash on my rug…

      • Jim,

        If the engines computer was encrypted I would be limited to who I could take that engine to for servicing. Lets face the facts, encryption is used in SQL primarily because the vendor of the code just want clients coming back to them for business. If you have to go that far, I wouldn’t give you my business to start with.

        Don’t use the warranty joke. If I modify my car or some code/script, I’m happy to suffer the consequences. If someone else modifies my SQL script, good luck to them. If they break it great me or someone like me gets some more business.

    • And here’s where I’ll offer a rebuttal by pointing out that Microsoft doesn’t see the point in this so why should Quest? If nothing else, write a quick script you require customers to run that calculates a CRC value for each stored procedure and if it doesn’t check out, you ask, “What did you do?” and you can point to the specific stored procedures/functions/etc. that have been changed?

      • Microsoft doesn’t see the point in LOTS of things that we do. Microsoft didn’t see the point in giving you backup compression for SQL Server either – does that mean we shouldn’t have?

      • Ooooo, bad example, Brent. See, technically Quest Software did not see the point in giving us backup compression, either. A company called DBAssociates did (and DBAssociates became Imceda). Now, when SQL Litespeed was selling like gangbusters, then Quest went and acquired Imceda. But then you could and should argue that Quest saw a hot property in the SQL Server tools space and were looking to make a buck, not specifically trying to help the DBA.

  2. How I love a good rant first thing in the morning! You’d probably get a kick out of this guy: http://sqldumbass.com/

    In the mean time, there probably needs to be some sort of discussion with the ISV (at the time of purchase) about whether you and they can come to some sort of agreement about you leafing through their stored procs. If not at the time of purchase, then why not at the point where you suspect a problem?

    Bottom line: if there’s a problem with this app and you’re trying to fix it without any input from the ISV, you’re in a dysfunctional relationship with that ISV, aren’t you?

    • SQL Dumbass is a great humor site.

      I agree that vendors should make it very clear when you purchase an application, or even before you purchase it, what you can and can’t do. I try to find this out before I purchase an application and that can actually be a big part of my decision. I ran into an app at the beginning of my career that was a critical line of business application for my client. Unfortunately all of the application had been written using encrypted stored procedures and there was nothing that could be done to tune it. The ISV admitted they had never seen the customer’s volume of data and that they were unsure of how to proceed to fix things.

      Shockingly enough, there is no ISV that has me all up in arms. We only use on or two third party applications at work that hit a database and those apps are tuned incredibly well by the vendor or else nobody cares about their performance. Either way, they don’t cause me any problems.

  3. Mark,

    Excellent presentation today on SQL Internals! Was worth attending!! And sorry for abusing the questions… or maybe not, since everyone was kind of quiet :)

  4. And let me go ahead and snip the rebuttal about encryption algorithms in case anyone goes that direction… it is well held within the crypto community that a strong encryption algorithm is one where everyone knows the algorithm and even with that knowledge you aren’t able to decrypt the ciphertext without knowledge of the secret (the key). The first indication that one likely has a weak encryption algorithm is when you are seeking to protect that algorithm. It has been proven through time and experience that exposing algorithms to peer review is to best way to weed out weak and poor algorithms for truly strong ones.

  5. Andy Irving,

    it’s an empty gesture at best, sql server’s encryption for stored procedures is so trivial to break. red gate even builds it into sql prompt now.

    my guess is (as an ISV), if the ISV is using it its because they really think it will protect their awful encryption algorithm or other bad code. therefore, they probably have some bad software written by people who largely don’t know what they’re doing!

    I feel Brent’s pain though; i suppose all you can really do is get a dump of the schema and do a compare. customers always lie about changing things :)

  6. Pretty much encrypted the stored procs in litespeed to keep most folks from messing with them and causing support problems. It was easy. Just like locking the door on your house. It won’t keep out the people that REALLY want in but will keep folks from just walking through.

    You pay us to fix it. If you thought you had a solution we would listen but at the end of the day we (the ISV) generally know more about the code and the interdependency in the product stack, if you make changes you may fix one problem and cause two more. Work with your ISV, if they are worth a damn they will listen.

  7. John Lawrence,

    Amen Jeremiah! Too many times have I reverse-engineered some code only to discover that it really wasn’t worth my trouble. Personally, I’d rather have my code copied around the world than be known for how well I hide something that shouldn’t have been published. But, as the old saying goes, “…Those who can’t teach, teach gym.” Thanks for the muse.

  8. Ben McIntyre,

    if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[sp_Post_Forum_Comment]‘) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
    drop procedure [dbo].[sp_Post_Forum_Comment]


    /****** Encrypted object is not transferable, and script can not be generated. ******/


  9. Ed Zann,

    We have a truly awful third party client-server application that has always had performance issues. Matter of fact the vendor has since ceased offering it and is pushing everyone into their hosted model. In the early days of using this app, I spent some time with SQL Profiler analyzing the workload because the morning import was soooo loong. I spotted some stored proc sql statements that would benefit from indexes. I relayed that to the vendor. In the next upgrade we received, all of the stored procs were encrypted!! and the app still runs like crap, so I’m not even sure if they implemented my suggestions.

  10. Siva Sivapalan,

    The current mass marketable Accounting softwares do not use stored procedures. as such they are not using the full power of the SQL server

    As a small company who belives in such methods we develop software using such methods. we use the concept that the inteleigence is in the Data and not in the application. It is like that character Data in star trek. The whole of the logic of our application is in the data (including the SPs etc).

    We feel that the big companies will be forced to change to these methods in the future. Not being able to protect our code will give them easy transition to use these methods. We wre not talking about one or two Stored procedures. 100′s of them together who function together to give you a end product as a say a accounting system or booking system

    Not having a more rigoureous way to protect our Inetelectual Property is stopping us from mass marketting the product. This may be the reason that software such as Navision etc do not have Stored procedures

    • Siva, I’m going to disagree with you about everything you just said.

      It’s possible to take advantage of SQL Server’s query optimizer without using stored procedures. You can achieve many of the same benefits by using sp_executesql to execute T-SQL statements as if they were stored procedures. These statements will receive fully optimized execution plans and those plans will be cached, allowing for plan re-use, a reduction in memory consumption (ad hoc plans can kill the cache), and a reduction in CPU utilization.

      Encrypted stored procedures don’t have cached execution plans. Instead, every time an encrypted procedure runs in SQL Server, SQL Server has to go through the entire rigamarole of building an execution plan again. For infrequently used applications, this isn’t going to produce a noticeable effect. On busy, mission critical systems, encrypting stored procedures has effectively doomed the customer to buying a larger physical server to run SQL Server. With the licensing changes in SQL Server 2012 you can bet your bottom dollar that savvy customers are going to start watching CPU utilization like hawks.

      I’m not really sure what code protection you provide. It’s laughably easy to rip the source code out of SQL Server even for an encrypted stored procedure. There are very cheap products on the market to decrypt stored procedures and it’s not terribly difficult to figure out the decryption yourself from a few quick searches with your favorite search engine. Security through obscurity will never be an answer because someone will always figure out a way around it.

      Protecting IP is a fallacy. If someone wants your IP, they will get it. Your competitors don’t want your IP because you can take them to court and bury them in legal fees for years. Your customers don’t want to figure out your IP because, if they did, they would have developed it themselves long ago. If you want to protect against modifications, there are many ways to solve that problem. People have already commented about it. Jonathan Kehayias wrote an excellent follow-up piece about the IP protection argument. If you haven’t read it, I suggest that you do.

      When you supply a customer with software that they cannot tune or troubleshoot apart from buying a faster server, you’ve done them a disservice. When that server gets slow, their only option will be to spend more money on a larger server. They won’t be able to call you and ask for help, they won’t be able to give back to you and make your product better. Their only recourse will be to spend more money.


One Trackback Trackbacks are closed.

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.