February 8, 2002 at 1:58 pm
Our company creates custom databases. When we create a new release, we distribute the database as several SQL files. Once the stored procedures and triggers are loaded, they are encrypted using the 'WITH ENCRYPTION' option, but that does not stop customers from viewing the source files on the CD. I have seen sql files entirely encrypted, including the 'CREATE PROCEDURE' keyowrds, but I have not figured out how to do this. Any clues?
February 8, 2002 at 2:02 pm
I believe and I may be wrong that when they created the views and stored procedures they ran them once with WITH ENCRYPTION then opened the in SQL EM and copied the encrypted format out and pasted into their source files.
February 8, 2002 at 3:17 pm
There are any number of ways to do this, but the bottom line is that there are numerous ways to decrypt stored procedures. A quick search on google will have you decrypting them in 30 minutes.
If you are trying to protect the casual browser, then encrypt the source files on the CD and compile the code into a VB or VC program and connects to SQL and then decrypts the source before submitting a batch. Still ain't perfect, but its tougher to decompile a VB program than a stored procedure.
Steve Jones
February 8, 2002 at 3:38 pm
I agree with Steve wholeheartedly because, well, I did try the search about a year ago (on Google no less) and found and installed a stored procedure/view decryption program on my desktop just to prove a point to another DBA. SQL 7 or 2000. They are out there.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 4:09 pm
Besides if your concern is code theft just make sure you have the proper copyrights and stick it too them. If it is code tamper have a disclaimer against that and your resposibility. I too agree with Steve in that you can pretty much encrypt it anyway you want but some can always decrypt, and by the way, the way constants are stored in vb it isn't that hard to to figure out from there if you can find the right tools. Bottom line you hide someone will break into it just because.
February 8, 2002 at 7:12 pm
You can choose a non-obvious key and encrypt the files with it. Even if someone is looking at the VB program, the person has to realize you're using a non word based key (for instance K9a7FGER2 or something of that sort) and also has to know the encryption method. Here's a good starting point:
Counterpane Security is where Bruce Schneier is at as CTO. He's the writer of Applied Cryptography.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 9, 2002 at 9:24 am
Which the VB thing or any other encryption method is a waste IMHO, as once it is loded into SQL Server the only encryption you can use is the SQL Encryption anyway and we already know that is hackable.
February 9, 2002 at 2:22 pm
Not sure its a total waste of time. Every bit you can do to obfuscate is probably going to help if its that critical. Or you think it is! If its really critical what's the solution? If its merely important? Here's one I dont know the answer to yet - if you create a temporary stored proc does it show up in syscomments? Any way to keep an SA from profiling to see what the temp stored proc contains when it is sent over?
Andy
February 12, 2002 at 10:03 am
It's not a total waste, and I agree with Andy. Obfuscation does help. Lots of people are lazy and won't keep trying if the easy thing doesn't work.
However, keep in mind what yuo are realistically accomplishing. You are hiding the info, not securing it.
Steve Jones
February 12, 2002 at 10:18 am
Let me restate. Go back to my original post, where I suggest doing with WITH ENCRYPTION and copy the string out into a .sql or whatever file. I merely mean taking it to the extreme of putting all this into a vb app to hide is pointless as if they really want it they will get it from SQL anyway in a manner that is easy to decrypt. You are just adding extra work on yourself to hide it in any app. Just make sure your EULA covers your source code and if you find a customer or any other person using it without your permission go after them (but make sure you have the code properly copyrighted). Never assume you can protect yourself thru coding means. The point of creating a stored procedure in itself is to take full advantage of what this does, temporary versions cause you to lose the bennefits. And no you cannot keep an SA from seeing your code while profiling the server. If you want to hide the code from your standard user then WITH ENCYPTION and the method I stated is enough.
February 12, 2002 at 10:36 am
Consider this:
(a) User has to realize that decryption tools are available for SQL Server.
(b) User has to know how to fire up and use Profiler
(c) User probably knows how to open up a file with a text editor.
We're not going to be able to keep the hard core people from taking apart our stuff. People have even decompiled SQL Server to get at trace flags. But by encrypting the files and having the VB program decrypt and pass to SQL Server, it makes a little bit harder, and that's all it takes for 90% of the population, even DBAs.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 12, 2002 at 10:55 am
Rule of thumb:
This applies to every product. SQL, MP3 music, DVDs, etc. Bruce Schneier has stated something similar to this in the past. Read his book about network security if you have doubts.
Steve Jones
February 12, 2002 at 10:59 am
Ok I understand what you are stating but I am not talking about putting the plain text into the file. What I am talking about is putting the SQL Encypted version in the file. Works in SQL 7, will allow you to open the comment from EM so when you do this
CREATE PROC ip_SelMain
WITH ENCRYPTION
AS
SELECT * FROM sysobjects
But the text is
exec(decrypt(0x4CAFD1771AB283A69C1A88839C439A5182D4765C9BDD4BA6A37489AFD2CBDE072F4C2F378847639A5543CADE15D0CBB36704DCC61A4F6566BAAA4FEF35ADFD916E492F355CCDDED498F598055A3313F841BF959176A0B04917568EDFED7C2E6323750D20F54A29FA0D27BCDE727758DDCB5620F26AC65743480C97159151AC2268D8997522FEB1262424F3162DC21D66920E9AE4E6DE4EB5BD43
))
Which you can run and build the Stored Procedure and works fine. This was a flaw in SQL 7, so you see I would past the latter in a file and distribute. SQL 2000 will accept it just will not let you get at it like this.
Does this explain better what I am talking about.
February 12, 2002 at 12:00 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply