I need to write an API to connect to an existing SQL database. The API will be written in ASP.Net MVC3.

The slight problem is that with existing users of the system, they may have a username on multiple databases.

Each company using the product gets a brand new instance of the database, but over the years (the system has been running for 10 years) there are quite a few users (hundreds) who have multiple usernames across multiple "companies" (things got fragmented obviously and sometimes a single Company has 5 "projects" that each have their own database).
Long story short, I need to be able to have a single unified user login that will allow existing users to access their information across all their projects.

The only thing I can think is storing a bunch of connection strings, but that feels like a really bad idea.

I'll have a new Database that will hold the "unified user" information...can anyone suggest a solid system architecture that can handle a setup like this?

2 Answers
2

Unifying the data

Since the authentication was independent between the five projects, you may encounter several inconsistencies:

Different identifiers. For example if the user identifiers are e-mail addresses, the same person can register with vanessa.kelley@example.com for one project, v.kelley@example.com for another one, and vanessa12345@example.com for a third one. It is practically impossible to solve this issue: you may have an idea through information aggregation¹, but it's not precise enough and may be insufficient in some cases².

Data standards inconsistency. What if both projects need to know where the user is, but the project 1 uses two letters convention, like CA, while the second one uses the full name, like Canada?

Different authentication implementations. For example, the project 1 uses SHA256, while the project 2 uses salt and SHA512 and the project 3 uses PBKDF2? In order to solve this issue, you'll need to store every hash used, which means having a table like this:

The good thing is that you may progressively consolidate the passwords for the users once they authenticate. For example, if the user successfully authenticate with the SHA1 used by the fifth project, you may compute and store the PBKDF2 hash and salt and remove the SHA1 hash from the database.

After a year or two, if there are still users who didn't logon for a while, you may send them an e-mail to tell that they must logon if they want to keep their account.

Different passwords. The consolidation may work only if the user is registered at one project, or if she uses the same password for every project. What if the passwords she use for different projects are not the same? You still need to keep the ability to logon with several passwords.

Different data. If Vanessa Kelley told that she lives in Toronto to the project 1, and in Montreal to the project 2, which one is obsolete?

If the inconsistencies are too important, there is a risk that you will never be able to create the unified logon. If there are few or no inconsistencies, than the task will be pretty easy.

Unifying the process

Once you unified the data, you may provide a common process for the five projects. You may want to create a web service which will be accessed by the five projects, or you can modify those projects to access the common database directly. It would be probably much better to use a web service, instead of letting different applications to access the database.

If you can't modify the original projects, too bad. You may sync the data between databases, but it's not easy and not error prone.

Still, you don't want your common logon process to use all the five databases. Not only it costs too much in terms of resources, but it also makes your whole process dependent on five databases. If one of them is down, the logon process would fail.

¹ Example: according to the logs, both logons were made from the same machine, one at 3:00 PM, the second one at 3:15 AM. Moreover, the browser headers were the same, and the first name, last name and birth date are the same.

² Example: if two logons share the same first and last name, it doesn't mean anything at all. Two people can share the same machine or use the same browser too.

Sorry, I should have explained better. The Data is unified. It's the same database strcutre for each "project" (Project for the client, not "project" in the sense of a group of code files). It's just a different physical database for each Project. Thanks for the fantastic answer!
–
James P. WrightMay 20 '12 at 18:00

If you're going to have users in a separate database, that's fine, but you must understand that referential integrity cannot be guaranteed. Therefore, you must do your best to maintain it (through application logic, distributed transaction, trigger, etc.), and you must keep the remote references minimized.

In other words, if you are going to have a foreign key that refers to a remote database, then it's best to create a table in your local database that associates the remote key with a local one, so that local tables can refer to the local key rather than directly to the remote one.

For example, suppose you have a remote key called "facebook_id" (and it's a GUID). Rather than have all your local tables reference it directly, create a table called local_facebook_id, which stores a locally unique id along with the facebook_id, then have all your local tables refer to your local id rather than directly to the facebook_id. The benefit is that you get local referential integrity to a single point of reference.

Futhermore, you can also expand that local_facebook_id table to cache credentials and other profile information, so that if the remote database goes missing or the user's account is deleted, users of your application could still be allowed to log in against the local_facebook_id table's credentials. Also, it would contain enough meaningful information about them that if the remote account gets permanently wiped out, your local data remains linked to a local source of meaningful personal information (rather than just a meaningless GUID), which could even be used later to re-link the local account to a new facebook_id (e.g. by email address or some other uniquely identifying information).