Easiest way to implement this dynamic login scheme for asp.net

Question

I currently have a site where different users can login, and depending on their sub domain, are presented with different data from different databases. I am overriding the SqlMembershipProvider to use a "temp" connection string, that I dynamically set during the Initialize Method, per this post's instructions:

http://forums.asp.net/p/997608/2209437.aspx

public override void Initialize(string name, NameValueCollection config)
    {
        // intercept the setting of the connection string so that we can set it ourselves...
        string specifiedConnectionString = config["connectionStringName"];

        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings[specifiedConnectionString];

        var fi = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);

        fi.SetValue(connectionString, false);

        connectionString.ConnectionString = WakeflyClientHelper.GetClientConnectionStringByCurrentSubdomain();

        config["connectionStringName"] = connectionString.Name;

        // Pass doctored config to base classes
        base.Initialize(name, config);
    }

The problem is that the SqlMembershipProvider class seems "static" in that when multiple users connect from different sub domains, they end up seeing the User Accounts from ONE of the databases, not each of their own. It looks like the Initialize is called by the application, and not on a per request basis.

So my question to you is... What would be the easiest way to implement a solution to this?

I have not written a custom provider before, so I'm not sure how that works, or what the limitations are. And if I write it myself, there is always the possibility of security holes if I overlook something (not to mention the time it will take). Any ideas?

Update 1: I could have a single database, however, the user's within a given sub domain need the ability to add/edit/delete user's within their own sub domain, without seeing other sub domains' users. Would that be possible under this scheme?

Answer 1

I have done exactly this using a single membership database to handle all the logins, and then hook them up to the appropriate database depending on the domain in which they logged in.

Answer 2

The code for the SQLMembershipProvider is provided here. You could extend/change what you think is necessary by adding the project to your solution and changing the namespace references in web.config.