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


I run across this error today while setting up a repro for a problem I’m working on; the first time I through I did something wrong between the mess of things which case across my hands today (maybe I’ve used the wrong .sql script? smile_thinking), but after removing the database from Sql Server and re-running correct InstallSqlState.sql, I got the same message once again smile_sarcastic.


Ok then, time for a (hopefully) quick research on this error and finally I found out a few things which might cause it.



Running  both ASP.NET and classic ASP in the same application pool?


If the application pool is also running classic ASP pages, and those classic ASP pages use .NET 2.0 components, and those classic ASP pages which use .NET components are called before any ASP.NET 1.1 page, then we’ll load CLR 2.0 (first come, first serve smile_regular) and of course it will look for his specific ASPState version.


Ok, there are a lot of “if” in this case, but it’s still a possibility… not my scenario, through.


Where are your session tables?



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



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


Again, that was not my case.


Are you sure you can run it? 


Of course we still need permissions to access the database… so make sure the account used in your connection string can connect to the database has EXEC permission on the following stored procedures in ASPState database:



  • TempGetAppID

  • TempGetStateItem

  • TempGetStateItemExclusive

  • TempReleaseStateItemExclusive

  • TempInsertStateItemLong

  • TempInsertStateItemShort

  • TempUpdateStateItemLong

  • TempUpdateStateItemShort

  • TempUpdateStateItemShortNullLong

  • TempUpdateStateItemLongNullShort

  • TempRemoveStateItem

  • TempResetTimeout

Well… to make things easier in my sample, I just granted NETWORK SERVICE dbo permission on ASPState and I got my repro up an running.


Need self irony and a joke


What can I say? The error message is not very helpful in this situation (ok, it’s misleading smile_omg), and this reminded me a joke I heard a few years ago before joining Microsoft… it’s something like the jokes we have in Italy against our Carabinieri (one of Italian police forces); but I warn you, I’m not a good storyteller…


There is a group of friends in trouble on their helicopter, the radio is broken, there is a thick fog and they don’t know where they are. Suddenly they see the shape of a skyscraper and try to attract the attention of people inside their offices; someone on the helicopter writer down on a peace of paper: “Radio broken, need help, where are we?”. Immediately the people inside the office start to talk each other and finally someone writes the answer on a sheet of paper: “You’re in an helicopter at about 30 feet from the ground, and in this foggy day you’d better stay at home”. The pilot of the helicopter at first looks puzzled, then smiles, nods thankfully and flight back to the airport where they land safely. Other passengers were amazed and asked the pilot how he had been able to find their way home so quickly
“Easy”, he replied “we were at Microsoft’s building”
“How did you know?”
“We asked a simple and direct question and they gave a vague and completely useless answer!”


P.s.
Ok, I told you I’m not a good storyteller…smile_teeth


Carlo

Quote of the day:
I have a rock garden. Last week three of them died. – Richard Diran

Comments (10)

  1. TristanK says:

    Hey Carlo!

    As I heard it, I think the last line was:

    "They gave an answer that was technically correct, but absolutely useless." 🙂

  2. Raja says:

    Thanks Pal,

    Valuable suggestions, its worked for me!

  3. Scott Burkhalter says:

    Thanks for your post!!

    Worked out the grant statements to assign exec rights for the necessary stored procs, and since your post solved my problem (4 hours of wasted time due to that awesome "Session State not installed" message) I thought I’d give back 🙂

    The list of sp’s is a bit longer than the one you present above.

    replace [user] with a real [user] from your security hive, or [dbo] if you want:

    use APSState

    go

    grant EXECUTE on GetHashCode to [user]

    grant EXECUTE on GetMajorVersion to [user]

    grant EXECUTE on TempGetAppID to [user]

    grant EXECUTE on TempGetStateItem to [user]

    grant EXECUTE on TempGetStateItem2 to [user]

    grant EXECUTE on TempGetStateItem3 to [user]

    grant EXECUTE on TempGetStateItemExclusive to [user]

    grant EXECUTE on TempGetStateItemExclusive2 to [user]

    grant EXECUTE on TempGetStateItemExclusive3 to [user]

    grant EXECUTE on TempGetVersion to [user]

    grant EXECUTE on TempInsertStateItemLong to [user]

    grant EXECUTE on TempInsertStateItemShort to [user]

    grant EXECUTE on TempInsertUninitializedItem to [user]

    grant EXECUTE on TempReleaseStateItemExclusive to [user]

    grant EXECUTE on TempRemoveStateItem to [user]

    grant EXECUTE on TempResetTimeout to [user]

    grant EXECUTE on TempUpdateStateItemLong to [user]

    grant EXECUTE on TempUpdateStateItemLongNullShort to [user]

    grant EXECUTE on TempUpdateStateItemShort to [user]

    grant EXECUTE on TempUpdateStateItemShortNullLong to [user]

    go

    YMMV

  4. carloc says:

    Cool, thanks for sharing 🙂

  5. emilioMalaga says:

    Thanks a lot to both Carlo and Scott.

    Really helpful posts.

    Cheers 🙂

  6. Carlos Del Río says:

    Thanks, i can solve my problem.

    Isn’t obvious the conection between de problem and the solution….beside Microsoft’s designers

  7. Eduard says:

    Thank you. That message drove me crazzy until I found this blog. I was running from Visual Studio 2005, pointing to SQL Server 2005. Because when you run from Visual Studion it assumes your own identity and this one didn’t have enough permission to Session database it failed to run.

    My solution: made my windows account (under which I’m logged to my PC) the db_owner of ASPState database… and it started to work.

    Thanks…

  8. JattDev says:

    You can assign dbo role to the connection string user or set execute permissions for the user on all session objects [tables and store procedures].

  9. Nay says:

    Great post, this post saved alot of my time. Was having the same problem, now it’s solved by giving exec permission to store procedures.

  10. Greg says:

    In this case the answer from Microsoft is NOT technically correct, but it is useless.  Also, while you do get a more helpful error message for this part, but your user also needs read and write access to the tempdb.