How to handle database connection password?

Question

I have a question about using databases in my application. I have the User table which stores passwords for my users. I know how to handle user authentication, but how to handle the password for the database itself? I searched online, and all articles mention only storing user passwords, not the database password itself.

To be more specific, let's say I'm working on an application that requires the database password to access the database, and only then authenticates the user. Where and how to safely store it/check it?

• Dan Wilson: Typically you would store the connection string in a settings file or use integrated authentication (service account).
• Arseni Mourzenko: And why are you storing users' passwords in the first place? Those are users' passwords. You shouldn't store them, and you shouldn't know them.
• Robert Harvey: Your question is probably too broad to be answerable here, for several reasons. We use Crypto in the .NET Framework to encrypt ours, and store the password so encrypted in an external file. It's not perfectly secure, of course; a determined person could reverse-engineer the code and get at the password, but it deters all but the most committed folks, and our data is not that critical or attractive to an attacker. For "better" security, the database needs to be taken out of the client, and accessed by an intermediary on the server like a Service Layer.
• Robert Harvey: security.stackexchange.com/questions/35235
• M.R.: Robert, that answers my question, thank you all for trying to help.

Answer 1

What you've come up against is one of the fundamental problems of client-server architecture. In order for the client machine to access the database, you need to have credentials for that database on the client machine. As accepted answer in to the that question provided by Robert Harvey explains, there's no way to completely prevent the user of the application from getting those credentials.

This is one problem that is made easier by moving to a N-tier architecture. Other than that, I see two main approaches you can take:

1. Create database credentials for each user. Then there is no need to store the DB credentials on the client machine. Logging into the DB could then take the place of managing user passwords in a table. You would then need to manage the rights within the DB for each user to prevent them from being able to access or change data outside of their authority.

2. Encrypt the database password using the clients credentials. This is extremely error-prone (especially around credential updates (both user and DB). Ideally you would find a well tested pre-built solution for this but I doubt you'll find one because such a solution still leaves you with the reality that someone with user credentials (legitimately or otherwise) can intercept the database credentials. You will need to consider what access those database credentials provide and what someone with them could do to your database.

It might not be easy but the preferred approach to improve security here is to move to a more contemporary architecture.

• Felipe Pereira: Option 1 looks like an egg/chicken problem, since one still needs db access to create the credentials for a new user (although I might be missing something since I haven't seen this coded anywhere for reference)
• JimmyJames: @FelipePereira This is assuming that there's some sort of administrator managing this. In these kind of solutions, I would expect that you would know your user i.e. you get access by requesting it, it's not a self-service situation. If it were the latter, a client-server approach is really problematic from a security perspective.
• Andy: @felipepereira not necessarily if all clients are part of something like active directory, then the ad infrastructure takes care of the authentication for you, and your client app picks up on the login of the windows desktop.