Moving from ASP Sessions to Database Sessions

[Bookham Measures]

Hello

We are planning to set-up a load balanced web environment. Accordingly, we
are going to change the session management on our website from the classic
ASP Session State and session variables, to a database method.

Does any one have any pointers as to how I might approach this, so that I
can have the same sort of functionality the ASP sessions give without having
to create database columns for each session variable I wish to create.

I am thinking along the lines of some serialised dictionary or something
that I can stick in a blob column.

Thanks in advance

David

[Bob Barrows [MVP]]

Bookham Measures wrote:

Hello

We are planning to set-up a load balanced web environment. Accordingly, we
are going to change the session management on our
website from the classic ASP Session State and session variables, to
a database method.
Does any one have any pointers as to how I might approach this, so
that I can have the same sort of functionality the ASP sessions give
without having to create database columns for each session variable I
wish to create.
I am thinking along the lines of some serialised dictionary or
something that I can stick in a blob column.

Thanks in advance

David

The simplest: three columns, with the "Variable..." columns being varchar:
UserID, VariableName, VariableValue

More functionality can be gained by adding a DateCreated and/or DateModified
column
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Dave Anderson]

"Bookham Measures" wrote:

We are planning to set-up a load balanced web environment. Accordingly,
we are going to change the session management on our website from the
classic ASP Session State and session variables, to a database method.

Does any one have any pointers as to how I might approach this, so that
I can have the same sort of functionality the ASP sessions give without
having to create database columns for each session variable I wish to
create.

I am thinking along the lines of some serialised dictionary or
something that I can stick in a blob column.

I wholeheartedly endorse this decision. It's a great way to share session
information between multiple web technologies (like ASP and ASP.NET), as
well as across servers.

We use two tables: One is common session information (session ID (PK),
session expiration, user ID (optional), and demographics (user agent, IP
address, etc.). The other contains the variables, with session ID and
name-value pairs.

We have been using this for more than three years, and have one regret --
this design does not allow scope limitation. Our next version will have
optional domain & path restrictions, much like cookies have.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Bookham Measures]

Thank you for the replies guys.

Would you say this method offered any performance benefits on it's own.
Would it be better not to use ASP sessions ever, if it could be avoided.
"Bookham Measures" <bo**********************@yahoo.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

Hello

We are planning to set-up a load balanced web environment. Accordingly,
we are going to change the session management on our website from the
classic ASP Session State and session variables, to a database method.

Does any one have any pointers as to how I might approach this, so that I
can have the same sort of functionality the ASP sessions give without
having to create database columns for each session variable I wish to
create.

I am thinking along the lines of some serialised dictionary or something
that I can stick in a blob column.

Thanks in advance

David

[Dave Anderson]

"Bookham Measures" wrote:

Would you say this method offered any performance benefits on it's
own. Would it be better not to use ASP sessions ever, if it could
be avoided.

I can't imagine that it offers any performance benefit at all. But
performance is really a secondary concern when you want to share session
information across platforms, servers, and applications.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Jon Paal [MSMD]]

How do you pass/share the PK between ASP.net and Classic ASP ?

I wholeheartedly endorse this decision. It's a great way to share session information between multiple web technologies (like ASP
and ASP.NET), as well as across servers.

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

How do you pass/share the PK between ASP.net and Classic ASP ?

You don't. You obviously have to use part of the request to identify the
session, but the PK is not required to be the shared information. We use
cookies and demographic information as a basis, and put it behind SSL when
security matters.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Jon Paal [MSMD]]

I should clarify my question...

I presume either can look up the desired session info from the database.

Where is the common value exchanged between ASP.net and Classic ASP, so the info can be looked up by either ?

"Dave Anderson" <NP**********@spammotel.comwrote in message news:OS*************@TK2MSFTNGP02.phx.gbl...

"Jon Paal [MSMD]" wrote:

>How do you pass/share the PK between ASP.net and Classic ASP ?

You don't. You obviously have to use part of the request to identify the session, but the PK is not required to be the shared
information. We use cookies and demographic information as a basis, and put it behind SSL when security matters.

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

I presume either can look up the desired session info from the database.

Where is the common value exchanged between ASP.net and Classic ASP,
so the info can be looked up by either ?

In a cookie.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Jon Paal [MSMD]]

So why not put all the session values in the cookie keys and skip the database ?

"Dave Anderson" <NP**********@spammotel.comwrote in message news:ep**************@TK2MSFTNGP06.phx.gbl...

In a cookie.

"Jon Paal [MSMD]" wrote:

>I presume either can look up the desired session info from the database.

Where is the common value exchanged between ASP.net and Classic ASP,
so the info can be looked up by either ?

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

So why not put all the session values in the cookie keys and skip the
database ?

That's like asking why people use session variables at all.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Anthony Jones]

"Jon Paal [MSMD]" <Jon nospam Paal @ everywhere dot comwrote in message
news:13*************@corp.supernews.com...

So why not put all the session values in the cookie keys and skip the

database ?

>

Because that would place a signficant burden on bandwidth. Every request to
the application would carry all this 'session' data. You also have to jump
through hoops to avoid having this blob of data being sent every time a
piece of static content such as an image is requested.

--
Anthony Jones - MVP ASP/ASP.NET

[Jon Paal [MSMD]]

um, because the question was based upon sharing sessions.

Here is an elegant solution using cookies....

http://searchvb.techtarget.com/tip/1...951935,00.html

"Dave Anderson" <NP**********@spammotel.comwrote in message news:Oc**************@TK2MSFTNGP04.phx.gbl...

"Jon Paal [MSMD]" wrote:

>So why not put all the session values in the cookie keys and skip the
database ?

That's like asking why people use session variables at all.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use of this email address implies consent to these terms.

[Jon Paal [MSMD]]

Here is an elegant solution using cookies....

http://searchvb.techtarget.com/tip/1...951935,00.html


"Anthony Jones" <An*@yadayadayada.comwrote in message news:eO**************@TK2MSFTNGP04.phx.gbl...

>
"Jon Paal [MSMD]" <Jon nospam Paal @ everywhere dot comwrote in message
news:13*************@corp.supernews.com...

>So why not put all the session values in the cookie keys and skip the

database ?

>>

Because that would place a signficant burden on bandwidth. Every request to
the application would carry all this 'session' data. You also have to jump
through hoops to avoid having this blob of data being sent every time a
piece of static content such as an image is requested.

--
Anthony Jones - MVP ASP/ASP.NET

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

>>So why not put all the session values in the cookie keys and
skip the database ?

That's like asking why people use session variables at all.

um, because the question was based upon sharing sessions.

It might have extended the topic, but it still deserves to be examined as a
self-standing question.

Consider session variables in the simplest form. The server assigns
resources to the session and identifies it with a session ID, which is sent
to the browser for use in subsequent requests. The mechanism can be 1)
URL[1], 2) querystring, or 3) cookies. In the case of ASP, it is a cookie.

Does the server send all of the session variables in Response.Cookies? No.
It sends a key to the session, not the contents of the session. The reasons
are myriad, but they include security, privacy, data integrity, practicality
and browser limits on cookie length/number. A session-sharing schema would
necessarily share these concerns, so "putting all of the session values" in
cookies would be just as bad an idea in the shared-session model as in the
single-session one.

Hence, my response. Why do people use session variables at all? The answers
to that question also answer your "why not" question.
[1] Compare these three. Each goes to the same content. The second contains
the session ID, and matches the "session-id" cookie sent when the session
was created:
http://www.amazon.com/dp/0470124482/
http://www.amazon.com/dp/0470124482/...57465-0881918/
http://www.amazon.com/Professional-A...dp/0470124482/

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

Here is an elegant solution using cookies....

http://searchvb.techtarget.com/tip/1...951935,00.html

I would not call that elegant. For one thing, it fails to address load
balancing.

It is sufficient for sharing between ASP and ASP.NET on one server, but
becomes a management nightmare (managing the ACLs grows exponentially) as
you add servers.

Suppose you now want to access those session variables in a JSP application.
Do you look in the ASP app or the ASP.NET one? Since there is no
SetSessionVar to match GetSessionVar, there is no common store; therefore,
this architecture does not allow us to determine which value is freshest.

The DB approach is far more elegant, IMO.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Jon Paal [MSMD]]

I don't want to argue with you.

The cookie solution presumes the existing ASP solution is not going to have to be rewritten to store information into a database and
allows it to be the primary source of the session value.

This will keep the classic ASP solution as the primary and any other apps like JSP would refer back to Classic ASP as the source
also.

I like the cookie solution....

[Jon Paal [MSMD]]

Yes, the presumption is the old Classic ASP is the basis for the source and new code would derive from it.
Again , I don't want to start a debate on preferences. But if someone is mixing ASP, ASP.net, JSP, etc., I think it might be time to
step back and revisit where things are going...

I like the cookie solution ...

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

...if someone is mixing ASP, ASP.net, JSP, etc., I think it might be
time to step back and revisit where things are going...

Mixing ASP and ASP.NET is no surprise. Anyone with a large base of ASP
applications may have to do this at some point.

As for other technologies (like JSP), many of us work for companies that
feel compelled to buy industry-specific off-the-shelf software that must be
integrated with the homegrown stuff (not to mention portals, ERP and content
management systems).
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Dave Anderson]

"Jon Paal [MSMD]" wrote:

The cookie solution presumes the existing ASP solution is not going
to have to be rewritten to store information into a database and allows it
to be the primary source of the session value.

This will keep the classic ASP solution as the primary and any other
apps like JSP would refer back to Classic ASP as the source also.

I agree with all of this, provided you do the extra work needed to SET
values in the classic ASP sessions (and also that the classic ASP app is not
load-balanced).

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Bookham Measures]

(bottom posted)

"Dave Anderson" <NP**********@spammotel.comwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...

"Bookham Measures" wrote:

>We are planning to set-up a load balanced web environment. Accordingly,
we are going to change the session management on our website from the
classic ASP Session State and session variables, to a database method.

Does any one have any pointers as to how I might approach this, so that
I can have the same sort of functionality the ASP sessions give without
having to create database columns for each session variable I wish to
create.

I am thinking along the lines of some serialised dictionary or
something that I can stick in a blob column.

I wholeheartedly endorse this decision. It's a great way to share session
information between multiple web technologies (like ASP and ASP.NET), as
well as across servers.

We use two tables: One is common session information (session ID (PK),
session expiration, user ID (optional), and demographics (user agent, IP
address, etc.). The other contains the variables, with session ID and
name-value pairs.

We have been using this for more than three years, and have one regret --
this design does not allow scope limitation. Our next version will have
optional domain & path restrictions, much like cookies have.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

Hello

I'm thinking of using a GUID generated by SQL Server as the Session ID and
just cookie-ing that to the browser. This should eliminate the chances of
anyone being able to guess a sequential number.

What would be the best way to protect the Session ID cookie from being
manipulated/intercepted?

I am not able to switch the whole site to SSL or supply a new checksum value
over all the querystrings/links, this information could be readable anyway.

I can implement a timeout based on the time of the last request etc. but I'm
wondering if there's anything slightly cleverer. I could probably do
something with the IP address to ensure the request, albeit it possibly
malicious, is at least originating from the same IP as that when the session
was started. This would limit the attack to the same proxy server or
corporate network perhaps.

I note in Classic ASP, that even the cookie name has been subject to some
encoding, E.G. ASPSESSIONIDASSSQCBC=JFEMNMICCBCALFJPCJOFJHFK. What is to
be gained by this and isn't it slightly inefficient to have to examine the
whole cookies collection to extract one where:

For Each strKey In Request.Cookies
If Left(strKey, 12) = "ASPSESSIONID" Then
stKeyRemain = Mid(strKey, 12)
Exit For
End If
Next

How could/does this extra bit help us or secure the cookie?

I have been reading about the various session management methods available
in ASP.NET, but none of them explain how this kind of interception is dealt
with. It's so easy now with some of the plugins available on FireFox to
manipulate almost anything.

Many thanks in advance.

David

[Dave Anderson]

"Bookham Measures" wrote:

I'm thinking of using a GUID generated by SQL Server as the Session
ID and just cookie-ing that to the browser. This should eliminate
the chances of anyone being able to guess a sequential number.

What would be the best way to protect the Session ID cookie from
being manipulated/intercepted?

SSL. Otherwise, you are vulnerable to a man-in-the-middle exploit. That is
going to be true no matter what you use for Session IDs. That is, unless you
write your own implementation of a key exchange handshake and an encryption
algorithm, plus figure out a way to keep the generated key secret and in
memory on the client across stateless requests.

I am not able to switch the whole site to SSL or supply a new
checksum value over all the querystrings/links, this information
could be readable anyway.

Yes, it could. By not using SSL, you acknowledge that you do not have true
security. You still have a need to "harden" your sessions a bit. Totally
reasonable, as not every application needs security.

I can implement a timeout based on the time of the last request etc.
but I'm wondering if there's anything slightly cleverer.

I would do this at minimum. You should tickle the database with every
request. Ours does something like this:

1. Delete all expired sessions (this has to be first)
2. Refresh the expiration date on current session
3. Return the session variables

That's on the DB side. At the web server, we then compare a couple of
things. First, if nothing is returned, the session either did not exist or
was expired, so we create a new one (sending to a logon page, if the app
requires it).

Next, we compare the user agent and IP addresses to the stored ones. If
there is an X-Forwarded-For header (HTTP_X_FORWARDED_FOR), we use it.
Otherwise, REMOTE_ADDR. Any non-match triggers a new session. Note that
there is no reason to kill the session not matched, as it may still be open
and in use. If everything matches, you have a man-in-the-middle or a true
session owner.

I could probably do something with the IP address to ensure the
request, albeit it possibly malicious, is at least originating
from the same IP as that when the session was started. This would
limit the attack to the same proxy server or corporate network
perhaps.

See above.

I note in Classic ASP, that even the cookie name has been subject
to some encoding, E.G. ASPSESSIONIDASSSQCBC=JFEMNMICCBCALFJPCJOFJHFK.
What is to be gained by this and isn't it slightly inefficient to
have to examine the whole cookies collection to extract one where:

For Each strKey In Request.Cookies
If Left(strKey, 12) = "ASPSESSIONID" Then
stKeyRemain = Mid(strKey, 12)
Exit For
End If
Next

How could/does this extra bit help us or secure the cookie?

I don't know. I imagine it is useful for identifying the originating server
in a clustered environment. If you are using a GUID, this will be moot.

I have been reading about the various session management methods
available in ASP.NET, but none of them explain how this kind of
interception is dealt with. It's so easy now with some of the
plugins available on FireFox to manipulate almost anything.

Right. That's because true security starts with SSL.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Bookham Measures]

"Dave Anderson" <NP**********@spammotel.comwrote in message
news:13*************@corp.supernews.com...

"Bookham Measures" wrote:

>I'm thinking of using a GUID generated by SQL Server as the Session
ID and just cookie-ing that to the browser. This should eliminate
the chances of anyone being able to guess a sequential number.

What would be the best way to protect the Session ID cookie from
being manipulated/intercepted?

SSL. Otherwise, you are vulnerable to a man-in-the-middle exploit. That is
going to be true no matter what you use for Session IDs. That is, unless
you write your own implementation of a key exchange handshake and an
encryption algorithm, plus figure out a way to keep the generated key
secret and in memory on the client across stateless requests.

>I am not able to switch the whole site to SSL or supply a new
checksum value over all the querystrings/links, this information
could be readable anyway.

Yes, it could. By not using SSL, you acknowledge that you do not have true
security. You still have a need to "harden" your sessions a bit. Totally
reasonable, as not every application needs security.

>I can implement a timeout based on the time of the last request etc.
but I'm wondering if there's anything slightly cleverer.

I would do this at minimum. You should tickle the database with every
request. Ours does something like this:

1. Delete all expired sessions (this has to be first)
2. Refresh the expiration date on current session
3. Return the session variables

That's on the DB side. At the web server, we then compare a couple of
things. First, if nothing is returned, the session either did not exist or
was expired, so we create a new one (sending to a logon page, if the app
requires it).

Next, we compare the user agent and IP addresses to the stored ones. If
there is an X-Forwarded-For header (HTTP_X_FORWARDED_FOR), we use it.
Otherwise, REMOTE_ADDR. Any non-match triggers a new session. Note that
there is no reason to kill the session not matched, as it may still be
open and in use. If everything matches, you have a man-in-the-middle or a
true session owner.

>I could probably do something with the IP address to ensure the
request, albeit it possibly malicious, is at least originating
from the same IP as that when the session was started. This would
limit the attack to the same proxy server or corporate network
perhaps.

See above.

>I note in Classic ASP, that even the cookie name has been subject
to some encoding, E.G. ASPSESSIONIDASSSQCBC=JFEMNMICCBCALFJPCJOFJHFK.
What is to be gained by this and isn't it slightly inefficient to
have to examine the whole cookies collection to extract one where:

For Each strKey In Request.Cookies
If Left(strKey, 12) = "ASPSESSIONID" Then
stKeyRemain = Mid(strKey, 12)
Exit For
End If
Next

How could/does this extra bit help us or secure the cookie?

I don't know. I imagine it is useful for identifying the originating
server in a clustered environment. If you are using a GUID, this will be
moot.

>I have been reading about the various session management methods
available in ASP.NET, but none of them explain how this kind of
interception is dealt with. It's so easy now with some of the
plugins available on FireFox to manipulate almost anything.

Right. That's because true security starts with SSL.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

Thanks Dave.

I have now implemented something I think that will suffice. It is based on
the IP and User Agent and the actual name of the cookie is based on some
number crunching on both. This saves even going to the database if the
cookie does not exist based on this info from the client, regardless. I
thought I would get unstuck with AJAX stuff, but it would seem that IE and
FireFox forward the browser user agent versus WinHttp or similar which I've
seen when working with XMLHTTPRequest from the server side. So, so far so
good. Thanks again.

[Dave Anderson]

"Bookham Measures" wrote:

I thought I would get unstuck with AJAX stuff, but it would seem that
IE and FireFox forward the browser user agent versus WinHttp or similar
which I've seen when working with XMLHTTPRequest from the server side.

I believe you see that because WinHttp *IS* the browser when the server
makes the request.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[David Morgan]

"Dave Anderson" <NP**********@spammotel.comwrote in message
news:e8**************@TK2MSFTNGP05.phx.gbl...

"Bookham Measures" wrote:

>I thought I would get unstuck with AJAX stuff, but it would seem that
IE and FireFox forward the browser user agent versus WinHttp or similar
which I've seen when working with XMLHTTPRequest from the server side.

I believe you see that because WinHttp *IS* the browser when the server
makes the request.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

Can I just confirm that the only reason we're not using ASP session
management is because we think that we will get requests from the same
client via proxy servers on different class C subnets.

I am reading about NLB, now that I understand it a bit more, and it is quite
clear that one can set a client affinity that allows for the support of ASP
"server cookie" sessions. You can either choose to have all requests from
the same IP go to the same server OR all requests from the same class C
going (class C mask applied to the client IP) to the same server (in the
case of client requests coming via different proxies).

Presumably the likes of AOL have huge proxy server farms that span many IP
address ranges.

[Dave Anderson]

"David Morgan" wrote:

Can I just confirm that the only reason we're not using ASP
session management is because we think that we will get requests
from the same client via proxy servers on different class C
subnets.

That doesn't really make sense to me. Why would the web server care? Each
request comes with a bundle of headers, including cookies. They are tied to
the individual browser, not to any proxy in between. No proxy is going to
add a cookie that was not part of an original request, so why should
sessions be shared?

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Anthony Jones]

"Dave Anderson" <NP**********@spammotel.comwrote in message
news:Oo**************@TK2MSFTNGP03.phx.gbl...

"David Morgan" wrote:

Can I just confirm that the only reason we're not using ASP
session management is because we think that we will get requests
from the same client via proxy servers on different class C
subnets.

That doesn't really make sense to me. Why would the web server care? Each
request comes with a bundle of headers, including cookies. They are tied

to

the individual browser, not to any proxy in between. No proxy is going to
add a cookie that was not part of an original request, so why should
sessions be shared?

Servers A and B are identical web servers forming a farm. WLB is used to
load balance requests coming from clients. A fresh browser session on
Client Z makes a request to an ASP URL that the WLB serves. WLB pass that
request to Server A. Server A creates a new session for Client Z and loads
up some relevant session variables. The response carries the an ASP session
cookie.

Client Z now makes a subsequent request to another ASP URL served by the
WLB, it expects the relevant session info created by the initial request to
still be valid. WLB takes the request but this time decides the Server B is
the least busy so forwards it there. Despite the request containing an ASP
session cookie Server B has no idea what that session is. The application
is broken.

WLB provides a feature to create an affinity between an ASP session and one
of the servers by means of sniffing the ASP session cookie. All requests
carrying a specified ASP session cookie are subsequently forwarded to an
appropriate server. This is less than ideal from a load balancing point of
view creating a potentially lop-sided loading.

Allegedly :P
--
Anthony Jones - MVP ASP/ASP.NET