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

7
  • Yeah, after your update it's clear you forgot to set Initial Catalog. That's what i guessed in my answer. Sep 27, 2016 at 23:15
  • @DmytroShevchenko See latest connection string. The same error is being raised. Sep 27, 2016 at 23:23
  • OK, then this sounds like a defect in the SQL Session State Provider. Report it to Sitecore Support. Sep 28, 2016 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. Sep 28, 2016 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) Sep 28, 2016 at 17:35

3 Answers 3

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

6
  • 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. Sep 27, 2016 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, 2016 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. Sep 27, 2016 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. Sep 27, 2016 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. Oct 4, 2016 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.

3
  • Thanks, I only mentioned tempdb because the error mentioned it: The SELECT permission was denied on the object 'Application', database 'tempdb', schema 'dbo'. Sep 27, 2016 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. Sep 27, 2016 at 22:21
  • And I just confirmed with the ODBC Control Panel on the server that the server can successfully connect to that database. Sep 27, 2016 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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