Hiding stored procedure code
-
I've got a client that has some sensitive business logic encoded in some stored procedures in MS SQL2000. Originally the project was to be used in house so puting the code into a stored procedure was not a risk; now they want to sell the application and limit the buyers from viewing the code in the stored procedures. Is there a way to hide the stored procedures' code from being viewed in Query Analyzer, short of moving it out of SQL and into a DLL or an Exe?
-
Yes...
Don't give the client permissions to the SQL server. Make sure the SA account is secured.
Also, put all of your SQL user access info in compiled COM objects or other executables.
-
If this system will be run on your clients servers then they will have access to the server and all the tools. Create the procs WITH ENCRYPTION as in
(see BOL)
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
WITH ENCRYPTION
This isn' t perfect but its not bad. There is also a product called SQL Shield by ActiveCrypt that may help
Francis
-
Any user can read the stored procedures, you need to remove the permissions to syscomments in the database which will stop viewing of the code. However, even with encryption your code isn't secure as there are procs available to decrypt them, I have such a proc. Your only way to secure the code is to put it in a dll. Also any user with dbo rights ( or higher ) can put the read permissions back on the table and read the stored procs, encrypted or not.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I agree encryption is not 100% but I doubt you can break encoding by SQL Shield. Having said that, putting business logic into a DLL makes sense, SQL procs should contain data access code not business logic. Certainly some data manipulation may be more easily handled within the stored proc but large amounts of sensitive business logic are better suited to the middleware layer of the application.
Francis
-
The data belongs to the data owners. The database design belongs to
the owners of the data resident in the database. The business
process logic implemented in the stored procedures belongs to the
owners of the business processes. Do not try to hide these from
their owners. Sell services, not apps.
Microsoft Office System 2003, with InfoPath and VSTO (Visual Studio
Tools for Office) provides the building blocks for easy-to-assemble
apps that users will use as easily as they now use Word and Excel.
Buy Office System 2003 and hire a module assembler (integration
architect) whose talents include communicating with data and business
process owners as well as using Microsoft technology.
As an integration architect consultant, I have seen, again and again,
companies spend money on apps that confound rather than expedite
their business processes. Most recently, I implemented a vendor's
app built on a database design that has 300 tables. The company that
bought the app does not use all the app's functionality, although
they paid for all of it, and, guess what, 200 of the 300 tables in
their implementation of the database are pemanently zero rows. But
of course the tables have to be there because the app is coded to
check for them on startup. And already, four months after "go-live,"
the company is exporting data to Excel to achieve functionality not
provided by the app. Death to these dedicated app dinosaurs! I
won't even look at a vendor's dedicated app when I can create and
debug in two weeks the functionality needed by my company.
Reporting by SQL Server reporting services. Security is database and network security and eliminate the middle app security.
My career strategy is to be expert in database design modeling
business processes, plus stay current assembling, maintaining, and
reconfiguring using Microsoft tools.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.." -
I could not agree with katesl more; sell services not applications. We've got an application (was here when I got here
where the developers decided not to use procs (due to security, most likely), so they imbedded on a their data access logic into JSP classes. Aargh! The worse thing about this is that they developed this appliction to work for both Oracle and MS SQL. I don't know about the Oracle version, but the MS SQL version is crap. Due to using java connection pooling they haven't been able to make the app work with SQL2K (threading issues, I guess). They also extensively use views, which can't be indexed in 7.0. And they also use unicode conversions when querying the database (= N'value), even though the datatypes their querying are varchar, not nvarchar. These things together effectively makes all indexes useless, and makes this application a dog.
The worse thing is that we have no way of modifying/improving their code, and even if we did decompile the Java classes we would be breaking our contract. The company we bought the app from has not been able to address our concerns; most likely the issues are not even getting to their developers.
All that to say that the old model of black-box systems is going bye-bye, it just doesn't work well enough. web services and open source are currently becoming more and more popular, web services for selling "service" and open source for selling applications. If our app was open source we would not have the problems we are having.
Signature is NULL
-
There are still times when obscuring the underlying code may be needed, for example on web facing sql servers.
encryption, limited permissions are one way, dll's probably the best - Yukon will allow better integration of the programming languages which may improve things.
Third party apps are another discussion that doesn't really relate to this thread - although we could discuss poor apps - but I'd say don't blame the developers, blame the descision makers in the companies who agree to buy them ( the apps ) - it's only after that that the DBA's have to pick up the pieces. Poor coding will always continue while companies keep buying the poor applications.
Whilst I agree, in principle, with the coment of open source, the issue is one of support - if you can't write the app you want it's not right to expect another company to support ad hoc changes and recodes to an an app you bought from them.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I've found the posts about open source interesting and good reading. On the whole, I like the open source business model; however, in this case, my client has a compelling reason to hide the code from the customers. My client engages other clients to perform some complex financial analysis for those other clients. They charge a nice premium for their work and the clients gladly pay it because they can't get the information any where else. My client now wants to sell this analysis tool to other entities, including governements. The reason companies and governments come to them is because of the process that is encoded in the application. Were they to open source it as some of you appear to have suggested, they would be giving away information they've paid enormous sums of money to acquire and develop. In this case the clients buy what the product can do, not the knowledge of how to do it. That my client desires to retain "secret" so they can preserve its market value and their investment.
-
sorry, didn't mean to mention the "o" word
; and I was off topic besides.customsw, if you want to encrypt your code don't put it in a SQL server that anyone else has access to. Period. All methods I've seen are easily broken (except 3rd party software, I don't know about that). Like people have suggested, compiling your code is the way to go for that, but it can be a pain in the butt.
Personally, I would think of this in terms of services. Client post data to a website, processing occurs on SQL, then the processed data is grabbed by the client, again though a website. No need to encrypt the procs or for the client to have SQL server at all.
cl
Signature is NULL
-
I never really thought that much about decrypting stored procedures before, but Colin was right about the existence of code that decrypts them. It took me just a couple of minutes to find this freebie on the web:
http://searchvb.techtarget.com/tip/1,289483,sid8_gci841704,00.html
-
Recently, I have tried to work around this problem using the following approach:
•I moved all the SQL stored procedures to Entity Framework 4.0 using Visual Studio 2010 and SQL 2008 Express.
•During this process I created views in the model and also some model defined functions plus some TSQL strings that represent TSQL native commands.
•This web-app project in C# can be compiled into an EXE or DLL later.
The problem, however, is that all the SQL commands can be viewed and saved to a file when I run the SQL profiler.
My question is:
How do I hide the SQL commands created by the EF functions from the SQL Profiler?
I remember reading a post in the Internet that says that if we put in a comment “password” in the code the SQL profiler will leave that function alone. I tried to search for this post today and couldn’t seem to locate it.
Can someone please help and advise on this?
-
Two things:
#1 (less important)
The thread you "hijacked" is almost 7 years old... It's usually better to open a new thread and to add a link to the old one for references. But, like I said, that's a minor issue 😉
#2 (more important)
Don't expect there are no tools to decrypt your .net code... It's not as easy as running Profiler, but it's not hard at all either.
AFAIK, there is only one method to hide the code from the customer: host the SQL Server by yourself and allow access only via your front end.
Other than that, all you can technically do is increase the time needed to decrypt the .net code. I think.
-
Thanks for your advices.
First, I didn’t understand about the “hijacking” part until you point it out. I’ll remember that in my future new posting.
Second, I am aware of the ability for people with tools that can reverse engineering almost any software product.
I read elsewhere that Microsoft has used a product called Dotfuscator that can make reverse engineering so much harder that it is not the worth for people trying to do so.
I was thinking of using this to compile the product.
Has anyone any experience in using this Dotfuscator?
How effective is it?
Thirdly, I checked with Microsoft in our area and was told that our business people here are still not up to buying software as a service.
If I want to sell my very first product I need to package it as a product that can be installed into a client computer.
Just relating a recent experience as an offhand remark:
Only yesterday after I downloaded an email Excel file attachment from non-IT friend and a couple of web articles into my pen-drive. After that it couldn’t be accessed any more. There was an error message saying “the drive is not formatted or formatted in Mackintosh format”. My OS is Win XP Prof. and the drive could no longer be formatted after that.
At about the same time my other notebook in the next table which had the wireless connection button set to “off” suddenly had the connection button coming alive. My VPC 2007 VM also went amok and had an error message like some VM hard drive had an error.
Was I experiencing a piracy attack because I asked questions like this in the Internet?
Talk about being paranoiac.
:hehe:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply