In essence, encryption of code within the engine is more like obfuscation than anything else. Unlike passwords and other sensitive data, the engine has to be able to read the code, so there has to be a way to reverse the so-called "encryption." While the engine inherently has the ability to do this internally, there is no direct way for us to do it ourselves out of the box (so if you play with the WITH ENCRYPTION option, be warned; you need to ensure you keep a copy of your module code in source control). Obviously decryption methods exist: some of which are free, and some you have to pay for – such as SQL Prompt Pro from Red-Gate:
I won't point you to the free ones, but will simply state that anyone with a search engine can find them.
Personally, I haven't used them, but I haven't been put in a situation where I felt I needed to. Others have. Last week on twitter there was a brief conversation on whether or not it was "okay" to look at a vendor's encrypted stored procedure; the reason: poor performance. With the assumption that the vendor was either non-responsive or did not have a good answer, I suggested that this would be okay in order to learn for yourself the potential cause of the performance problem – not necessarily so you can fix it yourself, and not necessarily to say to the vendor, "I told you so," but it might actually yield information about a problem in your own part of the system (e.g. a missing index or a poorly networked linked server).
One of the comments @Dave_Levy made was:
I guess this is one of those areas where people willing to cut corners can get ahead of the rest of us.
I wasn't sure exactly how this constituted "cutting corners." If the vendor is not owning up to the issue and taking responsibility for their code, and as long as you're not learning from the code in such a way to compete with them (or to replace their solution with your own), then personally I think it is within reason to use the tools at your disposal to get to the bottom of the issue.
Later in the discussion it turned out that this peeking would be a direct violation of the licensing agreement with the vendor (which is probably the case in most situations). The software vendors who sell products that use encrypted stored procedures know that it is not protection of their intellectual property, but rather a deterrent for the casual observer. So encryption is just a minor roadblock slowing down a determined user; the way they really enforce the protection of their IP is through their EULA.
So it is quite a fence to be sitting on : suffer the performance problem, or try to get to the bottom of it? wait for the vendor to fix it (or to refuse to), or arm yourself with enough information to argue with them? stand by your morals? switch vendors? If what you learn from inspecting the code is that the vendor should have no business putting code anywhere near your systems, then that's something, right?
I realize we all want to be loyal to our vendors, but if they aren't willing to work with us, our only other alternative seems to be to either violate the EULA or uninstall the software; both options have their pros and cons from both sides. I'm curious what others' opinions on this are?
I think that this is always going to be a tricky issue. But I have seen Vendor Stored Procedures in the past that once decrypted (I'm as curious as Peter about anything that is running on my SQL Servers) are using Cursors etc that could have been written out of the Stored Procedure to give better performance. In most cases I have found that when this is discreetly pointed out to the Vendor they are happy to change it, especially when you benchmark your work and show the better performance. I'm not particularly worried if the vendor then uses my work to make their product better as it helps me out.
This is a little OT, but I've been wondering why MS didn't include better encryption for database objects. SQL 2005 and 2008 have much better encryption capabilities than 2000 did. Simple insecure obfuscation seems to be a backwards-compatibility throwback, and a pretty useless one at that. With SQL 2008 and EKM you could theoretically create a "dongle" type hardware key to protect your properly encrypted database objects — assuming the ability to encrypt database objects using cryptographically secure algorithms was even a possibility.
Personally, I have indeed decrypted in the past, mainly as a way to learn how certain stuff was done. I'm curious by nature, and I feel if I do not commercially try to exploit the knowledge I gained, I should be alright.
The situation described where the vendor is not responsive is a different issue. I personally do not view EULAs as fully legally binding, as 99% of the time, they are merely meant as paper tigers; try and intimidate people, but if ever be brought in front of a court, deemed one-sided and too oppressive.
A way to sidestep the legal issue altogether though is to publish your findings on blogs and relevant professional sites. Publish your research and taken steps in a neutral way so your peers can see what you did and how you came to your conclusions, and perhaps someone might point out something you overlooked. If your findings are confirmed by others, feed the response back to the vendor, and if the vendor still refuses, name and shame them, and just do not buy their stuff anymore.
My 2 cents.
What would be ironic is if RedGate is encrypting their procedures et. al. that they distribute with products like SQL Backup, in an attempt to protect their IP. Yet here they give you the direct ability to circumvent that very protection mechanism.
I have to admit that I decrypted some vendor code at early in my career. There was a procedure that appeared to have a problem so I did some troubleshooting and determined that the procedure had a bug. I reported that I believed that there was an issue in that procedure. I actually don't remember if it was fixed or not, but it was definitely a thorny issue.
I don't think vendors should encrypt SQL Code. I believe any proprietary application code should go in the compiled app, but I also don't think we should be decrypting encrypted code and I was surprised to find the functionality in the new SQLPrompt Pro.
I think if you can make a strong enough case that the index strategy is a problem, and they still refuse to provide support, then I suppose they are shooting themselves in the foot. In such a situation, what are the odds you'll renew your license and/or upgrade when the time comes?
I've had a situation of deadlocks within one of my servers. I knew the application doing it, I knew the behaviour that was leading to it and I wanted to pull the code, but didn't.
I went to the vendor and said "hey, your application is causing deadlocks because you are attempting to update every column in a table any time you change one thing and you have an index on each and every one of those 25 columns killing everyone else that's attempting to do anything, can you fix this?".
The vendor comes back with "there's nothing wrong with our code".
I can't prove that their code is bad without pulling it, but if were to pull it where would it lead me from a legal standpoint? Even were I able to show it and the legal aspects to be fine, should I do something to change things and make it work swimmingly what happens when we run into an issue down the road? They take a look, say things have changed and refuse to provide support.
Pretty much a no win situation, so I get to look at 40 deadlocks a day in my logs…