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

  • Article

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: https://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.