Configuring private sessionState database raises runtime error:

Question

I am trying to configure a mssql private sessionState database. I added the following to web.config's :

<sessionState mode="Custom" customProvider="mssql" cookieless="false" timeout="20">
    <providers>
        <add name="mssql" type="Sitecore.SessionProvider.Sql.SqlSessionStateProvider, Sitecore.SessionProvider.Sql" connectionStringName="session" pollingInterval="2" compression="true" sessionType="private"/>
    </providers>
</sessionState>

My connection string (with password and domain altered for security):

<add name="session"
connectionString="user id=Sitecore;password=mypassword;Data Source=db.mydomain.com;Database=Sitecore_Sessions;Initial Catalog=Sitecore_Sessions"/>

However, this throws an error at runtime rendering the site useless:

Event code: 3008 Event message: A configuration error has occurred. Event time: 9/27/2016 1:27:20 PM Event time (UTC): 9/27/2016 8:27:20 PM Event ID: 9b328571e1994ddf8f99ae0ad4d56473 Event sequence: 8 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1/ROOT-1-131194816187533180 Trust level: Full Application Virtual Path: / Application Path: E:\wwwroot\Sitecore\Website\ Process information: Process ID: 9180 Process name: w3wp.exe Account name: IIS APPPOOL\SitecoreAppPool Exception information: Exception type: ConfigurationErrorsException Exception message: The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. The INSERT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. (E:\wwwroot\Sitecore\Website\web.config line 371) at System.Web.Configuration.ProvidersHelper.InstantiateProvider(ProviderSettings providerSettings, Type providerType) at System.Web.SessionState.SessionStateModule.SecureInstantiateProvider(ProviderSettings settings) at System.Web.SessionState.SessionStateModule.InitModuleFromConfig(HttpApplication app, SessionStateSection config) at System.Web.SessionState.SessionStateModule.Init(HttpApplication app)
at System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) at System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) at System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) at System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext)

The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. The INSERT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Sitecore.SessionProvider.Sql.SqlSessionStateStore.GetApplicationIdentifier(String name) at Sitecore.SessionProvider.Sql.SqlSessionStateProvider.Initialize(String name, NameValueCollection config) at System.Web.Configuration.ProvidersHelper.InstantiateProvider(ProviderSettings providerSettings, Type providerType)

Request information: 
Request URL: http://localhost/ 
Request path: / 
User host address: 127.0.0.1 
User:  
Is authenticated: False 
Authentication Type:  
Thread account name: IIS APPPOOL\SitecoreAppPool    Thread information: 
Thread ID: 9 
Thread account name: IIS APPPOOL\SitecoreAppPool 
Is impersonating: False 
Stack trace:    at System.Web.Configuration.ProvidersHelper.InstantiateProvider(ProviderSettings

providerSettings, Type providerType) at System.Web.SessionState.SessionStateModule.SecureInstantiateProvider(ProviderSettings settings) at System.Web.SessionState.SessionStateModule.InitModuleFromConfig(HttpApplication app, SessionStateSection config) at System.Web.SessionState.SessionStateModule.Init(HttpApplication app)
at System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) at System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) at System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) at System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext)

It appears that this could be is a permissions issue, but wouldn't permissions get reset on the tempdb every time the SQL server is rebooted?

I am running Sitecore 8.1

---

UPDATE: I can duplicate this behavior in SQL Management studio.

USE [Sitecore_sessions]
SELECT * FROM [dbo].[Applications]

Returns the following error:

Msg 229, Level 14, State 5, Line 2 The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'.

That is because dbo.Applications is a synonym that points to tempdb. :-(

Answer 1

It is a permissions issue. You've not shared your actual connection string sharedsession but the end result will be the same, the account you're using to connect to SQL does not have sufficient permissions. My first course of action would be to attempt a connection string based on the sa account and see if the problem goes away.

Default SQL session state is based on tempdb so you're right; setting permissions directly on that will be lost after restart. You can however opt to install a permanent version.

When you use the default InstallSqlState.sql and UninstallSqlState.sql script files to configure ASP.NET SQL Server mode session state management, note that these files add the ASPStateTempSessions and the ASPStateTempApplications tables to the tempdb database in SQL Server by default. Furthermore, if you restart SQL Server, you lose the session state data that was stored in the ASPStateTempSessions and the ASPStateTempApplications tables.

For setting up a permanent version; refer to HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management.

If you want to stick with the default setup, use an Integrated connection string, then grant your IIS AppPool user (or whatever user you use to connect) the following permissions on the ASPState database:

• datareader
• datawriter

Answer 2

You don't provide the ConnectionString for sharedsession which is probably wise. I am also making the assumption based on this that you are using SQL Server for session state, not MongoDb.

So, I will paste the one from the config and confirm a few things. The error that you are seeing definitely seems to indicate that Sitecore can't talk to the SQL Server Database.

1.) Check to make sure your connection string is correct. It should be in the following format

<add name="sharedsession" connectionString="user id=_sql_server_user_;password=_user_password_;Data Source=_sqlserver_;Database = _sharedSession_database_name_"/>

Where:

• _sql_server_user_ = the user name you use for sitecore databases. Also ensure that the user has dbo access to the session database.
• _user_password_ is set to the appropriate password.
• _sharedSession_database_name_ = the name of your session database. The default is Sitecore.Session for SQL Server.

2.) Next, make sure that the Sitecore.Session database is accessible with that username and password combo. I generally use SQL Server Management Studio for this.

3.) Go to the Walkthrough for Setting up SQL Server Shared Session State and make scroll down to Configure Sitecore. Ensure that your Sitecore configs are patched appropriately.

The issue you're seeing is definitely a configuration, connection string, or permission issue. You mention tempdb making me think that your connection string may be set wrong.

Answer 3

Your connection string is going to provide info on what user it is using to connect to SQL Server. Seems like you are using Integrated windows authentication and site running under IIS APPPOOL\SitecoreAppPool. Couple of things: 1- Make sure you restore/attach the Sessions database provided by Sitecore and that is the database being pointed to in the corresponding connectionstring 2 - Make sure this app pool user can access the session's database

See this below thread which has some suggested solutions: https://stackoverflow.com/questions/1933134/add-iis-7-apppool-identities-as-sql-server-logons

Another small thing to note, the nomenclature is to use connectionstring="session" when sessionType="private" and sharedsession when sessiontype="shared" . Please check this page for detailed walkthrough and see what else is to be done.

https://doc.sitecore.net/sitecore_experience_platform/81/setting_up__maintaining/xdb/session_state/walkthrough_configuring_a_private_session_state_database_using_the_sql_server_provider