Windows Azure SQL Database Marketplace
Editor's Note: This post comes from Stefan Schackow, Principal Program Manager Lead for the Windows Azure Web Sites Team.
Windows Azure Web Sites has a handy capability whereby developers can store key-value string pairs in Azure as part of the configuration information associated with a website. At runtime, Windows Azure Web Sites automatically retrieves these values for you and makes them available to code running in your website. Developers can store plain vanilla key-value pairs as well as key-value pairs that will be used as connection strings.
Since the key-value pairs are stored behind the scenes in the Windows Azure Web Sites configuration store, the key-value pairs don’t need to be stored in the file content of your web application. From a security perspective that is a nice side benefit since sensitive information such as Sql connection strings with passwords never show up as cleartext in a web.config or php.ini file.
You can enter key-value pairs from “Configure” tab for your website in the Azure portal. The screenshot below shows the two places on this tab where you can enter keys and associated values:
You can enter key-value pairs as either “app settings” or “connection strings”. The only difference is that a connection string includes a little extra metadata telling Windows Azure Web Sites that the string value is a database connection string. That can be useful for downstream code running in a website to special case some behavior for connection strings.
Once a developer has entered key-value pairs for their website, the data can be retrieved at runtime by code running inside of a website.
The most generic way that Windows Azure Web Sites provides these values to a running website is through environment variables. For example, using the data shown in the earlier screenshot, the following is a code snippet from ASP.NET that dumps out the data using environment variables:
Here is what the example page output looks from the previous code snippet:
[Note: The interesting parts of the Sql connection string are intentionally blanked out in this post with asterisks. However, at runtime rest assured you will retrieve the full connection string including server name, database name, user name, and password.]
Since the key-value pairs for both “app settings” and “connection strings” are stored in environment variables, developers can easily retrieve these values from any of the web application frameworks supported in Windows Azure Web Sites. For example, the following is a code snippet showing how to retrieve the same settings using php:
From the previous examples you will have noticed a naming pattern for referencing the individual keys. For “app settings” the name of the corresponding environment variable is prepended with “APPSETTING_”.
For “connection strings”, there is a naming convention used to prepend the environment variable depending on the type of database you selected in the databases dropdown. The sample code is using “SQLAZURECONNSTR_” since the connection string that was configured had “Sql Databases” selected in the dropdown.
The full list of database connection string types and the prepended string used for naming environment variables is shown below:
If you select “Sql Databases”, the prepended string is “SQLAZURECONNSTR_”
If you select “SQL Server” the prepended string is “SQLCONNSTR_”
If you select “MySQL” the prepended string is “MYSQLCONNSTR_”
If you select “Custom” the prepended string is “CUSTOMCONNSTR_”
So far we have shown how the key-value pairs entered in the portal flow through to a web application via environment variables. For ASP.NET web applications, there is some extra runtime magic that is available as well. If looking at the names of the different key-value types seems familiar to a .NET developer that is intentional. “App settings” neatly map to the .NET Framework’s appSettings configuration. Similarly “connection strings” correspond to the .NET Framework’s connectionStrings collection.
Here is another ASP.NET code snippet showing how an app setting can be referenced using System.Configuration types:
Notice how the value entered into the portal earlier just automatically appears as part of the AppSettings collection. If the application setting(s) happen to already exist in your web.config file, Windows Azure Web Sites will automatically override them at runtime using the values associated with your website.
Connection strings work in a similar fashion, with a small additional requirement. Remember from earlier that there is a connection string called “example-config_db” that has been associated with the website. If the website’s web.config file references the same connection string in the <connectionStrings /> configuration section, then Windows Azure Web Sites will automatically update the connection string at runtime using the value shown in the portal.
However, if Windows Azure Web Sites cannot find a connection string with a matching name from the web.config, then the connection string entered in the portal will only be available as an environment variable (as shown earlier).
As an example, assume a web.config entry like the following:
A website can reference this connection string in an environment-agnostic fashion with the following code snippet:
When this code runs on a developer’s local machine, the value returned will be the one from the web.config file. However when this code runs in Windows Azure Web Sites, the value returned will instead be overridden with the value entered in the portal:
This is a really useful feature since it neatly solves the age-old developer problem of ensuring the correct connection string information is used by an application regardless of where the application is deployed.
As an alternative to maintaining app settings and connection strings in the portal, developers can also use either the PowerShell cmdlets or the cross-platform command line tools to retrieve and modify both types of key-value pairs.
For example, the following PowerShell commands define a hashtable of multiple app settings, and then stores them in Azure using the Set-AzureWebsite cmdlet, associating them with a website called “example-config”.
Running the following code snippet in ASP.NET shows that the value for the original app setting (“some-key-here”) has been updated, and a second key-value pair (“some-other-key”) has also been added.
Sample HTML output showing the changes taking effect:
some-other-key <--> a-different-value some-key-here <--> changed-this-value
Updating connection strings works in a similar fashion, though the syntax is slightly different since internally the PowerShell cmdlets handle connection strings as a List<T> of ConnStringInfo objects (Microsoft.WindowsAzure.Management.Utilities.Websites.Services.WebEntities.ConnStringInfo to be precise).
The following PowerShell commands show how to define a new connection string, add it to the list of connection strings associated with the website, and then store all of the connection strings back in Azure.
Note that for the property $cs.Type, you can use any of the following strings to define the type: “Custom”, “SQLAzure”, “SQLServer”, and “MySql”.
Running the following code snippet in ASP.NET lists out all of the connection strings for the website.
Remember though that for Windows Azure Web Sites to override a connection string and materialize it in the .NET Framework’s connection string configuration collection, the connection string must already be defined in the web.config. For this example website, the web.config has been updated as shown below:
Now when the ASP.NET page is run, it shows that both connection string values have been over-ridden using the values stored via PowerShell (sensitive parts of the first connection string are intentionally blanked out with asterisks):
You have seen in this post how to easily associate simple key-value pairs of configuration data with your website, and retrieve them at runtime as environment variables. With this functionality web developers can safely and securely store configuration data without having this data show up as clear-text in a website configuration file. And if you happen to be using ASP.NET, there is a little extra configuration “magic” that also automatically materializes the values as values in the .NET AppSettings and ConnectionStrings configuration collections.