One login for multiple DBs

Question

One login for multiple DBs

Stesvis 1,041

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!

AgaveJoe 30,126 Reputation points

2021-08-30T17:53:20.78+00:00

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

3 answers

Your answer

AgaveJoe 30,126 Reputation points

2021-08-30T17:53:20.78+00:00

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

Answer 1

Erland Sommarskog 122K MVP Volunteer Moderator

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.

Stesvis 1,041 Reputation points

2021-08-31T01:10:46.4+00:00

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...?
AgaveJoe 30,126 Reputation points

2021-08-31T13:39:07.663+00:00

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.
Erland Sommarskog 122K Reputation points MVP Volunteer Moderator

2021-08-31T21:13:58.94+00:00

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.

Answer 2

Tom Phillips 17,771

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.

Answer 3

Lan Huang-MSFT 30,191 Microsoft External Staff

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

Best regards,
Lan Huang

Share via

One login for multiple DBs

3 answers

Your answer