B
Brian Gambs
I've been trying to figure out how to properly install
and configure ASPState (fwk 1.1,non-persistent, sql based
state) in a way that
a. works
b. doesn't require an account with system
administrator/sa access
Before I begin -- I know -- and accept -- that state
information does not survives a sql restart unless you
use the persist state version. I see an upside in using
tempdb for storage and I'm fine with the downsides.
Except...
The only thing resembling microsoft documentation that
even refers to setting up security for this is here
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnnetsec/html/THCMCh19.asp
It suggests that one needs to grant exec access on all
the stored procs in aspstate and you are good to go.
This demonstrably isn't true, totally reproducibly so.
You need to assign permissions in tempdb, ideally "dbo"
permissions.
Okay, fine. Weird, but, at least I know what I must do.
Unfortunately, this doesn't survive a sql server restart.
If you create a login for, say "aspnet" with only the
default aspnet permissions, use sql integrated security
to give it a sqllogin, map it into an application
database, aspstate, and tempdb, give it exec perms in
aspstate and dbo rights in tempdb, then try to access
session state, this works.
Restart sql sqlserver, and you fail with horrible
exceptions, e.g:
"INSERT permission denied on
object 'ASPStateTempSessions', database 'tempdb',
owner 'dbo'. "
Checking tempdb, the login for aspnet is no longer
present in tempdb. Presumably, tempdb is recreated on
restart.
So....you need to go through a complicated dance, most
easily performed by dropping and recreating aspstate db.
Not so cool for a production system.
Now, checking the code for the stored procedures in
aspstate, one can see that they reference tempdb
directly, e.g.:
(from TempInsertStateItemLong)
INSERT tempdb..ASPStateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockDateLocal,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
@nowLocal,
1)
This strikes me as a terrible, horrible, approach to
accessing tempdb. Maybe this is
allowed/supported/endorsed now and I am unaware, but,
clearly, this engenders (so far as I can tell) pretty
severe adverse consequences if you are trying to actually
allow your applications to continue WORKING after a sql
server restart. Again, I know I'm going to lose the state
information itself. I would just like to not have to
reinstall databases, frantically rework security, etc.
So, what am I missing here? For now, we are going to go
with the persiststate version, because it seems to be
pretty much identical except that instead of tempdb.. you
get aspstate.. which is obviously going to work...
Any comments or help appreciated.
Brian
and configure ASPState (fwk 1.1,non-persistent, sql based
state) in a way that
a. works
b. doesn't require an account with system
administrator/sa access
Before I begin -- I know -- and accept -- that state
information does not survives a sql restart unless you
use the persist state version. I see an upside in using
tempdb for storage and I'm fine with the downsides.
Except...
The only thing resembling microsoft documentation that
even refers to setting up security for this is here
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnnetsec/html/THCMCh19.asp
It suggests that one needs to grant exec access on all
the stored procs in aspstate and you are good to go.
This demonstrably isn't true, totally reproducibly so.
You need to assign permissions in tempdb, ideally "dbo"
permissions.
Okay, fine. Weird, but, at least I know what I must do.
Unfortunately, this doesn't survive a sql server restart.
If you create a login for, say "aspnet" with only the
default aspnet permissions, use sql integrated security
to give it a sqllogin, map it into an application
database, aspstate, and tempdb, give it exec perms in
aspstate and dbo rights in tempdb, then try to access
session state, this works.
Restart sql sqlserver, and you fail with horrible
exceptions, e.g:
"INSERT permission denied on
object 'ASPStateTempSessions', database 'tempdb',
owner 'dbo'. "
Checking tempdb, the login for aspnet is no longer
present in tempdb. Presumably, tempdb is recreated on
restart.
So....you need to go through a complicated dance, most
easily performed by dropping and recreating aspstate db.
Not so cool for a production system.
Now, checking the code for the stored procedures in
aspstate, one can see that they reference tempdb
directly, e.g.:
(from TempInsertStateItemLong)
INSERT tempdb..ASPStateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockDateLocal,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
@nowLocal,
1)
This strikes me as a terrible, horrible, approach to
accessing tempdb. Maybe this is
allowed/supported/endorsed now and I am unaware, but,
clearly, this engenders (so far as I can tell) pretty
severe adverse consequences if you are trying to actually
allow your applications to continue WORKING after a sql
server restart. Again, I know I'm going to lose the state
information itself. I would just like to not have to
reinstall databases, frantically rework security, etc.
So, what am I missing here? For now, we are going to go
with the persiststate version, because it seems to be
pretty much identical except that instead of tempdb.. you
get aspstate.. which is obviously going to work...
Any comments or help appreciated.
Brian