I want to use Session State to store values used in a WebControl on a SharePoint page. The SQL connection string in web.config is using the currently logged on user (Integrated Security=true), however the values aren't being persisted across page requests. This is a permissions issue, when logged into SharePoint as the system account it works fine. Surely you don't need to give all users access to the SessionState database?! Using elevated privileges won't work either as the call within that code block to HttpContext.Current.Session will pick up on the logged on user not the elevated account (app pool account). Am I missing something?
I dont know if this helps, but can you check to see if you have done this?
You just need two entries that you need to add in web.config file, when you use session state in SharePoint 2010 to store data for use in web parts, application page, etc. you may encounter an error if not properly configured and enabled the session for the web application.
To fix this error and enable session state for SharePoint there are two entries you need to add for your web application.
First task is to make sure the enable session state is set to True.
<pages enableSessionState=”true” enableViewState=”true” enableViewStateMac=”true” validateRequest=”false” pageParserFilterType=”Microsoft.SharePoint.ApplicationRuntime.SPPageParserFilter, Microsoft.SharePoint, Version=184.108.40.206, Culture=neutral, PublicKeyToken=71e9bce111e9429c” asyncTimeout=”7″>
Second part is you need to remove and add lines that exist in the modules section for Session
<modules runAllManagedModulesForAllRequests=”true”> … <remove name=”Session” /> <add name=”Session” type=”System.Web.SessionState.SessionStateModule” preCondition=”" /> … </modules>
After you have modified the web.config file with above two entries, you need to ensure that the Sate Service application is running. This can be checked through Central Administration.
Now session Sate is enabled for you web application.
I have looked over your question again! so you are storing over a DB the session state.. according to msdn you would need to do the following:
If you specify a trusted connection to your SQL Server in the configuration file using the sessionState element's sqlConnectionString attribute, the SessionStateModule will connect to SQL Server using SQL Server integrated security. The connection will be made using the ASP.NET process identity or the user credentials supplied for the identity configuration element, if they exist. You can specify that the IIS impersonated identity be used instead by specifying and setting the useHostingIdentity attribute of the sessionState configuration element to false. For more information on the ASP.NET process identity, see Configuring ASP.NET Process Identity and ASP.NET Impersonation.
It looks like it takes the currently logged in user credentials to login but as they said you need "IIS impersonated identity be used instead by specifying and setting the useHostingIdentity attribute of the sessionState configuration element to false" in the web.config.
you can find the full article at: under: SQL Server Mode
I hope Iv understood your issue this thime round ;)