For about 10 years I've worked on various in-house desktop client applications with SQL Server data stores. Rarely did I start these projects – most are takeover work.
One thing that seemed constant everywhere was that there was a single global SQL Server user account that this application used that granted it permission to the common database, and yes in some naive situations it used the sa user account, which I generally tried to fix when possible.
You can't really effectively hide this username and password that the application uses to access the database. They're usually stored in an ini or config file, or possibly baked into the executable itself. In all cases, they're visible to the user if they do a little digging. In one case we actually used a config file but encrypted it, but of course the encryption key had to be stored in the executable (we weren't naive to the limitations of this, but it did effectively stop people from poking around who were savvy enough to look in config files).
All of these systems had a user-authentication system built into the application, but of course they were all managed through the application itself, meaning the user information was stored in the database. The application restricted what things you could do based on your access level, but it's all kind of moot if you can just connect to the database and run ad-hoc queries.
I'm interested to know what other systems do to get around this problem. Here are the options I know of:
- Use SQL Server's security mechanism to maintain a user and roles list, and make the desktop application add and remove users through T-SQL queries.
- Instead of connecting directly to the database, create some kind of web service that runs on the server and put the authentication logic in there. Make every request do security validation.
The first options is a bit ugly because you're separating users from the database so users are no longer first class entities and you can't reference them with foreign key relationships, etc.
The second just seems like a major performance problem, and a lot of extra work, plus you can't as easily use ORM mappers like NHibernate (I think).
Does anyone have experience with this? Best practices?
Edit
Thinking a bit more, can SQL Server Authentication actually solve this problem? For instance, if your user must be able to insert and update timesheet records so you can edit your timesheet, there's no way SQL server can disallow access to other rows in the timesheet details table, meaning you can read and write other people's timesheets too.
Best Answer
I'm afraid adding a Web Service layer is probably the correct solution to your problem.
Separating the client from the underlying database implementation will probably help you in the long run too.
Adding a web service layer doesn't necessarily have to hurt performance...
Indeed, with an appropriate API, a web service can actually improve performance, by batching together multiple database queries within the data center LAN, rather than requiring multiple round trips over the WAN.
And of course a web service layer can often be scaled horizontally, and add appropriate caching to your database queries, perhaps even a change notification mechanism.
A server layer adds security that you cannot possibly ensure with apps running on a remote client. Anything that runs on a client can be "hacked" and should not really be considered in any way trusted. You should only really put presentation logic in the client, and host anything important on hardware you have complete control of.
I don't know about your apps, but my web apps are naturally split into several layers, with the presentation code separated from the persistence layer by at least one level of business logic that keeps the two apart. I find this makes it much easier to reason about my app, and so much faster to add or modify functionality. If the layers are separated anyway, it is relatively easy to keep the presentation layer in the client, and the rest on a server under my control.
So while you can solve your problems without introducing a "web service" layer, by the time you have written all the stored procedures (or equivalent) necessary to fill in the holes in the standard database security implementation, you would probably be better off writing a server-side application that you can write proper unit tests for.