0

I am trying to add session state to SQL Server and I am unable to get it to work.

I first tried adding following connection string into the config:

 sessionState mode="SQLServer"
 sqlConnectionString="Server=XXXXXX;Database=ASPState;Integrated Security=true"

And get the following error:

The sqlConnectionString attribute or the connection string it refers to cannot contain the connection options Database, Initial Catalog or AttachDbFileName. In order to allow this, allowCustomSqlDatabase attribute must be set to true and the application needs to be granted unrestricted SqlClientPermission. Please check with your administrator if the application does not have this permission.

I then tried adding allowCustomSqlDatabase="true" to the connection string and get the following error:

System.Data.SqlClient.SqlException: Invalid object name 'tempdb.dbo.ASPStateTempApplications'.

This looks like it is trying to connect to the tempdb and not ASPState database.

Any ideas how to get this to use the ASPState database?

Cheers

Andy

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrew Beal
  • 427
  • 8
  • 23
  • Use Aspnet_regsql.exe to create the database for stateserver. Check this [link](http://msdn.microsoft.com/en-us/library/ms229862(v=vs.140).aspx) – Nilesh Jan 12 '15 at 12:23
  • The database has been created. The secord error seems like it is trying to use the tempdb database and not the ASPState database. – Andrew Beal Jan 12 '15 at 12:35
  • Yes thats what it does, check this [KB](https://support.microsoft.com/kb/317604/EN-US) and this [KB](https://support.microsoft.com/kb/311209/EN-US) article – Nilesh Jan 12 '15 at 12:51
  • Does it help, using persistent state exe? – Nilesh Jan 12 '15 at 13:10
  • Yeah persistent state exe create the session state database but I am unable to set the session state in IIS to use this database. Which is the error I was getting before. I can only seem to get it to work using tempdb – Andrew Beal Jan 12 '15 at 13:29
  • @AndrewBeal I update my solution and explained how to add session tables to your database: https://stackoverflow.com/a/49218168/5675763 – Mahdi Ataollahi Mar 12 '18 at 07:38

1 Answers1

1

As it said here:

If you do not add session state to SQL Server, you receive the following error message that is generated by the System.Data.SqlClient.SqlException class when you configure a website to use SQL Server mode session state:

Invalid object name 'tempdb.dbo.ASPStateTempSessions'.

So you should follow the instruction of Step 1 in the above link and use aspnet_regsql.exe to generate required table and other things.

The tool's full path is:

<system drive>:\Windows\Microsoft.NET\Framework\<.NETFrameworkversion>\aspnet_regsql.exe

Take a look at this too: https://stackoverflow.com/a/3151315/5675763

Impotrant Update

from here:

If you use aspnet_regsql wizard, session tables are not added by default so you need to run the following command...

The command in the above link is this:

aspnet_regsql.exe -S <servername> -E -ssadd -sstype p

This command will add AspState Database and uses current windows logon.

If you want to add session state tables and stored procedures to your database you should use this command:

aspnet_regsql.exe -S <server_address> -U <sql_username> -P <sql_password> -d <database_name> -ssadd -sstype c

You can use this command reference to use more options.

Mahdi Ataollahi
  • 4,544
  • 4
  • 30
  • 38