What are the best practices on MS-SQL when Windows Authentications is not an option?

Question

What is the best option for a windows application that uses SQL server authentication? Should I create a single SQL account and manage the users inside the application (using a users table). Or should I create a SQL server account for each user. What is your experience? Thank you!

Answer 1

What about having SQL accounts based on the level of permissions needed for the task. For example you could have a read only account just used for reporting if your system has a lot of reporting. You would also need an account what has write access for people to change their passwords and other user admin tasks.

If you have situations where certain users are only going to have access to certain data I would have separate accounts for that data. The problem with using 1 account is you are saying that there is no SQL injection anywhere in your application. That is something everyone would strive for but sometimes perfect security is not possible, hence the multi-pronged approach.

Answer 2

One option that I have used in the past is to use the ASP.NET Membership Provider. It makes authentication a breeze to use. The only drawback that I saw was that it added a bunch of tables to your database.

The code for using it is very straight-forward.

Here's a blog post about using this in a Windows app. http://msmvps.com/blogs/theproblemsolver/archive/2006/01/12/80905.aspx Here's another article with more details. http://www.c-sharpcorner.com/UploadFile/jmcfet/Provider-basedASP.NET10162006104542AM/Provider-basedASP.NET.aspx

Here's another article that talks about using it with Windows applications: http://www.theproblemsolver.nl/usingthemembershipproviderinwinforms.htm

Google for "ASP.NET 2.0 Membership Provider", and you will get plenty of hits.

Answer 3

Depends on whether the username/password for the SQL server would be exposed to the user, and whether that would be a problem. Generally for internal apps (in smaller organisations), one would trust the users not too log in directly to the sql server. If you have a middleware layer (ie webservices) the password can be hidden from user.

I prefer to use a general login for the DB and manage users in the application. Even if you do create a login to sql for each application user, they could still connect directly, so why not just use a generic sql login that is easier to manage. This is of course assuming all users have the same accesses.

One good practice, if the users potentially can get direct access to the db, would be to grant access only through Stored Procedures and not directly to tables, so that only certain actions can be performed. Steer away from writing business logic or security checks (except basic ones) within the stored procs.

One way I would solve your problem is to write some webservices that check security and does your CRUD (via datasets, etc), but again it depends on the app and environment.

In summary if you have a middle layer or all users have the same access manage the user within the application and use a single user login. Otherwise use a login per user or role.