Multi Tenant SQLMembershipProvider ASP.NET MVC

Question

I am trying to migrate to Azure (including SQL Azure) with a multi-tenant ASP.NET MVC application. Each client receives its own database, which is autonomous, including all their credentials.

We can set the connection string for SqlMembershipProvider when initializing the SqlMembershipProvider object. However, subsequent requests to various subdomains (in the same session) do not change the connection string. I 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 it ... but we would like client credentials to be isolated in this SAAS model.

So the question is how to dynamically change the SQLMembershipProviders connection string for each query?

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, which handles 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 wrote.

In the 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 user member Provider, instead you get BaseType

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

I'm not sure if this is the most suitable solution, but it seems like work is being done so that the dynamic connectionString is activated for memberProvider without folding its own. Feels a bit hacky, though.

Answer 2

You need to get this at the beginning of the request loop in Application_PreRequestHandlerExecute

Pay attention to the code from http://forums.asp.net/p/997608/2209437.aspx

There are several ways: check the code of the proxy provider and use it (I don’t think this is what you said above)? SqlConnectionHelper (from the link above)

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

the other includes pre-authentication. You could save the line in the session (set it the first time you log in) and specify it in the provider or use reflection-based code if all else fails, although it does not look clean. If this does not work, you will need to collapse your own provider.