How do you handle database security from a desktop application?

Question

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.

Answer 1

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.

Answer 2

Similar to the answer by jmoreno, you can deny a user access to everything aside from EXECUTE permissions on stored procedures, then take advantage of ownership chaining to have the stored procedure perform the required operations on the tables.

See here for details https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx

When the user enters their username/password client side, I store them and send as parameters to every stored procedure call. You can then verify them against values stored in a table before performing the desired operation.

Definitely not the last word in security, but might be necessary if your PCs have generic logins, limiting your ability to use AD groups for permissions, or you have limited access to AD itself.

Answer 3

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.

Answer 4

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.

Answer 5

Technology has changed a bit. If you authenticate each user to the database itself, and use database roles, you can now use what's called an Updatable View to solve this problem, at least in SQL Server.

Here's what an updatable view might look like for a table called SomeTable where each row in that table is linked to an employee. The employee should be able to see the rows linked to them, and members of the HR role should be able to see all rows, for instance:

CREATE VIEW [dbo].[vwSomeTable]
AS
    SELECT SomeTable.*
    FROM SomeTable
        INNER JOIN Employee ON SomeTable.Employee_ID = Employee.Employee_ID
    WHERE Employee.Username = USER_NAME() OR IS_MEMBER('HR_Role')=1

GO

Then what you do is give read (and possibly write) permissions on the view (vwSomeTable) to all users, and give no permissions on the table (SomeTable).

You can test this like this:

EXECUTE AS USER = 'Some_Regular_Username'
SELECT * FROM vwSomeTable

...which should only return their row(s). Or:

EXECUTE AS USER = 'Some_HR_Username'
SELECT * FROM vwSomeTable

...which will return all rows. Note that you'll need the execute as (impersonation) permissions to do this test.

The views are updatable, so even the regular user can do this, as long as the row is linked to their Employee row:

UPDATE vwSomeTable
SET SomeColumn = 5
WHERE SomeTable_ID = 'TheID'

Answer 6

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

Answer 7

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.