Retrieve the connection string of an SQL Azure database that is linked to a Windows Azure Web Site with C#.NET

Question

The configuration page of a Windows Azure Web Site has a "connection strings" section. The section lists connection strings for linked resources. How do we programmatically retrieve the connection string for a linked SQL Azure Database?

Answer 1

Solution

Programmatically retrieve the connection string as follows:

connString = 
    Environment.GetEnvironmentVariable("PREFIX_myConnStringName");

Explaination

The Azure connection strings become environmental variables. Documentation explains that Azure creates the variables with the prefixes as follows:

SQL Server: SQLCONNSTR_myConnStringName

MySQL: MYSQLCONNSTR_myConnStringName

SQL Database: SQLAZURECONNSTR_myConnStringName

Custom: CUSTOMCONNSTR_myConnStringName

SQL Azure: SQLAZURECONNSTR_myConnStringName

Knowing that, we can retrieve the desired connection string with the following code:

connString = 
    Environment.GetEnvironmentVariable("SQLAZURECONNSTR_myConnStringName");

Other Option

As another option, this related post about how to access the connection string through web.config as follows:

<add name="myConnStringName" 
    connectionString="you can leave this blank"
    providerName="System.Data.SqlClient" />  

Note: we might not have to include the providerName attribute.

Further Research

We can view all the available environmental variables and connection strings by putting this code into a Razor view. Warning: this will reveal your password!

<ul>
    @foreach (System.Collections.DictionaryEntry ev in Environment.GetEnvironmentVariables())
    {
        if (ev.Value.ToString().ToLower().Contains("data source"))
        {
            <li><strong>@ev.Key.ToString()</strong> @ev.Value.ToString()</li>
        }
    }
</ul>

<ul>
    @foreach (System.Configuration.ConnectionStringSettings cs in System.Configuration.ConfigurationManager.ConnectionStrings)
    {
        <li><strong>@cs.Name</strong> @cs.ConnectionString</li>
    }
</ul>

That's all for now.