question

Stesvis-5434 avatar image
0 Votes"
Stesvis-5434 asked ErlandSommarskog commented

One login for multiple DBs

I am looking for some advice, not for code snippets etc but just some help with the right approach.

We have two projects, PA and PB that we created for different audience and purposes.
Now our clients realized that they can use both our apps after we implemented some integration between the two.

So the current situation is that some users only use PA, some only use PB, and some use both.
We are building a "dashboard project" where users can go and log in.

Requirement: once they log in, this dashboard is supposed to detect if their credentials are valid for PA or PB or both.

Problem: users most likely have different credentials for PA and PB, so one common login would not work.

Question: how would you approach this?? Is it even possible?? They're asking me to implement it but i have no clue how to achieve that...thx!



sql-server-generaldotnet-aspnet-mvc
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would centralize the logins in another DB. Then use OAuth/OIDC for single sign on.


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

I guess that you could simply try both logins and see what you can access. But there many unknowns (for me) here, so my reply may be completely off base.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I was thinking to do that (like when you add accounts to your email client and then access gmail, hotmail etc from on app).

And then store the access tokens and use them to consume the APIs...?

0 Votes 0 ·

It is unlikely you'll be able to programmatically figure out every matching user account between the two systems. Also there are unknowns. Do the schemas match, how are roles/claims are handled in each application, are the passwords hashed, etc. You could have more work to do depending on the answer these and other questions that pop up.

I would try the old 80% rule and get most of the existing population as possible. Handle the remaining as one-offs.

Centralize the user accounts and claims in a new database. This is a more modern less hacky approach and there are out-of-the-box OAuth libraries for dealing with SSO in .NET.

The user contacts an admin if the user feels they should have access to an application. It's the admins responsibility to verify and authorize the user. You can also provide login validation from the new login page to either of the older login stores. If the user can successfully login to an older login store then the user is granted access to that system in the new system. You might have to retire older accounts at some point.

Anyway, that's how I would approach the problem.

0 Votes 0 ·

I realise now that we don't know what sort of credentials we are talking about.

Are they SQL logins?

Are they users that are authenticated by the application, and the application, as Tom says, logs into SQL Server?

Or are they using Windows authentication?

In the latter case, it would be as simple as I suggested. And as I mainly been working with applications which uses Windows authentication, this seemed natural to me.

But if they have a username/password for both applications, you will need to ask them supply both I guess. Or rework the existing solutions to use Windows auth.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

Normally, users do not login to the SQL Server. The APPLICATION logs into SQL Server as a generic user, and user security is handled by your APPLICATION, not SQL Server.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered LanHuang-MSFT edited

Hi @Stesvis-5434,
Maybe you can make the two databases trust each other,
or combine two databases into one database.


Best regards,
Lan Huang


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.