SQL Server Session Management

SQL Server Session Management
Configure SQL Server for ASP.NET SQL Server Session State in Custom Database.
To install the session state database on SQL Server, run Aspnet_regsql.exe tool supply the following information with the command:

aspnet_regsql -ssadd -sstype c -d < Database> -S <Server> -U <Username> -P <Password>

·         The name of the Database, using –d option.
·         The name of the SQL Server instance, using the -S option.
·         The logon credentials for an account that has permission to create a database on a computer running SQL Server. Use the -E option to use the currently logged-on user, or use the -U option to specify a user ID along with the -P option to specify a password.
·         The -ssadd command-line option to add the session state database.
·         The -sstype command-line option to specify the type of session state to use. c for Custom i.e. both session state data and stored procedures are stored in a custom database. The other two types are t- Temporary (i.e. session data is stored in the SQL Server’s tempdb database and stored procedures are installed in the SQL server ASPState database) and p- for Persisted (i.e. both session state data and SPs are stored in the SQL Server ASPState database.)
To run the wizard, run Aspnet_regsql.exe without any command-line arguments, as shown below
aspnet_regsql

Modify the Web.config File of Your Application
To implement ASP.NET SQL Server mode session state management, you must modify the <sessionState> element of your application's Web.config file as follows:
1.       Set the mode attribute of the <sessionState> element to SQLServer to indicate that session state is stored in SQL Server.
2.       Set the sqlConnectionString attribute to specify the connection string for SQL Server. For example:
3.  sqlConnectionString="data source=<Server>; Initial Catalog=<Database>; user id=<Username>; password=<Password>"

The modified <sessionState> element should appear as follows:

<system.web>
   <sessionState     
                    cookieName="s_id"
                    mode="SQLServer"
                    sqlConnectionString="data source=<Server>;Initial Catalog=<Database>;user id=<Username>;password=<Password>"
                    cookieless="false"
                    timeout="20"
    />
<system.web>

Note:
·         “timeout” specifies the number of minutes a session can be idle before it is abandoned. 
·         The user, <Username>, in the ConnectionString, must have permissions to perform this operation on the database.

Important notes:
Some findings related to SQL Server Session Management are as follows:

  •  Make sure SQL Server Agent is started and DeleteExpiredSessions Job exists and is properly scheduled.
  • SessionId is autogenerated (and its value is like “wbdmjp2p0ojzoynnfs40lm4s465b250a”) and its max length is 80 characters.
  • The session id has 24 characters using [a-z] chars and [0-5] digits (total of 32 possible chars which is 2^5) which gives a total of 2^(5*24) = 2^120 possible values. And 8 characters of AppId is also concatenate with 24 characters session id to make a 32 characters application specific sessionid.
  • However, sessionid could be customized by overriding CreateSessionID and Validate methods of SessionIDManager class.
  • If user accesses Expired session, then ASP.Net session management creates new session with the same session id in cookie-ful environment i.e. when cookieless="false".
  • To overcome the above issue, we should use the following code on user logout event
Session.Abandon();
Response.Cookies.Add(new HttpCookie(((System.Web.Configuration.SessionStateSection)ConfigurationManager.GetSection("system.web/sessionState")).CookieName, ""));
·          
Useful Scripts:
--- ASPStateTempApplications table contains Application ID and other info
Select * from ASPStateTempApplications

--- ASPStateTempSessions table contains Session ID and other info
Select * from  ASPStateTempSessions

 --- This select show us the size of each session
SELECT
    a.SessionId,
    SUBSTRING(a.SessionId, 25, 8) AS AppIDHex,
    b.AppId AS AppIDDec,
    b.AppName,
    DATALENGTH(a.SessionItemLong) AS SessionSize_In_Bytes,
    DATALENGTH(a.SessionItemLong)/1000.0 AS SessionSize_In_KBs,
      DATALENGTH(a.SessionItemLong)/1000000.0 AS SessionSize_In_MBs
FROM
    dbo.ASPStateTempSessions AS a
    LEFT OUTER JOIN
    dbo.ASPStateTempApplications AS b
    ON
    SUBSTRING(a.SessionId, 25, 8) =
    SUBSTRING(sys.fn_varbintohexstr(CONVERT(VarBinary,b.AppId)), 3, 8)
WHERE
    --(DATALENGTH(a.SessionItemLong) > 0) And
      b.AppName like '%MyWebApp%'
ORDER BY SessionSize_In_Bytes DESC

 --- This select show us the Total and Avg size of overall MyWebApp sessions
SELECT
  a.AppName,
  SessionCount = COUNT(s.SessionId),
  TotalSessionSize = SUM(DATALENGTH(s.SessionItemLong)),
  AvgSessionSize = AVG(DATALENGTH(s.SessionItemLong))
FROM
  dbo.ASPStateTempSessions AS s
LEFT OUTER JOIN
  dbo.ASPStateTempApplications AS a
  ON SUBSTRING(s.SessionId, 25, 8) = SUBSTRING(sys.fn_varbintohexstr(CONVERT(VARBINARY(8), a.AppId)), 3, 8)
WHERE
      a.AppName like '%MyWebApp%'
GROUP BY a.AppName
ORDER BY TotalSessionSize DESC;

Comments

Post a Comment

Popular posts from this blog

Data Bound Controls in ASP.Net - Part 4 (FormView and DetailsView controls)

FormView The FormView control is used to display a single record from a data source. It is similar to the DetailsView control, except it displays user-defined templates instead of row fields. Creating your own templates gives you greater flexibility in controlling how the data is displayed. The FormView control supports the following features: ·          Binding to data source controls, such as SqlDataSource and ObjectDataSource. ·          Built-in inserting, updating and deleting capabilities. ·          Built-in paging capabilities. ·          Programmatic access to the FormView object model to dynamically set properties, handle events, and so on. ·          Customizable appearance through user-defined templates, themes, and styles. Templates For the FormView control to display content, you need to create templates for the different parts of the control. Most templates are optional; however, you must create a template for the mode in which the control is configur
Read more

ASP.net: HttpHandlers

Image
HTTPHandlers: ASP.NET handles all the HTTP requests coming from the user and generates the appropriate response for it. ASP.NET framework knows how to process different kind of requests based on extension. For example, it can handle request for .aspx , .ascx and .txt files, etc. When it receives any request, it checks the extension to see if it can handle that request and performs some predefined steps to serve that request. ASP.NET provides a low-level request/response API that enables developers to use .NET Framework classes to service incoming HTTP requests.So as a developer, One might want to handle some new kind of requests such as requests for .jpg or .gif. ASP.NET provides HTTPHandler to handle requests for .jpg or .gif. An ASP.NET HTTP handler is the process or endpoint that runs in response to a request made to an ASP.NET Web application. The most common handler is an ASP.NET page handler that processes .aspx files. When users request an .aspx file, the requ
Read more

The Clickjacking attack and X-Frame-Options

Clickjacking  Clickjacking is a type of “web framing” or “UI redressing” attack. What that simply means in practice is that: 1. A user (victim) is shown an innocuous, but enticing web page (think watch online video) 2. Another web page (that generally does something important – think add friends on social network) is layered on top of the first page and set to be transparent 3. When the user thinks they are clicking on the web page they see (video), they are actually clicking on the higher layered (framed) page that is transparent There are two main ways to prevent clickjacking: 1.     Sending the proper X-Frame-Options HTTP response headers that instruct the browser to not allow framing from other domains 2.     Employing defensive code in the UI to ensure that the current frame is the most top level window Using X-Frame-Options X-Frame-Options  originally invented by Microsoft for IE8, but supported by a number of browsers, this idea might have more
Read more