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)

Cancel reply

  1. Mario Saccoia says:

    Good post!!!

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