Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

  1. 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.
  2. 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.

share|improve this question
    
On the topic of bindings; not using ORM like NHibernate is (I think) a non issue. If you use web services as an example, you will find many ways to efficiently bind your data to XML. –  jasonk May 28 '12 at 22:06
add comment

5 Answers 5

up vote 3 down vote accepted

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.

share|improve this answer
    
I concede that it need not be a performance bottleneck, but it certainly adds an extra layer to the architecture, which means a lot more maintenance. –  Scott Whitlock May 28 '12 at 18:14
1  
It adds a layer, but not necessarily maintenance. Consider that with all the logic placed in the service, not the client, changes can be 'rolled out' without requiring users to update their client applications. –  GrandmasterB May 28 '12 at 20:30
add comment

One approach is to use AD groups and stored procedures to limit what the user can do -- for instance your time sheet DB, could allow inset, update and deletes of the users hours, but not allow updating of anyone else's hours. The user's ID would be provided by the DB engine, user would have no direct access to the DB tables, just to sp's that ran queries based upon their login id.

Of course this isn't always feasible, but it can be. The best approach will depend upon your requirements and resources.

share|improve this answer
    
This is something I hadn't considered. I'm not sure it's a great fit, but it would work. –  Scott Whitlock May 29 '12 at 15:48
add comment

What you hint at as 'web service' is called n-tier architecture. Its generally the way to go in cases where security or configuration issues are likely (for ex, distribution of an application across many offices). It does not have to be 'web based', though. Many work with other protocols.

You create an application server to act as an intermediary between the client and the database (and other resources). The application server handles your application based authentication and performs actions on behalf of the client. In fact, ideally you wouldnt be doing any SQL in your client - rather you call methods on the app server. The application server would handle all data manipulation.

There's a number of benefits to the approach. You dont need to configure database connections and drivers on clients. You dont store database users, passwords, & servers. Configuration of the clients isnt even necessary - just point them in code to the right url or address. Also, with the 'logic' in the application server, you dont have to repeat yourself when developing other applications - the same app server can be reused by different types of clients.

share|improve this answer
add comment

Using certificate based authentication is the "correct" way of implementing a shared sql account. The goal is it eliminate the password use for this kind of thing.

Update:

I suppose misunderstood the question. I thought it had to do with trying to find an alternative to putting a db username and password either in an application config, or backed into the application itself.

You can eliminate the problem of managing passwords in applications by using client side certificates instead. The cert itself is not enough, you have to have a distribution and management system capable of operations like cert revocation.

Reference: http://en.wikipedia.org/wiki/Public-key_infrastructure

share|improve this answer
    
Can you provide some more info on this? Sounds like it might be orthogonal to the intent of my original question, but it's interesting. –  Scott Whitlock May 29 '12 at 15:49
add comment

Building a new desktop security solution, we opted for the web service solution I´ll try to describe bellow.

We compile the desktop application executables in a separate environment from the developers. And calculate a HASH from that executable that is recorded to the database.

One web service that provides all the needed information for the application to run, the DB password, connection string information, user permissions, etc...

we use single DB log in per application and record the user details in the database in session variables to be able to audit records.

A DLL handles all the communication from the desktop application to the web-service, that is only accessible with a token build into the DLL.

To be able to get the application DB password from the web service the DLL calculates the DLL callers HASH in runtime and passes as a parameter to the web service that validates the DLL token and the executable runtime calculated HASH to the one recorded when it was deployed (the application is only available in a single network shared installation).

That way we fell it is a good solution to the security problem we where most concerned with and are well aware of a few designs flaws. Are are almost finishing this implementation and so far we are happy with the results.

Edit: You could replace the hash idea by using digital signatures and X.509 certificates.

share|improve this answer
    
It seems pretty obvious where the glaring security hole is. The DLL you speak of is on the client's system and there's no way for your server code to verify that it's talking to a legitimate copy of the DLL or a hacked/malicious/fake one. You just created a lot of work for yourself without adding much, if any, extra security. All a malicious person needs is the token and algorithm, both of which are in the DLL for anyone who wants to look. –  Scott Whitlock Feb 17 at 15:20
    
@ScottWhitlock, Yes, I agree. we are looking into obfuscating the DLL, and the traffic going over HTTPS. We are trying to improve on that, I´d really like any input in how to improve it. But that solution already solves a lot of issues the current system has, including plain text passwords stored in network files. Also the webservice allows reuse of a lot of code being acessible by any of the client languages we use here, Including Delphi and Clipper (Harbor) clients! –  Varbex Feb 17 at 16:23
    
In your system, the user logs in and is presumably authenticated by the web service. Assuming use of HTTPS, isn't that good enough? You don't have to trust the client software, since you know that the user is who they say they are, and you control the web service, so make sure that web service only hands out information that the given user is authorized to see. Even if they reverse-engineered the client and wrote their own, what damage could they do? Only your web service knows the DB password, and that should be safe. –  Scott Whitlock Feb 17 at 21:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.