Setting the ASP.NET Memberships DatabaseName or Connection String Dynamically in C#

Question

I've successfully built many applications that use the ASP.NET Membership Provider. I'm diving into a new project that is multi-tenant and I'm making scalable by partitioning user accounts across multiple SQL Server databases.

Each database has the Memberships schema installed, and I have no problem setting any one of the databases as my Membership provider by adding the information in my Web.Config file. However, my goal is to use a master database as a look-up table. This database has a table called "Accounts" and I store the membership database for that account in a field called "MebershipPartition" that is associated with users on that account in that table (Each account can have many users).

Here is the basic architecture, with extraneous information removed:

What I am trying to do is set my Membership provider to that particular provider database via an updated connection string (or even just the databaseName since this is all on the same SQL Server) AFTER I do the lookup. This means that it has to happen outside of the Web.Config and the Global.asax file.

I also cannot pre-populate my Web.Config with a list of available connection strings because the system may generate a new Membership Database whenever certain criteria is met, such as reaching a certain cap on a previous database or of a particular account requires that it's users are isolated to an individual database. So this has to be completely dynamic.

In my research I've tried a number of methods which are detailed in the following posts:

StackOverflow, ASP.NET Forums and ASP.NET Forums

None of the above solutions work as desired. Either they rely on the solution to execute too early on in the application life-cycle, or they use hard coded values for the connection string and only solve the problem of removing the reliance on the Web.Config.

IDEALLY I would like to expose a single method that I can call at any time from a C# class to simply switch the database that the Membership system uses as needed, for example:

string Membership_DB_1 = "Server=[server];Database=database1;User ID=[userid];Password=[password];Trusted_Connection=False;Encrypt=True;"
string Membership_DB_2 = "Server=[server];Database=database2;User ID=[userid];Password=[password];Trusted_Connection=False;Encrypt=True;"
string Membership_DB_3 = "Server=[server];Database=database3;User ID=[userid];Password=[password];Trusted_Connection=False;Encrypt=True;"

Membership.SwitchConnectionString(Membership_DB_1);
Membership.SwitchConnectionString(Membership_DB_2);
Membership.SwitchConnectionString(Membership_DB_3);

I'd like to thank the community in advance, I have spent many days (and now weeks) wrestling with this scenario and I am near the point of pivoting to another solution!

UPDATED:

I've also been looking into the following solution, but again - it requires setting up the Web.Config with all potential connection strings in advance, which is not desirable from a management perspective:

Another Hard Coded Solution

Is this just not possible? Next I'll be looking into rolling my own version of the Membership Provider by using the Provider Toolkit Samples provided by Microsoft, but there aren't many samples or documentation available, and the actual downloads pages are missing in action! Scott Guthrie posted about it in 2005, but as you can see the reference in the article is now missing. I ended up finding a Download Link to the MSI here, but I'm not sure that the code is well maintained or even still relevant...

I was trying to avoid having to get this deep into the Provider components, but I plan to keep marching on! Will keep this thread updated as I go for any others looking to do the same!

Answer 1

Your proposed approach for switching connection strings:

Membership.SwitchConnectionString(Membership_DB_1);

doesn't sound practicable. Don't forget, there is a single static default provider which may be accessed concurrently from multiple request threads, and if you call this method you'll be changing the provider for all threads, which is unlikely to be what you want.

I must admit I don't see how your proposed architecture will increase scalability, but if you do want to do this, I think you're going to have to write a custom Provider.