Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

SQLServer asp.net session state mode management - Part 66

Suggested Videos
Part 63 - Cookie less sessions in asp.net
Part 64 - Inporc asp.net session state mode management
Part 65 - StateServer asp.net session state mode management

In this video, we will discuss about the asp.net session state mode - SQLServer. Asp.net session state mode can have any of the following 4 values. Asp.net session state mode is set in web.config file.
1. Off - Disables session state for the entire application.
2. InProc - Discussed in Part 64
3. StateServer - Discussed in Part 65
4. SQLServer - Will be discussed in this session.
5. Custom - Enables you to build your own Session State provider. For example, Oracle.



When the session state mode is set to SQLServer, the session state variables are stored in a SQLServer database.

Steps to follow, to configure asp.net web application to use SQLServer:
1. Create the ASPState database using aspnet_regsql.exe tool. There are several versions of this tool. I am running .NET version 4.0, on a 64 bit operating system. So I will use the version that is present in C:\Windows\Microsoft.NET\Framework64\v4.0.30319.
   a) click Start > Type Run > Press Enter
   b) Type cmd > Press Enter
   c) In the command prompt type - cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319
   d) Press Enter
   e) Type - aspnet_regsql.exe -S SQLServerName -E -ssadd -sstype p
   f) Press Enter. At this point you should have ASPState Database added.
   g) For help running this tool, please refer to the following MSDN article 
        http://msdn.microsoft.com/en-us/library/ms229862(v=vs.100).aspx

2. Set the Session state mode=SQLServer and sqlConnectionString
    If you want to use windows authentication
      <sessionState mode="SQLServer" 
      sqlConnectionString="data source=SQLServerName; integrated security=SSPI"
      timeout="20"></sessionState>
   
    If you want to use sql serevr authentication
     <sessionState mode="SQLServer" 
     sqlConnectionString="data source=SQLServerName; user id=sa; password=pass"
     timeout="20"></sessionState>



Note: If you use integrated security(windows authentication), you might get an error stating "Failed to login to session state SQL server for user 'IIS APPPOOL\ASP.NET v4.0'.". To resolve this error
a) click Start > Type Run > Press Enter
b) Type inetmgr > Press Enter
c) Expand IIIS and Click on Application Pools.
d) Right click on ASP.NET v4.0 and select Advanced settings
e) Change Process Model > Identity to LocalSystem and Click OK

Advantages of using SQLServer session state mode:
1. SQLServer is the most reliable option. Survives worker process recycling and SQL Server restarts.
2. Can be used with web farms and web gardens.
3. More scalable than State server and InProc session state modes.

Dis-advantages of using StateServer session state mode:
1. Slower than StateServer and InProc session state modes
2. Complex objects, need to be serialized and deserialized

Note: 
Web Garden - Web application deployed on a server with multiple processors
Web Farm - Web application deployed on multiple server

asp.net sql server session state mode

9 comments:

  1. Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.

    I am already register aspnet_regsql.exe

    ReplyDelete
  2. Solve this problem through the cmd command .
    if i am using wizard then its not created table ASPStateTempApplications and ASPStateTempSessions .

    ReplyDelete
  3. Hi Venkat

    I am unable to do with User credentials.
    I have tried by using this code

    and In command i have used -E sa -P sql@123 instead of -E and it was excuted succesfull.
    But i am getting error while cliking the button in the webform1 and the error is "Failed to login to session state SQL server for user 'TENXLABS\satishkumar.g'."

    ReplyDelete
  4. Hi Venkat...
    This is awesome article about SQL server state. I am find error to unable search by run prompt by typing inetmgr.
    How cal I resolve it.

    ReplyDelete
    Replies
    1. You may have to enable the IIS from
      Control Panel\All Control Panel Items\Programs and Features -> Turn Windows Features ON or OFF (from Left panel), and then check the Internet Information Services (IIS).

      Delete
  5. Great.....Its working 100% accurate.Thanks for vedio as well as article. You are great.Keep it on.It can save many people life.

    ReplyDelete


  6. Question:
    if i go for the windows authentication in which table session id will be stored and in which data base?

    ReplyDelete
  7. Mine run smooth following your instructions. I am using Windows10 and SQL Server 2017. I used SSPI.

    ReplyDelete

It would be great if you can help share these free resources