I found out the hard way that storing session in a SQL server takes a little more work then most of the books that I have read talk about. Most articles don?t talk about what settings you should have when you are in a web farm. This article will hopefully point you in the right directions and keep you from the headaches that I had.
One of the first things to check is the easiest to fix. You will need to make sure you have a constant machine key between servers. The machine key is used for encryption and decryption of cookie data. Unless you set the machine key to a specific code on each machine they will use their own unique key, so when your connection switches from one server to the other server it can?t decrypt the data and you lose your session.
Microsoft has a couple articles on their site on how to create the machine keys and where to place them. You can place this machine key in your machine.config or web.config files.
312906 HOW TO: Create Keys by Using Visual C# .NET for Use in Forms Authentication
313091 HOW TO: Create Keys by Using Visual Basic .NET for Use in Forms Authentication
The other fix is a little more complicated to change and is less likely to be a problem for everyone. This problem happens when you add more web sites to one server then to the other server then you try to load balance two web sites. What happens is that the application path for the web sites is different on each server. For example on one server you have a web with an application path of \LMW3SVC2 and on the other server it has an application path of \LMW3SVC4. The problem is IIS uses the application path to store the data, and if they don?t match you will lose your session. You will have to synchronize the application path for the web sites on all your web servers in the web farm.
Microsoft has an article on this problem and how to fix it. If you think this is your problem please check out this article.
325056 PRB: Session State Is Lost in Web Farm If You Use SqlServer or StateServer Session Mode
When I was going through this we had both of these problems, and after running though these steps we are now using SQL session state on our web site, on a three server web farm.