Using dynamic connection strings with the Asp.Net Sql Membership Provider

So you did it.  You drank the MS Kool Aid and decided to go full bore and implement the Asp.Net Membership Provider.

As with any out of the box solution like the membership provider there are alot of pro’s.  It gives you quite a bit out of the box:

• Ready made db for storing users, passwords, roles and more
• Login controls, forgot my password control, etc
• Lots of API classes to wrap users, roles, etc right out of the box
• Highly configurable

Sounds great right?  Now say you’re like alot of developers I know and you have one database for development, possibly localhost.  You probably also have a staging or QA environment where your testers can create 100 users and try to break everything.  Then you have production – the real deal.

You’ve decided to use the Sql Membership Provider, you right all your code hitting your localhost db.  Everything runs perfect.  All your unit tests pass.  (You’re writing unit tests right?)  Now the move to QA.  Well step 1 is to create the Membership database on QA.  Ok, let’s a assume you can do this or can get a DBA to do this.

Now what?  All your connection strings point to localhost.  You look at your web.config and ugh – it looks like this:

<connectionStrings>
  <add name="MySqlConnection"   connectionString="Data Source=localhost;  Initial Catalog=aspnetdb;Integrated Security=SSPI;" />
</connectionStrings>
<system.web>
...
  <membership defaultProvider="SqlProvider">
    <providers>
      <clear />
      <add
        name="SqlProvider"
        type="System.Web.Security.SqlMembershipProvider"
        connectionStringName="MySqlConnection"
        applicationName="MyApplication"
        enablePasswordRetrieval="false"
        enablePasswordReset="true"
        requiresQuestionAndAnswer="true"
        requiresUniqueEmail="true"
        passwordFormat="Hashed" />
    </providers>
  </membership>

Ok, cool, I’ll just add a connection string for QA.  Wait, what’s this membership section?  You’re kidding right?  I can only have one connection string for my membership provider?  In short, the answer is yes, you can only have one.

So what’s the fix?

The solution is what I like to call complicated simple.  In theory it seems complicated.  In execution it’s actually pretty simple.  You can create your own membership provider and fix the little “bug”.

Create my own?  But doesn’t that defeat the whole purpose?  not exactly, you create your own, but inherit from all of Microsoft’s classes and only override one very small bit of functionality – the connection string implementation.

Your new Membership Provider:

public class MembershipProvider:SqlMembershipProvider
{
public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
 {
  base.Initialize(name, config);  

  // Update the private connection string field in the base class.
   var connectionString = IAmInQABool ? QAString : DevString;
 // Set private property of Membership provider.
  var connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
 connectionStringField.SetValue(this, connectionString);
 	}
    }

Note: For the line where I set the connections string you’ll want to implement your own logic here.  Likely some sort of code to determine what system you’re running on and then grabbing the write connection string from your config file.

That’s it.  You’ve created your own Membership Provider.  Now all your really did was take everything MS was giving you by default and use a little reflection to override the connection string.  You new provider will work just like the old.  You can still use the login controls and all that good stuff.

The only last bit of config you’ll need is to update your web.config to not use the default membership provider but yours instead:

<membership defaultProvider="MembershipProvider">
	<providers>
	<clear/>
	<add name="MembershipProvider" type="MyFullNamespace.MembershipProvider, MyProject"          connectionStringName="ApplicationServices" enablePasswordRetrieval="true"          enablePasswordReset="true" requiresQuestionAndAnswer="false"          requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5"          minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0"          passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/"/>
	</providers>
</membership>
<profile>
	<providers>
	<clear/>
	<add name="MembershipProvider" type="MyFullNamespace.MembershipProvider, ResellerDashboard"          connectionStringName="ApplicationServices" applicationName="/"/>
	</providers>
</profile>

That’s all there is to it.  Would love to hear if anyone reads this post and implements this solution.