1

I have a large project which has multiple web servers (one for each client), one application (for all clients), and multiple SQL databases (one for each client) as pictured below.

Server Setup.

For security purposes I need to have separate passwords and usernames. How can I safely store the passwords to the SQL Servers on the application server? I could encrypt the host, username, and password and keep the key on the application server, or would it be better to store the passwords encrypted on another SQL Server? And where should the encryption key be kept?

1

First you must make sure that you are not facing an x-y problem, because having one database per user behind the same application is not the most common design. It is much more common to share the database and let the application implement the user separation. Among other advantages, you have one single database connection, which greatly eases scalability and database maintenance.

If you really need that design, my prefered choice would be to let the password responsability to the users. If it is an option, the database password could simply be the web user password. That way you do not even have to store it and just create the database connection at login time and close it at logout (or end of web session). Alternatively, you could derive a key from each individual web password and use it to encrypt the database password for each user if you want to maintain them separate => result is the same, you need the web password that you do not store, to access to the database

If none of those are acceptable options, you will have to store the passwords in an invertible form on the application server, but it is a security nightmare. Your options are:

  • don't care at all: store the passwords in clear text and pray that nobody ever get access to those data - surprisingly it is a too common option(*)...
  • don't care much: same as above but encrypt the passwords with a fixed key hardcoded in the application - a little better because the attacker now needs two elements but one of them can never change
  • do your best: encrypt the passwords with a modifiable key. It could be read from the environment at boot time and changed on a regular base, say every month for example. But that means that when changing the master password you also must change all database passwords in a atomic way - ok, any database can do it, but beuhhh.... Alternatively you could mimic password vaults and encrypt a whole file at the cost that accessing an individual password will require a full decryption unless you accept to keep the full database in clear text in memory.

(*) ... but it can be an acceptable option if the application server and the database servers are in the same security zone.

0

Why not consider storing the credentials on the web server(clients) itself? The application server will act like a forwarding interface from the server to the client. Also, it will circumvent the application server to be one single point of failure in case the host goes down or even in case of an intrusion. This would help in freeing up the Application server from key management and shifting it to the client, if there is not intention of key management on the application server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.