Protecting Proprietary SQL code
-
Is it possible to encrypt stored procedure code (for a commercial application) in a way that it can't be seen by anyone (including clever DBA's who administer the server the SP ares installed on)?
Or is there any other way of protecting proprietary SQL code from competitors, who might otherwise be able to install the application then access the SQL used?
Thanks.
-
About the best you can do is use the WITH ENCRYPTION option on the CREATE PROCEDURE statement. However, I know that procs encrypted on SQL 6.5 & 7 can be decrypted using a readily available procedure. Don't know if the same has been done for 2000 though.
On a personal note, my preference is to not encrypt the procedure. If you keep the procs as basic data input/output and keep all your actual programming logic in the application, either as a DLL or EXE, it makes life for DBA's a bit easier
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
-
If you have exe's that issues SQL commands, profiler still might be a tool someone could use to determine a sequence of SQL commands used to perform a task.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
quote:
About the best you can do is use the WITH ENCRYPTION option on the CREATE PROCEDURE statement. However, I know that procs encrypted on SQL 6.5 & 7 can be decrypted using a readily available procedure. Don't know if the same has been done for 2000 though.
Thanks, I've heard of WITH ENCRYPTION but had assumed a dba or anyone with SA privileges could still access the code?
As for processing in the middle tier, this is an analytic application and processing on the server with set based SQL operations will offer huge perfornace gains.
-
quote:
If you have exe's that issues SQL commands, profiler still might be a tool someone could use to determine a sequence of SQL commands used to perform a task.
Ok so its starting to look like the best I can do is make it more difficult for someone to access....
Is there a way to detect (within the SPs) if profiler is running?
What if I had all my code in a seperate DB, that could be attached (Restored) to clients database, temporarliy....while the application runs? could this offer any added protection?
-
WITH ENCRYPTION works against everyone, including sysadmin level accounts. However, it's not very strong.
There's a script (I think there's a version in the script repository here) that is based off original code by shoeboy. It basically uses an ALTER PROC to reveal the key and then uses that to decrypt the stored procedure.
There's also dSQLSRVD, which if you're a sysadmin on the box, will decrypt without using ALTER PROC.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Yes.
In SQL 7.0, you can use the "exec master.dbo.xp_trace_enumqueuehandles " to determine if any profiler traces are running. If no records are returned, no traces are running against the server.
In SQL 2000 you can use the "SELECT * FROM ::fn_trace_getinfo(default)" to determine if profiler is running. Also if no rows returned then profiler is not being run against the server.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
quote:
"exec master.dbo.xp_trace_enumqueuehandles ""SELECT * FROM ::fn_trace_getinfo(default)" to determine if profiler is running.
Gregory Larsen, DBA
Superb! so it looks it might be possible to almost completely secure proprientary SQL code by:
Using the SQL issued from exe/dll model: Preceed each commercially sensitive SQL batch with one of the profiler detect commands you gave above, and if profiler is detected then suspend the app and notify the user that the app is can't run while profiler is running.
This would make it nearly impossible to access the sensitive SQL as profiler would have to bestarted between the detect and batch issue (milliseconds) to get around this, and even then they'd only get one batch.
Can anyone see any weaknesses in this....e.g:
What about syscacheobjects?
-
Not bad. What access level needed for the function - BOL didnt specify any that I saw. If its sysadmin, you'll get some push back!
You'd also have to look at guarding the text in the dll/exe - obfuscate or better, plus what about the wire connection? Or you could look at the results and try to reverse engineer!
Andy
-
Still won't protect the code from someone getting to it. Anything can be decompiled.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
quote:
WITH ENCRYPTION works against everyone, including sysadmin level accounts. However, it's not very strong.There's a script (I think there's a version in the script repository here) that is based off original code by shoeboy. It basically uses an ALTER PROC to reveal the key and then uses that to decrypt the stored procedure.
There's also dSQLSRVD, which if you're a sysadmin on the box, will decrypt without using ALTER PROC.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
At this point only seems to work for under 4000 characters accurately. Basically what it does is issue a ALTER PROC statement with encryption. The key is that whatever underlying factors determine the hash key do not change with an ALTER. So what you do is figure the difference between the encrpyted version data and the ALTER PROC statment except you use CREATE PROC since ALTER is not what is encrypted. Then you go character by character figuring the current has difference and apply that difference to the stored value (stored in variable, this is why has a limit), I suppose an outside app can do the same but to be sure you got all the letters you have to do some calculating to get the original size. What happens the difference applied to the original encrypted version will give you the original character which in the code is stored and applied back to the db when done. If you have too many characters the rest on the info will be lost and corrupt the SP so you have to be carefull and makle a backup beforehand.
Ultimately thou you will find if they want it bad enough they will find a way to get the info.
quote:
Can anyone see any weaknesses in this....e.g:
Yes, how will you determine if they are using Profiler for legitiate reasons other than to spy on you code. Also, unless you have someway to enforce data stream encryption other than them installing an OS level encryption they can use NetMon to just get the raw packets and see the information.
quote:
Still won't protect the code from someone getting to it. Anything can be decompiled.
Not totally true. They can see the assembler version at best which has complicated statments like push and mov with data address but no actual code. They might could discompile from this to a psuedo facsimile but no guarantees it will be the same or even function. It does however give them hints at what may be going on, especially if they have a clue about assembly.
Bottom line is unless you own the entire server so that no one can get to it and no information is passed outside the box (say like a web box with a dedicated link to a sql box on their own small backbone) then they can insert themselves into what is going on in the network layer or kernel layer and still get information. You will have to just trust people to some extent not to rip you off.
-
The script by shoeboy is limited to 4000 chars, by dOMNAR's program dSQLSRVD is not.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Almost forgot to mention. The process is not limited to Procs. Any WITH ENCRYPTION item can be done this way. And thanks for the additional detail Brian.
-
Yup, views and functions use the same encryption method. However, it needs to be said that WITH ENCRYPTION will stop the merely curious.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
Ultimately thou you will find if they want it bad enough they will find a way to get the info.
OK thanks everyone this information has been very helpful. As many of you have suggested it seems there is currently no obvious way of completely securing proprietry code that runs on Microsoft SQL Server....but I'm still hoping to find a way that not only stops the merely curious but also deters competitors from trying...
will keep this thread posted of any progress I make...
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply