SqlConnection object stored in Session ? Wise move or not ? 1

[StevenK]

I've been working on a web application (for the main part to be used on an internal intranet but in some instance to be accessed via the internet).
I'm using Visual Studio ASP.NET (with C# code).
I had been developing it in such I way that I was saving the SqlConnection in the 'Session' object (with the object primed with the connection string consisting of the server-name, database-name, etc).
However it was suggested to me by a colleague that this was not a good move and he instead suggested I create the connection as required, creating the connection string in each instance.
My question is this :
- How sensible is it to store a SqlConnection object in the 'Session' object ?
- Should I be managing this practise in some other way ?
[For instance saving the connection string, or it's parts within the 'Session' object and creating the connection as required - be it saving the text as is or encrypted and then decrypting it when needed].

Any advice / pointers would be appreciated.
Thanks in advance
Steve

 

[chiph]

For a web app, you want to reconnect to the database with each request. ASP.NET will cache your connection objects, so it'll actually be quite fast.

If everyone is using the same database, maybe it'd be better to store the connectString in the Application object (or web.config). That way it only gets stored once, rather than in each user's Session object (which takes up server memory).

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[StevenK]

OK - to make life (slightly more) interesting the web application's starting point is the selection of an instance of SQL Server (visible on the network) and furthermore the selection of a database on that server.
As such, I am unable to store the connectString in the Application object as different users may be working against different databases on different servers (potentially).
And similarly this will prevent me from storing the connection string in the web.config file.

So, I'm back to where I started in the sense that I'm still trying to establish whether I should be storing the SqlConnection object in the 'Session' object ?
Or should I be storing the connection string in the 'Session' object ?
And furthermore should I encrypt it and only encrypt and use it to create the connection as and when it's needed ?

Thanks so far.
Steve

 

[chiph]

Since every user is (potentially) on a different database server, you have no choice but to store the connectString in the session object.

I would not store the actual DB session object in the HTTP Session object. User interactions with a web server are more on the order of an "ad-hoc" usage, and you wouldn't want to maintain that connection (remember: connections are licensed!) any longer than you have to. I would store the connectString (it's a String, not an object, so it's much lighter-weight).

Plus, a web-user can disconnect at any time (kill the browser from the task manager), so you'll never receive a "user logoff" event, so the connection will continue to hang around, taking up server resources.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[StevenK]

Should I be storing an encrypted version of the connection string if I do ?
Will I notice any slowdown with the fact that I'm re-creating my connection each time I'm wanting to use it (rather than just pulling the SqlConnection object from the 'Session') ?

If I never receive a log-off surely this is something of a problem against SQL Server (resources and all).
Is there any code I can implement (Session close or something) that would terminate my connection ?

Or would it be enough to simply create and open the SqlConnection (using connection String from 'Session') and then closing the SqlConnection after working with it ?

Would this keep things clean ?

Thanks again,
Steve

 

[chiph]

Open and close the connection every time you need it. ASP.NET has a collection of cached DB connections, so it'll be quite fast. The idea is to get in and get out as fast as you can.

Don't forget that if you're inserting/updating data, do any SELECT operations outside of your transactions. If you do any data retrieval inside your transaction you just slow things down by bulking up the transaction log on the DB server unnecessarily. This goes along with the idea that DB reads do not participant in transactions, so you want to leave them out.

You can encrypt the connect string if you like. I don't think there's any security risk by just leaving it in the session object -- after all, your database server won't have a public IP address, right?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[StevenK]

It would be OK to store the connection string in the 'Session' object as is ?
Is that what we're saying ?
Can hackers not hi-jack the 'Session' or read values stored in it ? [therefore giving away database access information] ?
Thanks again.
Steve

 

[chiph]

Yes, we're talking about storing the connection string in the session object.

The Session is stored in memory on the web server. The only thing that is sent to the client is an ID value in a cookie, or in the URL via URL-rewriting if the client has cookies disabled. So the contents of the HttpSessionState object (alias to just "Session") are never sent across the wire, and thus cannot be sniffed.

The Session ID value is a 120-bit character value that is generated by a cryptographic hash, and would be nearly impossible to duplicate, preventing session hijacking.

If you have the MSDN help installed, see more at ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconSessionState.htm

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[StevenK]

As such could I therefore store the connection string in the 'Session' object as is ?
Or would it be prudent to save an encrypted version of the connection string and then decrypt this each time I need to make use of it in opening up a new SQL connection.
Thanks for your help.
Steve

 

[chiph]

Yes, you can and should store the connection string in the session object.

There is no real need to encrypt it, but if you want to, you can.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[StevenK]

Thanks for your help chiph.
Steve

 

[DLLHell]

Just out of curiosity, why would people want to use session variables and have a web app that times out every 20 minutes? Even if you change the IIS default to 60 minutes, that impacts the server.

Pros/Cons of using session variables?

 

[DLLHell]

Also, session variables = no load balancing servers in ASP.NET?

 

[chiph]

Storing things in the session is an easy way to maintain state in your application. But, as you point out, the trade-off is that sessions will eventually time out due to inactivity. If you need longer-lived sessions, then you need to store the info in a persistent data store (aka database or flat-file), and allow the user to resume using it when they next connect.

Session variables can be load-balanced under ASP.NET, but AFAIK, you can't use a hardware load balancer -- you have to use the load balancing software that comes with Windows Server 2003. I could be wrong -- there might be new firmware out now that supports ASP.NET sessions.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first