Problems in configuring sql server for session mode sql server

Before going to details let me explain my experience on this. I was working on a project which pushed us to use bulky data in session. So we thought that it wouldn't be reliable to use the session in "InProc" mode as the asp.net worker process consumes lot of RAM and leads to crash the service. To avoid this we decided to use the session state in sql server mode. Telling the asp.net application to use sql server for session state is easy as shown below.


But to make the above to work, we need to setup the sql server which is mentioned in connection string above. To do this, I did the following steps mentioned in some article found in Google search.

1. Find the file 'aspnet_regsql.exe' in the location 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
2. Run the aspnet_regsql.exe.
3. Follow the wizard with all default values till the wizard finishes.
4. Verify that a new database is created in the sql server.

It all looked perfect for me and when I tested my application I got an error saying "Cannot open database requested in login 'ASPState'. The asp.net framework was look for the database 'ASPState' but when I looked at the sql server I have noticed that the setup has created 'aspnetdb' instead of 'ASPState'. Then I have changed the config to use the database 'aspnetdb' as show below.


This time I got a different exception.
"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."

To solve these problems I did a long research and found a way.

1. Open command prompt
2. Change the current directory to 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727'.
3. Run aspnet_regsql.exe -S SqlServer -E -ssadd -sstype p
4. You will notice the database 'ASPState' created in the sql server give
5. That's it. Your application is ready to use the sql server to store the session state.