Storing database connection strings for Azure cloud services (including encypting the connection strings)


As my Surface didn't arrive on launch day, I had nothing better to do on a Friday night so here’s a blog post on Azure configuration settings.
Prior to Azure, web.config was the place ASP.NET devs would store settings.  However, as you should consider web.config read-only in Azure, you should use the Azure .cscfg instead IF you’ll ever want to change the settings without a re-deployment.
For most Azure work I’ve done, I’ve used something like:
ASP.NET web.config
<connectionStrings>
  <add name="DBConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Persist Security Info=False;MultipleActiveResultSets=True" />
</connectionStrings>
Azure .cscfg
<ConfigurationSettings>
  <Setting name="DBConnection" value="Server=tcp:xxxxxxxx.database.windows.net,1433;Database=xxxxxxxx;User ID=xxxxxxxx@xxxxxxxx;Password=xxxxxxxx;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True" />
</ConfigurationSettings>
Code
// Default to reading from ASP.NET web.config.
string dbConnection = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
// If running in Azure.
if (RoleEnvironment.IsAvailable)
{
    // Read from Azure .cscfg instead.
    dbConnection = RoleEnvironment.GetConfigurationSettingValue("DBConnection");
}
 
However, due to a requirement to encrypt db connection strings on a recent project, I’ve done the following instead:
ASP.NET web.config
 
Store both connection strings in web.config.
<connectionStrings>
<add name="DBConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=tdirect3;Integrated Security=True;Persist Security Info=False;MultipleActiveResultSets=True" />
<add name="DBConnectionAzure" providerName="System.Data.SqlClient" connectionString="Server=tcp:xxxxxxxx.database.windows.net,1433;Database=xxxxxxxx;User ID=xxxxxxxx@xxxxxxxx;Password=xxxxxxxx;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True" />
</connectionStrings>
 
Azure .cscfg
Storing the “key” to the desired web.config setting in the Azure .cscfg.
 
<ConfigurationSettings>
<Setting name="DBConnection" value="DBConnectionAzure" />
</ConfigurationSettings>
 
Code
// Default to the “key” for the ASP.NET connection string setting I want when not running in Azure.
string dbConnectionRef = "DBConnection";
// If running in Azure.
if (RoleEnvironment.IsAvailable)
{
    // Use the key for the ASP.NET connection string setting I want when running in Azure.
    dbConnectionRef = RoleEnvironment.GetConfigurationSettingValue("DBConnection");
}
string dbConnection = ConfigurationManager.ConnectionStrings[dbConnectionRef].ConnectionString;
Azure .csdef
<WebRole>
  <Startup>
    <Task commandLine="Startup.cmd" executionContext="elevated" />
  </Startup>
</WebRole>
Azure Startup.cmd
REM *** Encrypt the web.config connectionStrings element.  More info: http://msdn.microsoft.com/en-us/library/dtkwfdky(v=vs.100).aspx
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis -pa "NetFrameworkConfigurationKey" "NT AUTHORITY\NETWORK SERVICE"
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis -pef "connectionStrings" "e:\approot"
The MSDN link above includes an example of what the ASP.NET web.config looks like when deployed to Azure with the encrypted connection strings.
Comments (2)

  1. Ritesh Malgaonkar says:

    Nice and concise article explaining exact steps. This feature should be used more instead of using web.config transformation.

Skip to main content