Setting Membership/Profile/Role provider's connection string at runtime...?

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

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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>

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

zhuhua1006 ,

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

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

did you ever find a solution to this?

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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

--B

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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>

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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

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.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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!

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

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#.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

Not a single issue, it just worked. One thing I did differently was I erased ", MyAssembly" in the "type" property as shown below, as I'm working on a regular website: <add name="MyMembershipProvider" type="MyNamespace.MyMembershipProvider" ... />

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

My application supports failover and we're using SQLEXPRESS so no DB capability exists for doing this.

I'm using a custom Profile Provider which works to initially set the connection string at runtime but I now need the capability to modify the connection string a second time to update the failover machine.

I've tried recalling the "Initialize" routine to get this to work and it runs and looks like the connection is changed but the provider's connection string is never changed.

Any help is greatly appreciated.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

Thanks, Gewgala. I know it's been a while since you responded, but I finally got back to this in my project. The C# code code works fine as you and others have stated. The code I converted to VB works for the Membership provider, but not the RoleProvider. I don't know why. Of course, it could be something with the translation. I'm not up on reflection yet.

Next, up is finding a way to integrate this with our shops standard data access layer. I found a good article on another site that may prove very useful.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

I have the same issue. Membership, Role, Profile custom providers work fine on my local IIS. I deploy this to production and no joy. using System.Web.Security;

using System.Reflection;

public class MyRoleProvider : 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.

//Using a class that manages my connection strings. It will pass the correct one based on the server name the application is running on.

ServerConnect sc = new ServerConnect(); string connectionString = sc.GetConnectionString();

// Set private property of Role provider.

FieldInfo connectionStringField = typeof(SqlRoleProvider).GetField("_sqlConnectionString", BindingFlags.Instance | BindingFlags.NonPublic);

if (connectionStringField != null)

connectionStringField.SetValue(this, connectionString);

}

web.config

</providers>

</profile>

Everything works great on my box. I created roles, profile, members ...

On the remote I get an error like this:

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: System.Web.Security.SqlRoleProvider._sqlConnectionString

Source Error:

Line 54: 			<providers>

Line 55:  <clear/>
Line 56:  <add name="MyRoleProvider" connectionStringName="conn" applicationName="CodersExpo" type="MyRoleProvider"/>
Line 57:  </providers>
Line 58:  </roleManager>

Source File: d:\hosting\charcoaldev\web.config Line: 56

Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

I don't get it??? Please help. I know the remote connection string is correct because I can query the DB.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

Thanks. I got it to work. There must have been a problem in the C# to VB translation. Now, I'm trying to get Memebership access to work with a Linked Server. I haven't found a solution for this yet.

Re: Setting Membership/Profile/Role provider's connection string at runtime...?

Hi Charcoal,

Did you find a solution to your custom role provider / config error? I'm using the same technique and am getting the exact same error as you describe on deployment (works locally though).

Thanks,

Mark