Can I store my session state in a database other than tempdb, In SqlServer mode?
Yes. For more details Refer HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management
What kind of objects can I store in a Session State
Depends on which mode you are using: InProc Mode- objects stored in session state are actually live objects, and so you can store whatever object you have created. State Server or SQL Server mode, objects in the session state will be serialized and deserialized when a request is processed. So make sure your objects are serializable and their classes must be marked as so. If not, the session state will not be saved successfully. In v1, there is a bug which makes the problem happen unnoticed in SQLServer mode and will make your request hang. The hanging problem is fixed in v1.1. The fix for KB 324479: ASP.NET SQL Server Session State Impersonation Is Lost Under Load also contains the fix for this problem. The problem will be fixed in v1 SP3 too. For more info: BUG: Session Data Is Not Saved in Out-of-Process Session State
How can I avoid specifying a plain password for my sql connection
Include a sql trusted connection, or put the connection string as encrypted data in the registry. Refer FIX: Stronger Credentials for processModel, identity, and sessionState How To Use the ASP.NET Utility to Encrypt Credentials and Session State Connection Strings
Can different apps store their session state in different databases on the same SQL server?
Yes. Refer FIX: Using one SQL database for all applications for SQL Server session state may cause a bottleneck
Why do I get the error message ‘Session state can only be used when enableSessionState is set to true, either in a configuration file or in the Page directive’
This problem may occur after you install Windows Sharepoint Server(WSS) on a server that has Microsoft Visual Studio .NET 2003 installed. The WSS ISAPI filter handles all incoming URLs. When you browse one of the ASP.NET Web application virtual directories, the ISAPI filter does not locate the URL path of the folder. To resolve this refer Session state cannot be used in ASP.NET with Windows SharePoint Services