Widgets Setting Membership-Profile-Role provider's connection string at runtime


I dream in code

About the author

Robert Williams is an internet application developer for the Salem Web Network.
E-mail me Send mail
Go Daddy Deal of the Week: 30% off your order at! Offer expires 11/6/12

Recent comments




Code Project Associate Logo


The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Setting Membership-Profile-Role provider's connection string at runtime

Why would you want to do this? Well perhaps your application uses a separate API to retrieve its specific connection string (via key, for security purposes), or perhaps you have different connection strings for production and development and want to be able to test a value (such as Environment.MachineName) at runtime and load the appropriate connection string name into your membership and role providers. Listing 1 shows how a typical web.config might list its connectionstrings and providers.

Listing 1


    <!-- DEV -->

    <add name="DEV.DB" connectionString="Database=NORTHWIND;Server=xxx;User=xxx;Pwd=xxx;" providerName="System.Data.SqlClient"/>


    <!-- Live -->

    <add name="LIVE.DB" connectionString="Database=NORTHWIND;Server=xxx;User=xxx;Pwd=xxx;" providerName="System.Data.SqlClient"/>



<!-- ... -->


<membership defaultProvider="MyProvider">


        <add connectionStringName="DEV.DB" applicationName="/Test"

    description="MyProvider" requiresUniqueEmail="false" enablePasswordRetrieval="false"

    enablePasswordReset="false" requiresQuestionAndAnswer="false"

    passwordFormat="Hashed" name="MyProvider" type="Williablog.Net.Examples.Providers.SqlMembershipProvider" />





    <profile defaultProvider="MyProvider">


        <add connectionStringName="DEV.DB" applicationName="/Test" description="" name="MyProvider" type="Williablog.Net.Examples.Providers.SqlProfileProvider"/>




        <add name="FirstName" type="System.String" allowAnonymous="false"/>

        <add name="LastName" type="System.String" allowAnonymous="false"/>




    <roleManager enabled="true" defaultProvider="MyProvider" domain="xxx">


        <add connectionStringName="DEV.DB" applicationName="/Test"

    description="" name="MyProvider" type="Williablog.Net.Examples.Providers.SqlRoleProvider" />





      <personalization defaultProvider="MyProvider">


          <add connectionStringName="DEV.DB" name="MyProvider" type="Williablog.Net.Examples.Providers.SqlPersonalizationProvider"/>




So how can you change the connection strings at runtime?

When shopping around for an answer, I came across this entry in the Microsoft Forums which suggests downloading the SampleProviderToolkitSampleProviders from Microsoft, modifying the SQLConnectionHelper class to include your special handling of the connection string. And this was initially the road I went down, however, the SampleProviderToolkitSampleProviders does not include a SqlPersonalizationProvider example and it was when I decided to simply inherit the built in SqlPersonalizationProvider and make my own that I discovered an easier solution (Listing 2) that can be applied to any provider (as long as your connection strings can be made available in the connectionstrings section of the web.config.):

Listing 2

Imports System

Imports System.Collections.Specialized


Namespace Williablog.Net.Examples.Providers


    Public Class SqlMembershipProvider

        Inherits System.Web.Security.SqlMembershipProvider


        Public Overrides Sub Initialize(ByVal name As String, ByVal config As System.Collections.Specialized.NameValueCollection)

            ' intercept the setting of the connection string so that we can set it ourselves...

            Dim specifiedConnectionString As String = config.Item("connectionStringName")

            config.Item("connectionStringName") = GetYourRunTimeConnectionStringNAme(specifiedConnectionString)


            ' Pass doctored config to base classes

            MyBase.Initialize(name, config)

        End Sub

    End Class


End Namespace

All you really need to do is override the Initialize method, change the connectionstring and then pass it to the base class. That's it! Simply set the type attribute of your provider in the web.config to your inherited class (as in Listing 1), and use this same technique on all the sqlproviders you need to use. In this example, config.Item("connectionStringName") would return "DEV.DB" and GetYourRunTimeConnectionStringName() would be a function that retrieves the right connectionstringname by checking the machinename. For this example, if it is the name of a production server it would return "Live.DB" and if it were a development machine it would return "DEV.DB" and that is the name of the connection string that would be passed to the base provider classes, and which would be used at runtime.

Of course, this technique is only viable if you can store your connection strings in the web.config. If you have to get them at runtime using an API or a webservice, you would still be better off following the directions on the Microsoft Forum, which I will reproduce here simply because links have a way of breaking over time. Download the ProviderToolkitSamples and modify the SQLConnectionHelper Class. Specifically, the GetConnectionString function which looks something like Listing 3.

Listing 3

internal static string GetConnectionString(string specifiedConnectionString, bool lookupConnectionString, bool appLevel)


            if (specifiedConnectionString == null || specifiedConnectionString.Length < 1)

                return null;


            string connectionString = null;



            // Step 1: Check <connectionStrings> config section for this connection string

            if (lookupConnectionString)


                ConnectionStringSettings connObj = ConfigurationManager.ConnectionStrings[specifiedConnectionString];

                if (connObj != null)

                    connectionString = connObj.ConnectionString;


                if (connectionString == null)

                    return null;




                connectionString = specifiedConnectionString;



            return connectionString;



Obviously, you can change the content of this function to do whatever you need it to do in order to generate or retrieve your connection string. I tested this technique and it works beautifully with all the provided sample Providers, but you are on your own if you need a connection string from outside the web.config for your SqlPersonalizationProvider.

Posted by Williarob on Friday, May 02, 2008 4:48 PM
Permalink | Comments (1) | Post RSSRSS comment feed
blog comments powered by Disqus