Geek Answers Handbook

What are the best practices for working with MS-SQL when Windows authentication is not an option?

What is the best option for a Windows application using SQL server authentication? Do I have to create a single SQL account and manage users inside the application (using the user table). Or should I create an SQL server account for each user. What is your experience? Thanks!

+4
source share
3 answers

Depends on whether the user / password for the SQL server is exposed to the user and whether this will be a problem. As a rule, for internal applications (in small organizations), you can trust users not to log in directly to the sql server too. If you have an intermediate layer (for example, web services), the password can be hidden from the user.

I prefer to use a common login for the database and manage users in the application. Even if you create a login for sql for each user of the application, they can still connect directly, so why not just use the common sql interface, which is easier to manage. This, of course, assumes that all users have the same access.

One good practice, if users can potentially get direct access to db, will provide access only through stored procedures, and not directly to tables, so that only certain actions can be performed. Get away from writing business logic or security checks (other than core ones) in stored procedures.

One way to solve your problem is to write several web services that check security, and your CRUD (via datasets, etc.), but again it depends on the application and the environment.

In general, if you have an average level, or all users have the same access, then manage the user in the application and use one user login. Otherwise, use the username for the user or role.

+3
source

How about having SQL accounts based on the level of permissions required for the task. For example, you may only have a read-only account used for reporting if your system has a lot of reports. You will also need an account that has write access for people to change their passwords and other user tasks.

If you have situations where certain users will have access only to certain data, I will have separate accounts for this data. The problem with using 1 account is that you say that SQL injection is missing in your application. This is something that everyone would strive for, but sometimes perfect security is impossible, therefore, a multi-purpose approach.

0
source

One option I've used in the past is to use an ASP.NET membership provider. This makes authentication easy. The only drawback I saw was that it added many tables to your database.

The code to use it is very simple.

Here is a blog post about using this in a Windows application. http://msmvps.com/blogs/theproblemsolver/archive/2006/01/12/80905.aspx Here is 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 in Windows applications: http://www.theproblemsolver.nl/usingthemembershipproviderinwinforms.htm

Google for "ASP.NET 2.0 Membership Provider" and you will get many hits.

0
source

More articles:


All Articles