Thursday, May 10, 2012

Using SQL Azure for Session State Management in Windows Azure Applications

We all know that, Inproc session management will not work in WindowsAzure. I am going to explain you how SQL Azure can be used for maintaining session state in Windows Azure Web Role Applications.
To start with let’s create a simple cloud service project in VS2010 and add a web role in it. Then I opened master page and changed the heading to “Session Management using SQL Azure Demo”. Open default.aspx and add a button and textbox. Input text written by user in textbox will be saved in session on the button click and on another button click it will be populated in label. Code on both the buttons is as follows –
protected void btnAddSession_Click(object sender, EventArgs e)
            Session["Value"] = txtValue.Text;

        protected void btnGetSession_Click(object sender, EventArgs e)
            lblValue.Text = Convert.ToString(Session["Value"]);

So my final solution structure and default.aspx layout is as follows –

Now session management mode is always specified in Web.config file using the tag <sessionState>.
However, if I specifiy my SQL Azure Database connection string in web.config and in future if I need to change the connection string to different server of database, I will need to redeploy the solution again. The alternative to the problem is specifying the connection string in ServiceConfiguration.cscfg file. But ServiceConfiguration.cscfg does not support <sessionState> configuration tag. Therefore I will write the connection string ServiceConfiguration.cscfg file and update the web.config to have connection string tag written, before application starts.
So first open the web.config fileand write <sessionState> tag as follows –

<sessionState mode="Custom" customProvider="DefaultSessionProvider" timeout="5">
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />

So in above statement I am specifying the connection string key name is DefaultConnection and you can change the timeout period as per your need. However, I will add this connection string DefaultConnection at runtime. So in this case your web role web config file will show error line on above statement as “The Connection string attribute is not allowed”. Still your project will build successfully. Also most important, comment out existing <connectionStrings> element from your web config as we will be adding it at runtime.

Add the following string in web role’s both cloud and local ServiceConfiguration.cscfg files –
<Setting name="SQLConnectionString" value="Data Source=YourSQLAzureServerNAme;Initial Catalog=SocialGames[Replace with your DB name];User ID=YourUserName;Password=YourPwd;MultipleActiveResultSets=True;" />
Replace the values in the connection string above with your own values. Here my DB name is SocialGames. Please make note that, MultipleActiveResultSets=True; will be required in your connection string.

Now our next task is to write method which will add required connection string element in the web.config on application start at runtime. Therefore open global.asax.cs file and add following code on Application_Start event –

void Application_Start(object sender, EventArgs e)
            // Code that runs on application startup to add connection string element to web configuration file

private void SetupConnectionStrings()
            string connectionString = RoleEnvironment.GetConfigurationSettingValue("SQLConnectionString");
            // Obtain the RuntimeConfig type. and instance
            Type runtimeConfig = Type.GetType("System.Web.Configuration.RuntimeConfig, System.Web, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a");
            var runtimeConfigInstance = runtimeConfig.GetMethod("GetAppConfig", BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null);

            var connectionStringSection = runtimeConfig.GetProperty("ConnectionStrings", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(runtimeConfigInstance, null);
            var connectionStrings = connectionStringSection.GetType().GetProperty("ConnectionStrings", BindingFlags.Public | BindingFlags.Instance).GetValue(connectionStringSection, null);
            typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.NonPublic | BindingFlags.Instance).SetValue(connectionStrings, false);
            // Set the SqlConnectionString property.
            ((ConnectionStringsSection)connectionStringSection).ConnectionStrings.Add(new ConnectionStringSettings("DefaultConnection", connectionString));


Yellow marked area in above code is the key name we specified in <sessionState> above. So make sure that both are same.
Using this approach you can have connection string in web config & it can be changed from cloud service project’s Serviceconfiguration.cscfg without need of redeployment.
Now run the project and put some value in textbox and click “Add Value to Session” button displayed in above screenshot. And then click on button “Get Session Value”.

Now connect to your SQL Azure DB server from management studio & open your database. You will observe that, Sessions table is created automatically in the database of SQL Azure. If you query that, you can view the session row created in it. If we open another browser and use the same (local or production) URL of our web role application then another entry of session will be created in the table as shown –

Hope it helps!!

Happy Sessioning!!!


  1. Hi Sanganak,

    I tried the method you mentioned above, but getting an error while executing as below

    Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

    at the code line

    var runtimeConfigInstance = runtimeConfig.GetMethod("GetAppConfig", BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null);

    Please help me to solve this. Thanks in Advance.

  2. Hi bhanu,
    Could you please post your complete code modifying config file here.

    1. This comment has been removed by the author.

  3. Hi Bhanu,
    Just check the .net framework of your application. it should be 4.0.
    I feel your are getting an exceptio because it is not able to find configuration file with specified .net framework.
    Hope it helps.

  4. Hi Sanganak

    I tried out the example as you directed above.
    On compiling i get the following error. Please advise.

    The setting 'SQLConnectionString' for role WebRole1 is specified in the service configuration file, but it is not declared in the service definition file.

    1. Hi Marco,
      Thanks for writing in. The error is self explanatory. The key 'SQLConnectionString' you defined in serviceconfiguration.cscfg.local (or cloud) file must also be declared in ".csdef" file as well. Do that and you should be good to go ahead. Hope this helps.

    2. What's the point of having them in both?

  5. Thanks this worked great!