7

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.

RoyalSwish
  • 1,503
  • 10
  • 31
  • 57
  • is storing connection string in `web.config` an option? you could [encrypt](https://learn.microsoft.com/en-us/previous-versions/aspnet/zhhddkxy(v%3Dvs.100)) sensitive sections and/or use CI/CD tool to ensure noone has access to that machine – timur Jul 11 '20 at 13:16
  • what hinders you to do it indirectly the normal way? so when the user sets his PW, you'd hash it and set the hashed version as the SQL Server PW of the user. when the user then logs in you'd only have to store the hashed version, for the duration of the session. so, are there any other applications the user might use to login from or might they even connect directly to the DB? – Patrick Beynio Jul 12 '20 at 03:41
  • You can use cookie – Vivek Nuna Jul 12 '20 at 21:53
  • @PatrickBeynio the user has access to WPF applications that use this password to log in. Furthermore they can connect directly to the DB as well through SSMS. Their passwords are initially assigned by the DBA of the server, and the user then has to change it upon first login to the server via SSMS - so I cannot hash it for them and set this as the password. I don't have DBA level permissions to do so either – RoyalSwish Jul 13 '20 at 12:23
  • @timur that's potentially an option, I may have to investigate this further and see if it's viable – RoyalSwish Jul 13 '20 at 12:26
  • You could hash the value and use it in SecureString, then it would be gone when not needed https://learn.microsoft.com/en-us/dotnet/api/system.security.securestring?redirectedfrom=MSDN&view=netcore-3.1 – Patrick Goode Jul 13 '20 at 22:12
  • @PatrickGoode the article states not to consider the use of SecureString anymore due to vulnerabilities – RoyalSwish Jul 16 '20 at 12:57

1 Answers1

3

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

  1. 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.

TejSoft
  • 3,213
  • 6
  • 34
  • 58