Multi Tenant SQLMembershipProvider ASP.NET MVC

Question

I am trying to move to Azure (including SQL Azure) with a multi-tenanted ASP.NET MVC application. Each customer gets their own database which is self-contained, including all of their membership credentials.

We are able to set the connection string to the SqlMembershipProvider on Initialization of the SqlMembershipProvider object. However subsequent requests to different sub domains (in the same session) do not change the connection string. I have found an example where the implementation overrides the SqlMembershipProviders ConnectionString but this is not possible in version 4.0 of the System.Web dll.

We could implement a single membership database and authenticate against that......but we would like to keep the customers credentials isolated in this SAAS model.

So the question is how do I change the SQLMembershipProviders connection string dynamically for each request?

Web.config

<membership defaultProvider="TenantMembershipProvider">
        <providers>
            <clear/>        
    <add name="TenantMembershipProvider" type="ABC.Infrastructure.MultiTenancy.TenantMembershipProvider, ABC"
         connectionStringName="ApplicationServices" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false"
         requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6"
         minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/"/>            

        </providers>
    </membership>

TenantMembershipProvider.cs that handles the initialization

public class TenantMembershipProvider : SqlMembershipProvider
{

    private SiteLinqSession _session;
    private MasterSession _masterSession;
    private static readonly Dictionary<string, Customer> _customers = new Dictionary<string, Customer>();
    private static string _host;


    public override void Initialize(string name, NameValueCollection config)
    {

        base.Initialize(name, config);

        string connectionString = GetConnectionString();
        FieldInfo connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
                    connectionStringField.SetValue(this, connectionString);

    }

    private string GetConnectionString()
    {
        var headers = HttpContext.Current.Request.Headers["Host"];
        string[] host = headers.Split('.');

        _host = host[0];

        if (_host == "127") _host = "demo";

        var customer = GetSite(_host);

        return BuildTenantConnectionString(customer.ConnectionSetting);

    }


    private Customer GetSite(string host)
    {
        Customer customer;

        //check dictionary if customer exists for the subdomain           
        _customers.TryGetValue(host, out customer);

        if (customer != null)
            return customer;

        //if not get the customer record and add it to the dictionary
        _masterSession = new MasterSession();
        var customers = _masterSession.All<Customer>();
        customer = customers.SingleOrDefault(x => x.SubDomain == _host);

        if (customer != null)
            _customers.Add(host, customer);

        return customer;
    }

    private string BuildTenantConnectionString(ConnectionSetting setting)
    {

        return string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", setting.DataSource, setting.Catalog, setting.Username, setting.Password);

    }
}

Answer 1

To save people some time from the link that Adam posted.

In your Global.asax file for the Application_PreRequestHandlerExecute event

    protected void Application_PreRequestHandlerExecute()
    {
        SetProviderConnectionString(GetConnectionString());
    }

    private void SetProviderConnectionString(string connectionString)
    {
        // Set private property of Membership, Role and Profile providers. Do not try this at home!!
        var connectionStringField = Membership.Provider.GetType().GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
        if (connectionStringField != null)
            connectionStringField.SetValue(Membership.Provider, connectionString);

        var roleField = Roles.Provider.GetType().GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
        if (roleField != null)
            roleField.SetValue(Roles.Provider, connectionString);

        var profileField = ProfileManager.Provider.GetType().GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
        if (profileField != null)
            profileField.SetValue(ProfileManager.Provider, connectionString);
    }

    private string GetConnectionString()
    {
        return string.Format("Data Source={0};", @".\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|demo.mdf;User Instance=true");
    }

if you created a custom membershipProvider then you would get the BaseType instead

   var connectionStringField = Membership.Provider.GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);

I'm not sure if this is the most appropriate solution, but it seems to get the job done to get dynamic connectionString enabled for the membershipProvider without rolling your own. Feels a little bit hackish though.

Answer 2

You need to get this early in the request cycle in Application_PreRequestHandlerExecute

Note the code from http://forums.asp.net/p/997608/2209437.aspx

There are several ways,one is to check out the sample provider code and use (I dont think this is what you referred to above?) SqlConnectionHelper (fron the link above)

internal static string GetConnectionString(string specifiedConnectionString, bool lookupConnectionString, bool appLevel)
{
   //Your Conn String goes here!!
   return Factory.ConnectionString;
}

the other involves the prerequestauthenticate. You could potentially store the string in the session (set it upon initial login) and reference it in the provider or use the reflection based code if all else fails although it doesn't seem clean. If those don't work then you'll need to roll your own provider.