Moving database connection string to azure service configuration (cscfg)

While working with ASP.NET web sites/projects we normally keep our database connection string in Web.config. However, while working in Azure, it's a good idea to keep this configuration in the service configuration itself as it will be easier to change the connection string once you have deployed your azure service and this will prevent the need of redeployment.

Also, while working with Azure services it would be easier to keep database configuration in the service configuration file for the simple fact that you will not need to keep on changing the database server name or credentials whiles working locally and while deploying to azure.

Because, we can have different service configuration files for different environments such as local, cloud or even for test or staging, we can simply add a key in the service definition file (csdef) and have values for each environment in each of the service configuration file (cscfg).

This is much more helpful in cases where you are using membership provider for forms authentication, authorization and/or session management, such as using ASP.NET Universal Membership provider for SQL Azure as it requires you to have the connection string in the web.config file, by default it is named DefaultConnection.

To achieve this you can remove the section from web.config and add key for your database connection in your csdef file and values in cscfg. Then it is simply to add the section at run time to your web.config in the Application_Start event, whilst reading the values from the cscfg file.

So, first add a key in your csdef file and its setting in the cscfg file as below, for example:

<Setting name="MyApplicationDB" value="Data Source=.\sqlexpress;Initial Catalog=Customers;Integrated Security=False;User ID=App.Web;Password=%#$##@;MultipleActiveResultSets=True;">

Then in your Global.asax.cs file's Application_Start event include the following code to add the required section in web.config

string connectionString = RoleEnvironment.GetConfigurationSettingValue(“MyApplicationDB”);

            // Obtain the RuntimeConfig type. and instance

            Type runtimeConfig = Type.GetType("System.Web.Configuration.RuntimeConfig, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a");

            var runtimeConfigInstance = runtimeConfig.GetMethod("GetAppConfig", BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null);

            var connectionStringSection = runtimeConfig.GetProperty("ConnectionStrings", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(runtimeConfigInstance, null);

            var connectionStrings = connectionStringSection.GetType().GetProperty("ConnectionStrings", BindingFlags.Public | BindingFlags.Instance).GetValue(connectionStringSection, null);

            typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.NonPublic | BindingFlags.Instance).SetValue(connectionStrings, false);

            // Set the SqlConnectionString property.

            ((ConnectionStringsSection)connectionStringSection).ConnectionStrings.Add(new ConnectionStringSettings("DefaultConnection", connectionString));