Changing Membership, Profile and Role provider connection string based on environment

by 14. August 2010 00:59

You’re probably no stranger to having to target different environments for your project, e.g. development, qa, staging and production. Often your application relies on a configuration file, web.config for ASP.NET applications, and in this file you will usually have one or more connection strings, targeting a specific server based on the environment. There are several ways to solve this problem, e.g. by using nANT or pre-build events in Visual Studioand indeed I would recommend either of these approaches over the one I’m going to describe below.

However, what do you do if you are deploying your web-application to a deployment server, and that server is then responsible for deploying to two or more environments, e.g. staging and production, and you have now way to tell the server to distinguish between the different environments. In other words, you are stuck with one web.config file. This is not necessarily a problem, unless you are using the membership provider which in its configuration looks something like this:

<add name="SqlProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="SqlServices" applicationName="MyApplication" />

Notice the connectionStringName property. Turns out solving this is pretty straightforward, and Brendan Caffrey over at devspade.com posted an excellent article on how to do it: Using dynamic connection strings with the Asp.Net Sql Membership Provider

However, he only posted the solution for the Membership provider. What if you’re using the Role and profile provider as well? Luckily, the solution is just as simple. I won’t go into details, but just point to Brendan’s article, but here’s the entire source code.

First the actual source code:

using System;using System.Collections.Generic;using System.Diagnostics;using System.Linq;using System.Reflection;using System.Text;using System.Web;using System.Web.Configuration;using System.Web.Profile;using System.Web.Security;namespace YourNameSpace
{
public class CustomMembershipProvider : 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.
string connectionString = //DetermineYourConenctionString();
// Set private property of Membership provider.
var connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
connectionStringField.SetValue(this, connectionString);
}
}
public class CustomProfileProvider : SqlProfileProvider
{
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.
string connectionString = //DetermineYourConenctionString();
// Set private property of Membership provider.
var connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
connectionStringField.SetValue(this, connectionString);
}
}
public class CustomRoleProvider : SqlRoleProvider
{
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.
string connectionString = //DetermineYourConenctionString();
// Set private property of Membership provider.
var connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
connectionStringField.SetValue(this, connectionString);
}
}
}

And then the web.config:

<membership defaultProvider="CustomMembershipProvider">
<providers>
<clear/>
<add name="CustomMembershipProvider" type="YourNameSpace.CustomMembershipProvider, YourNameSpace" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/"/>
</providers>
</membership>
<profile defaultProvider="CustomProfileProvider">
<providers>
<clear/>
<add name="CustomProfileProvider" type="YourNameSpace.CustomProfileProvider, YourNameSpace" connectionStringName="ApplicationServices" applicationName="/"/>
</providers>
</profile>
<roleManager enabled="true" defaultProvider="CustomRoleProvider">
<providers>
<clear />
<add connectionStringName="ApplicationServices" applicationName="/" name="CustomRoleProvider" type="YourNameSpace.CustomRoleProvider, YourNameSpace" />
<add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</roleManager>

Have fun!

Tags: , , , ,

ASP.NET

ASP.NET 4 and MySQL Membership Provider

by 5. August 2010 01:07

Recently I had to setup an ASP.NET MVC 2 project which would utilize the built-in membership of ASP.NET. However, I didn't have access to a MS SQL database, so I had to use a MySQL data provider instead. The following is a quick guide on how to get it setup.

First, you need to download the MySQL Connector/Net from this page. This makes it possible to connect to MySQL databases from .NET applications and gives you access to the ADO.NET interfaces. I choose to download the latest development release (at the time of writing) Connector/Net 6.3.3 beta, as this fully integrates with Visual Studio 2010 which the latest public release (6.2.3 at the time of writing) does not. Download BOTH the source (mysql-connector-net-6.3.3-src.zip) and the installation file (mysql-connector-net-6.3.3.zip). I will explain why in a second.

Once you've downloaded both files, extract them and install the connector. Now, normally when using the membership provider, the database tables/schemas are automatically created. The MySQL membership provider does this as well, unfortunately it just doesn't do it right. At least it didn't work for me. Instead, you have to create the databases semi-manually. Go to the location where you extracted the source and browse to the following folder "\MySql.Web\Providers\Properties". In this folder you will see a number of .sql files: schema1.sql, schema2.sql, schema3.sql, schema4.sql, schema5.sql and schema6.sql. Run each of these, in turn and starting with schema1.sql, against your MySQL database.

Now, fire up Visual Studio 2010 and open your application. Add a reference to MySql.Web.dll which can be found in the directory you installed the Connector, e.g. C:\Program Files\MySQL\MySQL Connector Net 6.3.3\Assemblies\v2.0

Next, unless you haven’t done this already, add your MySQL connection string to the configuration/connectionStrings element in the Web.config, e.g.:

<connectionStrings>
<add name="MySQLConn" connectionString="Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"/>
</connectionStrings>

Finally, open up your Web.config and add these lines to the <system.web> element:

<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear/>
<add name="MySqlMembershipProvider" 
type="MySql.Web.Security.MySQLMembershipProvider,MySql.Web,Version=6.3.3.0, Culture=neutral,PublicKeyToken=c5687fc88969c44d" 
autogenerateschema="true" connectionStringName="MySQLConn" 
enablePasswordRetrieval="false" enablePasswordReset="true" 
requiresQuestionAndAnswer="false" requiresUniqueEmail="false" 
passwordFormat="Hashed" maxInvalidPasswordAttempts="5" 
minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" 
passwordAttemptWindow="10" passwordStrengthRegularExpression="" 
applicationName="/" />
</providers>
</membership>
<profile defaultProvider="MySqlProfileProvider">
<providers>
<clear/>
<add name="MySqlProfileProvider" 
type="MySql.Web.Profile.MySQLProfileProvider,MySql.Web,Version=6.3.3.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d" 
connectionStringName="MySQLConn" applicationName="/" />
</providers>
</profile>
<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
<providers>
<clear />
<add name="MySqlRoleProvider" 
type="MySql.Web.Security.MySQLRoleProvider,MySql.Web,Version=6.3.3.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d" 
connectionStringName="MySQLConn" applicationName="/" />
</providers>
</roleManager>

Now, you've (hopefully) got a fully working MySQL membership provider. To test it, go to Project > ASP.NET Configuration and go to the Security tab. Here you should be able to manage users and roles.

NOTE: Make sure you enter the correct connectionstring name, version number and PublicKeyToken in the Web.config. The version number is self-explanatory, but the PublicKeyToken can be a little trickier to figure out. The PublicKeyToken value is most likely the same, but to make sure see this link on how to find the PublicKeyToken for a .dll

Tags: , ,

ASP.NET | MySQL