Verifying user credentials in application that connects to database directly (local network)

Question

I am reading about the best way to store password information in database, and most of the examples say that we should not store the actual password in database, but hash and salt values. In a web application, the code that authenticates user is located on the server side, so we send user name and password over the wire, and on the server we read the salt value, then hash password that we received with salt, and compare it with stored hash value in db.

What if application is located on client, and application is connecting to database directly, not through some service (local network)? This means that Hash class would be exposed on the client, and I would need to read hash and salt value from db on the client, which kind of allows anyone to create a few lines of code and fetch all user info on client.

How is security/authentication handled in such scenario?

Answer 1

Windows Authentication

If the application is running within a Windows Domain you can use the Authenticated Windows User Information. Then in the database there is no need to store the username/password. You would only have to store the Windows SID to identify the user in the User table. Then the application would just have to verify that the current user exists in the database User table and if it doesn't then the application fails to run.

You can get the current user information by using WindowsIdentity.GetCurrent() (MSDN) and the User property will get you access to the SecurityIdentifier. The question Convert a username to a SID string in C# will show you how to convert that to a string that can be stored in the database. Since each Windows user has a unique SID it provides easy look-up for the security information.

When going this route, for extra security, it would be best to use Integrated Security for the SQL connection and then lock down access to the queries and tables so that only a certain usergroup can run the administration actions and add users and regular users can only execute read and data insert/update statements for related tables.

EDIT:

As Windows Authentication doesn't work for you, you can have SQL do the Hashing for you in a stored procedure. By using HASHBYTES you can pass in the username/password to the stored procedure and then have the validation done in SQL so the end user would never be able to see the logic. In the Stored Procedure you can pull the Salt value from a table or have it coded in. For instance, we use the Username, Password and another value in from the user information table to create the value that gets hashed. This prevents different users from having the same hash even if they have the same password. We also use SHA512 to generate the hash. Then you just need to lock down SQL access so that users cannot connect to SQL and do the Right-Click-->Modify on the stored procedure to view the source. This would probably work the best for your case.