Use SqlExpress or Sql Server for Asp.Net Session

Problems using Sql Server or Sql Express for Asp.Net Session State

So you've run aspnet_regsql.exe -S .\SqlExpress -E -ssadd -sstype p or something like that, after following instructions about Asp.Net Session State Modes (in particular noting the instructions about the Agent XP setting for SqlExpress if you're using SqlExpress) and you've set your web.config sessionState section; and you hoped that aspnet_regsql might do something useful like give permissions to your machine's AspNet account to use the session state and it would all Just Work. Aaah. You can hope.

Error messages when setting up Asp.Net to use SqlServer Session State

Cannot open database requested in login 'ASPState'. Login failed for user {MachineName}\ASPNET

Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above

Failed to login to session state SQL server for user 'AspNetSqlLogin'

Example Web.Config section

    sqlConnectionString="Data Source=.\SqlExpress;Integrated Security=SSPI;"

Solution: Give permissions on Sql Server to your AspNet account

Note the username from your error page telling you what account Asp.Net is using to login to your server. Then, in Sql Management Studio or somesuch:

  1. Create a login for that Windows user
  2. Add the login as a user to your AspState database
  3. Give permissions to that user in that database by making it a member of the owner role

I first tried making the AspNet account a member of the db_reader & db_writer roles, but that didn't work; making it a database owner did work.

But I put SessionState on a Sql Server instance on a different machine

Then you're hopefully running your servers within a domain and you can set Asp.Net to run under a domain account, and you can give permissions on Sql server to that domain account.
Or, you create a new Sql Server login. Similar to the above process, but instead of finding an existing Windows login in Sql Management Studio, you create a new Sql Server login). The you specify a connection string with a Sql Security instead of Integrated Security: sqlConnectionString="Data Source=.\SqlExpress;User Id=SqlLoginForAspNet;Password=XXXXXX;

But I get a

Failed to login to session state SQL server for user


Login failed for user 'AspNetSqlLogin'. The user is not associated with a trusted SQL Server connection

That's because your Sql Server has been configured for Windows Authentication Mode only. Configure it for both Windows and Sql Server Authentication Mode via the Database Properties - Security tab.
When the Gui tells you to restart Sql Server you can do so like this:
[dos]net stop "Sql Server (SQLExpress)"
net start "Sql Server (SQLExpress)"[/dos]
Or do it from Control Panel -- Services.

Use SqlExpress or Sql Server for Asp.Net…

by Chris F Carroll read it in 2 min