Retrieve and use Windows Azure's connection strings?

Question

I've configured connection strings in Azure management portal Configure->Connection Strings (linked resources):

What are these connection strings useful for?

I tried deleting the conn. strings from web.config file, so it should read from here, but it doesn't.

Is there any other way?

Basically I want these connection strings to override the connection strings in web.config to be used in production environment.

I've added the following to the Application_Start method:

var sb = new StringBuilder();
var appConfig = ConfigurationManager.OpenMachineConfiguration();  
foreach (ConnectionStringSettings conStr in appConfig.ConnectionStrings.ConnectionStrings)
  sb.AppendFormat("Name: {0}, ConnectionString: {1}\n", conStr.Name, conStr.ConnectionString);
throw new Exception(sb.ToString());

Here's the result:

Name: LocalSqlServer, ConnectionString: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

I tried the above with ConfigurationManager.ConnectionStrings as well and the server (Azure) connection strings were not there.

Answer 1

The connection strings in the Portal allow you to override the connection strings defined in the web.config.

When you're developing locally, you probably use a database located in localhost\SQLExpress or something similar. If you deploy without having set up web.config transformation it would mean that your Web Site running in Windows Azure would still point to localhost\SQLExpress, which isn't something you would want.

The connection strings in the Portal allow you to override existing connection strings which are already defined in the web.config. If your web.config does not contain a connection string with the same name as the one configured in the portal, it will not be added and be accessible at runtime. This might be the issue you're experiencing.

To fix this, simply add a connection string to your web.config file with the same name as the one you have already added to the portal.

Update: Like I already explained in a comment, Windows Azure Web Sites does not physically modify the web.config (source), it does this at runtime. So in order to check which AppSettings and ConnectionStrings are actually available at runtime, try this:

Controller:

public ActionResult Index()
{
  ViewBag.ConnectionStrings =
    ConfigurationManager.ConnectionStrings.Cast<ConnectionStringSettings>();
  ViewBag.AppSettings = ConfigurationManager.AppSettings;
  return View();
}

View:

<h3>ConnectionStrings:</h3>
<ul>
  @foreach (var setting in ViewBag.ConnectionStrings)
  {
    <li>@setting.Name: @setting.ConnectionString</li>
  }
</ul>
<h3>AppSettings:</h3>
<ul>
  @foreach (var s in ViewBag.AppSettings)
  {
    <li>@setting: @System.Configuration.ConfigurationManager.AppSettings[s]</li>
  }
</ul>

Answer 2

After two days, I have finally managed to get it to work. I'm adding my solution here hoping that it might help someone in future.

Environment

• Azure API APP (but in theory, it should work for other types of projects too)
• SQL DB (hosted on Azure). It can be obviously any db
• EF 6.0 - Database First Approach
• Connection String is stored on Azure under Application Settings ->Connection Strings section. Image shown below

What I wanted to do I wanted to put my connection string on Azure and like @Sandrino Di Mattia mentioned, dynamically retrieve it from there.

Steps which worked for me

1. Create a connectionStrings in the web.config

`

<connectionStrings>
    <add name="dbConnectionStringForAzure" connectionString="Local Connection String" providerName="System.Data.EntityClient"/>
  </connectionStrings>

`

Note that the providerName is System.Data.EntityClient and not System.Data.SqlClient.

Extra bit: Also, once you have published the project, you can verify that the connection string in the web.config. Navigate to projectorapiurl.scm.azurewebsites.net.

Go to Menu -> Debug Console -> PowerShell -> Edit Web.config file. (There are other ways to get the web.config files too. Use the one you prefer.)

Note: Here you might find another auto generated connection string by Azure. It's safe to remove that as we aren't using that.

2. Go to Azure -> your project and Application Settings. Add the details like shown below:

   Name = dbConnectionStringForAzure

   Value = Provider=System.Data.SqlClient; metadata=res://*/csdlModel.csdl|res://*/ssdlModel.ssdl|res://*/mslModel.msl; Provider Connection String ='Data Source=server.database.windows.net,1433;initial catalog=database;User ID=username;Password=password;MultipleActiveResultSets=True;App=EntityFramework;'

3. Front the third dropdown, select CUSTOM. It's important else Azure will add System.Data.SqlClient (or any other provider depending upon what is selected) in the provider name of our connection string, which we don't want.

4. Save

At this stage, Azure should use this connection string on runtime. Want to verify!? do similar to what is suggested above by @Sandrino Di Mattia or in this SO post by @Shaun Luttin Retrieve the connection string of an SQL Azure database that is linked to a Windows Azure Web Site with C#.NET.

Alternatively, put the below code in any razor template:

    <p>Value of dbConnectionStringForAzure  :
    @System.Configuration.ConfigurationManager.ConnectionStrings["dbConnectionStringForAzure"].ConnectionString    
    </p>

On the other hand, I have set the connection string name in my DbContext constructor.

        public MyEntities() : base("name=dbConnectionStringForAzure")
        {

        }

Now, when I made a call to my API it dynamically used the connection stored in Azure.

Thanks to the dozens of posts and extra shots of coffee!