Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dextrous
Starting Member
15 Posts |
 Posted - 2010-02-01 : 17:34:31
|
| Hello,The application was developed in .NET and SQL 2005. Originally, we'd thought that it'd be a web-based application (i.e. the SQL database will be hosted by us and users can connect to it). But some clients wants the database to be hosted on their computer itself. Of course the application needs the database to run, however, by putting the database on the client's machine we will lose control of our data.I cannot find an easy way that SQL provides to allow only the application to access the data, but not humans. Nothing is altered in the database--we are simply reading data from the SQL table, so should we explore other things (indexed flat files?) to make this into a software product without losing control over the data? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 17:41:58
|
| Anyone who has SysAdmin access to the SQL Server can see the lot. If the data itself is encrypted they can't see that (nor can they fix it!!) but other than that all the design of your tables, columns, referential integrity, Sprocs, Triggers (i.e. EVEN if you use WITH ENCRYPTION they are easily unencrypted) and so on is fully visible to the client.Either wrap up in licence agreement (IME thread of withdrawal of service in the event of a breach of know-how is enough), or don't give it to them in the first place.Why do they think they need local SQL Server rather than you hosting it? Huge cost of maintenance, by you on their behalf, with the hassle it causes you for rollouts, patches, housekeeping and the like. We charge $2,000 per server p.a. additional maintenance for a client who wants dedicated server at OUR hosting service (i.e. all the files / patches we need are on a SHARE and readily available to us) - and this is just to cover the hassle of having to maintain our database on that server - our patches, housekeeping, and the like. |
 |
|
|
dextrous
Starting Member
15 Posts |
Posted - 2010-02-01 : 18:14:13
|
| Thanks, Kristen. thought so. I wish the client would understand how much easier it is for us to host. But it is a large company and we're small, so we have to figure out a way how to satisfy their demands.Any ideas/suggestions on what we can do if we cannot use SQL for this client? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-01 : 18:35:50
|
| Don't give them sysadmin access.Create a a role for the application and set explicit permissions for that role (or user) and deny everything else.Don't allow windows users, even domain admins on their end, any access to the OS.Need to protect the backups too else they can restore em to a box they are admin on |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-02 : 02:12:06
|
| "But it is a large company and we're small, so we have to figure out a way how to satisfy their demands."I don't think Fortune top 100 companies want the reputation for being bad thieves; if they need your software they won't have any intention of ripping you off, so will be happy with legalese that put the onus on them to keep details of your intellectual property confidential. Doesn't stop some thieving barsteward working for them and ripping you off ... but that's still Big Company's contract breach.You can't really not use SQL (I presume you have some fairly serious database code in your application, otherwise all you'll be giving them is the names of some tables and columns, which will presumably be fairly obvious! and thus you wouldn't be asking the question)Same problem has faced all people with database Apps in the past, and it comes down to trust (backed up by legal paperwork!)Oracle have some form of encryption of database code I think. Might be an alternative. Huge increase in cost switching to Oracle (and my person experience is that oracle takes "more work" than SQL Server - especially if you are not familiar with it)You could move all the SQL code to the application, so it isn't in the database itself, but SQL Profiler will show what is travelling "down the wire", so you are still no better off.Put this way: I'm pretty sure I could steal your database application if I wanted to, and had access.Could you own the SQL Box in their offices? So you have remote access to "manage it for them", they have no logins etc. Then as Russell has suggested - but they might as well outsource the box at that point (what's their objection?)As I type this I just don't see this working. Why do they want this arrangement? To have access to the data (to share with other applications)They worry that your lot might go "under a bus" and then they think they may be able to patch up & mend whilst they find another solutionThey worry that their data, on external servers, might be stolen by someone elseThey are paranoid / have always done it that way (I doubt it!)(3) is probably a valid point. The rest aren't. Its surprising what Big Company will come round to, its just that it is outside their normal comfort zone IME |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 02:48:51
|
In my opinion you have two options: Say yes: you will give them the database and sysadmins and whoever is at their mercy will have full access to everything. You will have to write a foolproof contract stating exactly what they are allowed to do and what they are not allowed to do. If it's a big and serious company this should not be a problem. Besides; what is so secret about this database that the client isn't allowed to view its own data? The client purchases a system usually because they don't have time/money or it doesn't fit their business to build one of their own...not because the system has some feature that they *really* want to know how work...Say no: giving them access to the database is out of the question and they will have to deal with it. If they want to use your system they can but it will not be by hosting the database. Plain and simple.EDIT: as a matter of fact you only have to options, that's not just my opinion  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
dextrous
Starting Member
15 Posts |
Posted - 2010-02-02 : 03:53:43
|
| kristen,thanks for the thoughtful response. the thing with our line of work (marketing segmentation) is that a lot of our data are profiles of people/zip+4, etc. that's stored in the database. using the .net application, the client can retrieve small parts of that data and pay for that data. the problem isn't so much that the company itself would steal it but that one of their minion workers can take the database and--boom--all our work is gone. basically, it is highly db-driven app but we don't sell the data en bloc, rather sell the applications that we build on top of the data as that's far more profitable.somehow, the competitor has been able to supply their software as a "desktop app" for some years now, so the large companies we are talking with are demanding the same model if they switch from the competitor. except that our competitor is not using a db management software, such as sql or oracle--the software is stand-alone. i'm not sure exactly what they're using but i know that when the application starts it takes a little while to load (something about decrypting tables?)--i have only seen it briefly in action.i *assumed* they could be using indexed text file, but i have never done that before and wouldn't know where to start. any tips on that line of thought?i do like your idea of providing them the box itself, i think they will be agreeable to that. how would we go about maintaining it in case a power failure of frozen screen appears? are there ways to remotely control the switch? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 04:23:06
|
| Ok, you're in the business of selling *information* and not a particular system itself which makes my previous comment completely obsolete. Sorry about that. So I totally agree...giving them access to the database could possibly ruin your business and you would have to find other options. Isn't it possible that you give the the desktop application that they ask for but you host the database on a server controlled by you somewhere? We're talking 2010 here and bandwidth is (usually) not that big of a problem...and a client app can easily connect to a database in a distant site across a vpn or something. I used to work for a company which had the majority of its production servers in Malta while I was in Norway and it worked great.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
psetterfield
Starting Member
7 Posts |
Posted - 2010-02-02 : 18:38:53
|
| I'm not sure if it would be an option to consider but there is an almost SQL Server compatible database system called VistaDB which may offer more protection for your database. It supports database passwords for example. (If you did go down this route, you should definately try to encrypt the password in your App because .Net apps are easily decompiled).It's a .Net DLL which you can embed in your app, but I believe you can buy the code as well so you could in theory implement your own encryption on the data that is stored.http://www.vistadb.net/Hope this helps. |
|
|
|
|
|
|
|
|
Hiding Table columns and structure