How can I set the connection string for the built-in membership, profile and role providers at runtime?

Since each site in our multi-site application uses a separate API to retrieve its specific connection string (via key, for security purposes), I cannot set the connection string to my providers in the web.config file. I need to be able to set the connection strings programmatically at runtime, but I cannot find how to implement this.

I thought many people must have run into this problem, but I can't seem to find anything regarding this.Can anyone here help me with this problem?

Sebastian

You'd better configure each web site as an application. After seoarating the application, you can set the connection string for each web site easily in web.config.

<connectionStrings>

        <remove name=”LocalSqlServer”/>

        <add name="LocalSqlServer" connectionString="Data Source=localhost;Initial Catalog=appservicesdb;Integrated Security=True" providerName="System.Data.SqlClient"/>

    </connectionStrings>

No, that's not an option for us. We HAVE to set the connection s tring programmatically, we have no choice. I will explain:

I work for an interactive/ marketing agency, and the project I am working on is a large site for a well-known multi-national company. This company has 200+ websites with a common user database and their own hosting. For their own security reasons, they do not give out database connection strings to be hard-coded. Instead, they developed an application (with an API given out to their interactive agencies) to which you pass a predetermined key as a parameter and get back the connection string. This is our only option.

So, based on this, you're telling me that there's no way in the world to set the connection string outside of the web.config file?!? That just can't be, there has to be a way.

If it helps, I am using a custom membership provider to handle interfacing with our client's common user database.

sebatwerk:

I work for an interactive/ marketing agency, and the project I am working on is a large site for a well-known multi-national company. This company has 200+ websites with a common user database and their own hosting. For their own security reasons, they do not give out database connection strings to be hard-coded. Instead, they developed an application (with an API given out to their interactive agencies) to which you pass a predetermined key as a parameter and get back the connection string. This is our only option.

Keep in mind that using this system of connection string management also helps when you use a multi-server development/staging/production process. We do not have to change the connection strings in our application because we are always passing the same key to the ConnectionStringManager API, but getting back different strings depending on which server the calling application is running. It's really very handy.

zhuhua1006 ,

So can you, or anyone else here, come up with a solution to help me out?

did you ever find a solution to this?

 Sounds like your agency is working for the same multi national company that  mine is. any one find anything on this??

--B

 OK figured it out...and my multi national company is now saved...ha ha

This is a one liner gang........

you will need to create your own providers however, We simply downloaded the SampleProviderToolkitSampleProviders from microsoft to lessen the blow.

http://download.microsoft.com/download/a/b/3/ab3c284b-dc9a-473d-b7e3-33bacfcc8e98/ProviderToolkitSamples.msi

Class to modify

SQLConnectionHelper.cs

Method to modify

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

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

//Your Conn String goes here!!

           return Factory.ConnectionString;
        }

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 
we used our own namespace to be used in the web.congif as well.

complile that bad boy and your golden..

have fun

--b
   
 

 A simpler, albeit somewhat eyebrow-raising solution is just modifying the connection string in the providers early enough in the request's lifecycle:

          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);
        }

 Calling this method from Global.asax.cs inside Application_PreRequestHandlerExecute does the job. Haven't tested it too much, but even if something doesn't work, it just means it needs to be done earlier. No guarantees this will work with future versions of the framework, although most likely it will.

Gabor

RGabo you're a star. Your post prompted me to create my own custom SqlMembershipProvider class to tidy this up.

namespace MyNamespace
{
	public class MyMembershipProvider : 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 = "my new connection string value that I get from a custom decryption class not shown here"

			// Set private property of Membership provider.
			FieldInfo connectionStringField = GetType().BaseType.GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);
			connectionStringField.SetValue(this, connectionString);
		}
	}
}

 Then in app/web.config I put this:

		<membership defaultProvider="MyMembershipProvider">
			<providers>
				<clear />
				<add name="MyMembershipProvider"
					  type="MyNamespace.MyMembershipProvider, MyAssembly"
					  connectionStringName="name of a real but dummy connection string from the connectionStrings section that will never actually get used. Its value could just be 'x'" 
					  applicationName="MyApp" 
					  enablePasswordRetrieval="false" 
					  enablePasswordReset="true" 
					  requiresQuestionAndAnswer="false" 
					  requiresUniqueEmail="false" 
					  minRequiredNonalphanumericCharacters="0" 
					  passwordFormat="Hashed" />
			</providers>
		</membership>

antmx, does this have to go into a separate project from the main one where it is used? I simply created a new class within my main project (LT) like so:

==== In Class COBWEBMembershipProvider.vb

Imports System.Reflection

Namespace COBWEB
    Public Class COBWEBMembershipProvider
        Inherits SqlMembershipProvider

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

            ' Update the private connection string field in the base class.
            Dim connString As String = "Data Source=Some Connection String Here"

            ' Set private membership provider property. Reflection is used to discover the attributes of a field and
            ' provide access to the field metadata.

            ' Note: Not sure about the parameter for GetType. C# example did not have it, but VB needs something.

            Dim connStringField As FieldInfo = GetType(FieldInfo).BaseType.GetField("_sqlConnectionString", BindingFlags.Instance Or _
                BindingFlags.NonPublic)

            connStringField.SetValue(Me, connString)
        End Sub

    End Class
End Namespace

Add added this to my web.config

===== In Web.Config

    <membership defaultProvider="COBWEBMembershipProvider">
      <providers>
        <clear/>
        <add name="COBWEBMembershipProvider"
             type="COBWEB.COBWEBMembershipProvider, LT"
             connectionStringName="LTDummy"
             applicationName="LT"
             enablePasswordRetrieval="false"
             enablePasswordReset="true"
             minRequiredPasswordLength="5"
             requiresQuestionAndAnswer="true"
             requiresUniqueEmail="false"
             passwordFormat="Hashed"
             maxInvalidPasswordAttempts="5"
             minRequiredNonalphanumericCharacters="0"
             passwordAttemptWindow="10"
             passwordStrengthRegularExpression=""/>
      </providers>
    </membership>

But i get this error:

System Message: Could not load type 'COBWEB.COBWEBMembershipProvider' from assembly 'LT'. (C:\Inetpub\wwwroot\LT\LT\LT\web.config line 33)

 I'm looling at the Toolkit Sample Providers now.

Since the compiler is telling me it can't find the type (in the *dll), i am going to try putting the code in a separate project and copying to the bin folder.

RGabo and Antmx - THANK YOU!  That's exactly what I needed.  Searched all over the internet and finally found this thread, I did exactly what you did Antmx and it works like a charm!

Gewgala, so you didn't have any issues? I still haven't had it work in the VB version I created so I think I'll try using the exact code in C#.