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.