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