I just went though this myself. Assuming you’re using SQL Server, I actually created user accounts on the database server for every user (you can do this from t-sql). I have one general user account that I can use to first verify that I can make a connection, and select from a Version table that makes sure the version of the software running matches the database schema version.

Users who have the ability to add/remove users in the application also need to have rights to administrate users on the database server. This is actually a problem since the users are defined at the database server level, not at the database level. That means someone with the ability to add/remove database server users could potentially create a new user that could access another database. For this reason, I suggest running a separate instance of the database server for this.

There are other ways to go about it:

• If your users login to Windows with unique accounts, you can use integrated security where they just authenticate with the Windows credentials. Ideal if it works for you (we had some shared computers, so it didn’t).
• You can have a common user and hard-code the credentials in the connection string, but only give that user access to stored procedures, not tables. Then in the stored procedures you have to pass login credentials every time, and the stored proc does the authentication. This has the advantage of allowing more granular security (you can limit what rows they can get back, so for instance, they wouldn’t be able to see another user’s timesheet records, but they could see their own).
• If you need only rudimentary protection of some data, just have two users: NormalUser and AdministratorUser. Only store the NormalUser’s password in the connection string. When you need to do something that requires administrator level permissions, have them enter the AdministratorUser’s password, and open a new connection.
• Put the database into local-only mode (no remote connections) and create a web service that runs on that machine and acts as your data access layer. This will let you do really advanced security (gives you full control over what a user can see) and it means the rest of your application basically sees the data as business objects rather than rows and columns. The web service, obviously, needs to handle the authentication.

Put your credentials information in the <connectionString /> on the app.config file. Then, proceed to encrypt the <connectionStrings /> section.

See the following article for more information: Encrypting Passwords in a .NET app.config File

The best practice is to give each user their own account on the database, and to set permissions on that account to restrict their access as you see fit.

The user’s credentials go into a configuration file for the application.

Can you use integrated authentication? For example, in SQL Server 2008 R2, the following connection string accesses the local master database using Windows credentials.

Data Source=localhost;Initial Catalog=master;Trusted_Connection=true

And I would never recommend creating SQL users for an application. You are now giving direct database access to your users, who should always be using the application (unless you explicitly allow them direct access). Additionally you are restricted by what tools SQL offers you for authentication. A common problem is per-row access, your application can do the filtering pretty easily, but creating stored procedures to duplicate it is non-trivial.

To be fair though, you do need to worry about escalation of rights attacks when using a proxied account.