I'm building a web application with .NET Core 3.1 and Angular 9. I would like to know if it is possible to use the internal .NET Core authentication / authorization mechanism to connect to an "authorization" database and based on the credentials provided by the user, connect to the database intended to be used for that user.
The layout is as follows: one database that contains which users can connect to what database.
Multiple structurally identical databases that the users can access based on their credentials.
This is required in order to split the data across multiple databases, as each database will contain different data but with the same structure. Something like the following picture.
The RDBMS to be used will be SQL Server 2019 Express. Would this approach require any modifications on the default mechanism that .NET Core 3.1 uses for user authentication? Should I consider a different approach based on the requirement?
Thank you in advance for your help and advice.
You can achieve this using Entityframework core and multiple DBContext and by passing different connectionstring. You can do user authentication with AuthDBContext which connects to auth db, and then based on condition use different dbcontext.
You can achieve this using other orm like Dapper as well, by passing different connection string to different SqlConnection object based on your scenario.