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
<sessionState
cookieless="false"
regenerateExpiredSessionId="true"
mode="SQLServer"
timeout="30"
sqlConnectionString="Data Source=.\SqlExpress;Integrated Security=SSPI;"
stateNetworkTimeout="30">
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:
- Create a login for that Windows user
- Add the login as a user to your AspState database
- 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
and
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.
