How to ensure DB security for a Windows Forms application?

Question

The basic setup is classic - you're creating a Windows Forms application that connects to a DB and does all kinds of enterprise-y stuff. Naturally, such an application will have many users with different access rights in the DB, and each with their own login name and password.

So how do you implement this? One way is to create a DB login for every application user, but that's a pretty serious thing to do, which even requires admin rights on the DB server, etc. If the DB server hosts several applications, the admins are quite likely not to be happy with this.

In the web world typically one creates his own "Users" table which contains all the necessary info, and uses one fixed DB login for all interaction. That is all nice for a web app, but a windows forms can't hide this master login information, negating security altogether. (It can try to hide, but all such attempts are easily broken with a bit of effort).

So... is there some middle way? Perhaps logging in with a fixed login, and then elevating priviledges from a special stored procedure which checks the username and password?

Added: OK, so integrated authentication and windows groups seem to be a fair choice in most situations, so I accepted the relevant answer. Still, if anyone can come up with a non-integrated authentication solution, they'll get an upvote from me.

Answer 1

Can't you use Active Directory Groups to make the management of the db connections easier?

From MSDN...

The name can be a Windows user name or a Windows group name, of the form DOMAIN\Name.

That way you may have a few groups, read-only, editor, manager, admin etc. It's how I have achieved similar things in that past with Click-Once apps.

It really is the best choice for what you are doing.

I am assuming this is an existing app? If it was new I would otherwise say to go client server with web-services or similar.

PK :-)

Answer 2

For WinForms use Windows groups. No passwords are needed because the credentials are inferred from the Windows login using your application.

This is best practice

Basically:

• The user belongs to a group (assumes single domain)
• Group is a login in the SQL Instance
• SQL login maps to a database user
• DB User belongs to a DB role
• Role has object permissions

It's worth reading up first before having someone try to capture all the information here

Edit:

If you have a workgroup, you can still do it by setting up sqlbox\bob, sqlbox\hans etc in a sqlbox local group.

When someone tries to connect (say bob on his PC) windows will ask them for their details. As long as bob knows his SQLbox account detailsm he can connect.

But then, I've not tried this in a workgroup setting...

Answer 3

In addition to using Windows Domain/AD Groups (put the AD groups in appropriate roles you create in SQL Server, so all account maintenance moves to AD), be sure to use the Application Name in your connection string - this allows you to see which applications are performing operations in the profiler etc.

Because when everyone is logging on as themselves from different applications - windows and web, it helps to know that it's actions taking place through an application and not just any user's ad hoc query through ODBC and Excel, say (if you allow users access to certain views for data export or report writing).

Answer 4

With regards to wanting to hide your authentication and use a single application login with WinForms, if the login has very little privilege - select only on views and execute only on stored procedures, anyone who manages to reverse engineer your encryption of the login information in your application will only be able to perform the same functions they could perform in your application. If you absolutely have to raise the level on the security, you could authenticate each stored proc against your users table (poassing user and hash to each SP). Also, rotate the central application login on a regular basis.

All this is a lot more difficult than using integrated authentication and implementing AD platform in your environment. So you're effectively writing your own directory and authentication instead of using one off the shelf.

In addition to gbn's update about workgroup versus domain, you can run the application with RUNAS /NETONLY /USER:SERVER\USER with user credentials on the domain or server which your machine is not a member of. At the point that the application makes the connection to the database, the remote credentials will be authenticated and used. I have an application which actually checks to see how it was run and if it not run with a particular switch, it prompts for username and password and then re-runs itself using the Windows API using a function equivalent to RUNAS /NETONLY /USER:DOMAIN\USER. This is because our workstations are not currently on the domain (or on a domain with a trust relationship) of the SQL Server. In this case, you could still manage the security in local groups on the SQL Server or groups on the domain of the SQL Server. You would basically just lose out on the automated authentication token.