How to store asp.net session state in SQL Server for Asp.Net MVC 4 application or WebRole application?

Question

How do I store session state in SQL Server for Asp.net mvc4 or Windows Azure Web Role application?

Edit

Why do I get this error?

HTTP Error 500.23 - Internal Server Error
An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

My web.config looks like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <sectionGroup name="system.web.webPages.razor" type="System.Web.WebPages.Razor.Configuration.RazorWebSectionGroup, System.Web.WebPages.Razor, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
      <section name="webAssets" type="Telerik.Web.Mvc.Configuration.WebAssetConfigurationSection, Telerik.Web.Mvc" requirePermission="false" />
      <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
      <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </sectionGroup>
  </configSections>
  <!--
    <connectionStrings>
        <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet2012620142020;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
  -->
  <connectionStrings>
    <!--<add name="HotDeployDbContext" connectionString="data source=JOE-WANG\WQ;initial catalog=HotDeploy;user id=sa;password=wq187731" providerName="System.Data.SqlClient"/>-->
    <!--<add name="HotDeployDbContext" connectionString="data source=tcp:z1782cwqws.database.windows.net,1433;initial catalog=HotDeployDB4CloudService;user id=hotdeployadmin@z1782cwqws;password=accela.123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient" />-->
    <add name="HotDeployDbContext" connectionString="data source=tcp:xxxxxxxx.database.windows.net,1433;initial catalog=xxxxx;user idxxxxx@xxxx;password=dddd.123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="2.0.0.0" />
    <add key="webpages:Enabled" value="true" />
    <add key="PreserveLoginUrl" value="true" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />

  </appSettings>
  <system.web>
    <customErrors mode="Off" />
    <httpRuntime executionTimeout="14400" maxRequestLength="716800" />
    <compilation targetFramework="4.0" />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" />
    </authentication>
    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
        <add namespace="Telerik.Web.Mvc.UI" />
      </namespaces>
    </pages>
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </profile>
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    <roleManager defaultProvider="DefaultRoleProvider">
      <providers>
        <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </roleManager>
    <!--
    <sessionState mode="InProc" timeout="30" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </sessionState>
    <httpHandlers>
      <add verb="GET,HEAD" path="asset.axd" validate="false" type="Telerik.Web.Mvc.WebAssetHttpHandler, Telerik.Web.Mvc" />
    </httpHandlers>-->
    <!--before I added sessionState element . everything is fine.-->
    <sessionState mode="SQLServer" cookieless="true" 
    sqlConnectionString="data source=10.50.70.81; userid=sa; password=wq187731"
    timeout="300" 
    sqlCommandTimeout="10" />
  </system.web>
</configuration>
  </system.web>
  <system.webServer>
    <security>
      <requestFiltering>
        <requestLimits maxAllowedContentLength="3000000000" />
      </requestFiltering>
    </security>
    <validation validateIntegratedModeConfiguration="false" />
    <modules runAllManagedModulesForAllRequests="true" />
    <directoryBrowse enabled="true" />
    <handlers>
      <remove name="asset" />
      <add name="asset" preCondition="integratedMode" verb="GET,HEAD" path="asset.axd" type="Telerik.Web.Mvc.WebAssetHttpHandler, Telerik.Web.Mvc" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.WindowsAzure.StorageClient" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.7.0.0" newVersion="1.7.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>

</configuration>

If I change the application pool to classical mode . I got IIS error page below ..

HTTP Error 403.14 - Forbidden
The Web server is configured to not list the contents of this directory.

...probably not the best idea to provide your database server's IP and sa password. :/ Yes, I realize that the IP is in a private address range, but it is still not a good idea. — J0e3gan, Feb 14 '14 at 02:35

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

The session state provider is configured in your web.config using the sessionState element. For SQL server you set the mode to SQLServer and configure a connection string. For Azure you could take a look at the following answer which covers different options.

edited May 23 '17 at 12:12

Community

1
1

answered Oct 19 '12 at 07:25

Darin Dimitrov

1,023,142
271
3,287
2,928

Very helpful Answer. please see my post . I edited again. thanks – Joe.wang Oct 19 '12 at 07:31
I see that you are using an `` section to register some custom `asset.axd` handler. But if you are running in Integrated Pipeline mode you should use the `system.webServer/handlers` section to register custom handlers, and not `system.web/httpHandlers`. – Darin Dimitrov Oct 19 '12 at 07:37
I had removed the `system.web/httpHandlers` element in web.config. but i got same error. help. – Joe.wang Oct 19 '12 at 07:42
Please show your full web.config as it is right now. Also aren't you getting some more details about the exception and which section cannot be used? Please post your full exception stack trace. Also look at the server EventLog. It might contain more information about the error. – Darin Dimitrov Oct 19 '12 at 07:42
Please review the full web.config. BTW, it is not exception it a IIS error page . you know ,It happened before web application run. thanks – Joe.wang Oct 19 '12 at 07:51
I am sure about i cleared the log before I found this error . I didn't see any new eventlog created . – Joe.wang Oct 19 '12 at 07:55
Does your application work if you comment the `sessionState` node? – Darin Dimitrov Oct 19 '12 at 07:57

How to store asp.net session state in SQL Server for Asp.Net MVC 4 application or WebRole application?

1 Answers1