ASP.NET MVC - How to safely persist password of a user for SQL Server Authentication?

Question

I have an ASP.NET MVC application that uses a SQL Server backend. However, the authentication method for SQL Server is SQL Server Authentication, meaning I will need to ask for the user's password to connect to the database via my application.

Ideally, I don't want to ask for the password once, make a connection from the application to the database permanently for the session, and then forget the password altogether as I want to open and close the connection to the database when required and only when a database action is performed.

But obviously, I do not want to store their password in a Session object either, regardless if the application will be https connections and internally facing as that is a big no-no.

What is the safest way to persist the password for the duration of the session?

I can't change the authentication method, as ideally I would want to use Windows Authentication but this is not possible.

Answer 1

You have two options to solve this issue.

1. If you are using asp.net identity then you can add a custom claim to user identity which will be saved for the session in the authentication cookie. Look for the examples and articles here:

How to add claims in Asp.Net Identity

Adding Custom Claims when Logging In with Asp.Net Core Identity Cookie

2. If you are not using Asp.net Identity, rather following the old style Forms authentication, you can add it as custom user data in authentication cookie.

How to store some custom data in form auth cookie

User data saved in authentication cookie will be encrypted, can be retrieved easily and will be discarded after logout or session expiry. It will also be thread safe in case you application is hosted on a server farm unlike a session variable.