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
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
what is load balancer
ReplyDeleteUnable 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.
ReplyDeleteI am already register aspnet_regsql.exe
Solve this problem through the cmd command .
ReplyDeleteif i am using wizard then its not created table ASPStateTempApplications and ASPStateTempSessions .
Hi Venkat
ReplyDeleteI 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'."
Hi Venkat...
ReplyDeleteThis 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.
You may have to enable the IIS from
DeleteControl 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).
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
ReplyDeleteQuestion:
if i go for the windows authentication in which table session id will be stored and in which data base?
Mine run smooth following your instructions. I am using Windows10 and SQL Server 2017. I used SSPI.
ReplyDelete