4

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. :-(

  • Yeah, after your update it's clear you forgot to set Initial Catalog. That's what i guessed in my answer. – Dmytro Shevchenko Sep 27 '16 at 23:15
  • @DmytroShevchenko See latest connection string. The same error is being raised. – eat-sleep-code Sep 27 '16 at 23:23
  • OK, then this sounds like a defect in the SQL Session State Provider. Report it to Sitecore Support. – Dmytro Shevchenko Sep 28 '16 at 7:07
  • 1
    In regard to your edit: try granting the CRUD permissions to the appropriate user—not permissions on the TempDB, but directly on the synonym dbo.Application. – Dmytro Shevchenko Sep 28 '16 at 16:30
  • @DmytroShevchenko yeah, working with the DBA's to get that done now (they don't give us marketing folks permission to change those things) – eat-sleep-code Sep 28 '16 at 17:35
3

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

enter image description here

| improve this answer | |
  • So, I can just use the standard ASPState database setup? I thought Sitecore_Sessions was special which was the whole reason I was trying to leverage it. – eat-sleep-code Sep 27 '16 at 22:28
  • It is. But you're not making it that far. Your exeption comes from System.Web.SessionState.SessionStateModule.InitModuleFromConfig - so the basic SQL Session Management doesn't even finish firing up. – Mark Cassidy Sep 27 '16 at 22:29
  • 1
    I have a standard ASPState database the works fine. It is only when I try to switch to point to Sitecore_Sessions that it fails. – eat-sleep-code Sep 27 '16 at 22:31
  • 1
    @MarkCassidy Sitecore's SqlSessionStateProvider is being used, according to the stack trace. ASP.NET SQL session state provider is not used. – Dmytro Shevchenko Sep 27 '16 at 23:04
  • 1
    This answer along with @DmytroShevchenko feedback got me headed down the right path. The account needs access to select, insert, and delete data in the tempdb. Sitecore_sessions points to tables in the tempdb. – eat-sleep-code Oct 4 '16 at 15:37
1

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.

| improve this answer | |
  • Thanks, I only mentioned tempdb because the error mentioned it: The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. – eat-sleep-code Sep 27 '16 at 22:18
  • Here is my connection string with password and domain changed: <add name="sharedsession" connectionString="user id=Sitecore;password=mypassword;Data Source=db.mydomain.com;Database=Sitecore_Sessions"/> It looks right to me. I can connect to and browse through the database with no problem using the credentials in the connection string. – eat-sleep-code Sep 27 '16 at 22:21
  • And I just confirmed with the ODBC Control Panel on the server that the server can successfully connect to that database. – eat-sleep-code Sep 27 '16 at 22:23
1

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.