After successfully logging in, every user in the application receives an authorization token and dedicated SQL server connection.
When Node server receives a request, it checks for authorization token, and if it's valid - makes a request to the database using a connection, that has been dedicated for the user.
The token and the connection is stored in the regular JSON object, which is cleared after Node server has been restarted. Therefore, users have to enter their SQL sever credentials once again.
Since my API is not stable, I would need to restart Node server frequently, and that would cause some inconvenience to users.
The reason I am using dedicated connection is the ability to manage users' access on the SQL server level (granting access to certain tables or views), and to use SUSER_SNAME() function in order to log users' activities. This approach also allows me to implement a row-level security model.
Or maybe there is a way to store some kind of token for the SQL sever session, that would allow to connect to the server without providing user credentials?
Thanks in advance!
You can't store a database connection during a server restart. The connection belongs to a particular process so when that process is killed the connection is killed too.
There are some things you could do:
Move the database connections to a custom proxy process that does not get restarted and your node app just reconnects to the proxy. This would allow the actual database connections to be retained in the proxy while your node.js server restarts.
Store the user credentials in a local database so when your node.js server restarts, you can reconnect to the database without asking the user to supply their credentials again. This has obvious security implications.
Fix the stability of your API so your node.js server does not have to be restarted very often.