ASPState Bug/Security issue

[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
[url]http://msdn.microsoft.com/library/default.asp?[/url]
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

[Yan-Hong Huang[MSFT]]

Hello Brian,

I checked the InstallPersistSqlState.sql file on my side.

CREATE PROCEDURE TempInsertStateItemLong
@id tSessionId,
@itemLong tSessionItemLong,
@timeout INT
AS
DECLARE @now as DATETIME
SET @now = GETDATE()

INSERT ASPState..ASPStateTempSessions
(SessionId,
SessionItemLong,
Timeout,
Expires,
Locked,
LockDate,
LockCookie)
VALUES
(@id,
@itemLong,
@timeout,
DATEADD(n, @timeout, @now),
0,
@now,
1)

RETURN 0
GO

Did you download the persist state file at [url]http://support.microsoft.com/?id=311209?[/url] Thanks very much.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - [url]www.microsoft.com/security[/url]
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Brian Gambs" <nobgambsspam@healthshare.com>
!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
!Subject: ASPState Bug/Security issue
!Date: Thu, 24 Jul 2003 08:09:11 -0700
!Lines: 93
!Message-ID: <0df101c351f5$8999eef0$a401280a@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Thread-Index: AcNR9YmZoldjojzWS/m5bSgeW0QzwA==
!Newsgroups: microsoft.public.dotnet.framework.aspnet.security
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet.security: 6008
!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.security
!
!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
![url]http://msdn.microsoft.com/library/default.asp?[/url]
!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
!
!

[Brian Gambs]

Hi -- I am not sure what you are saying.

InstallPersistSqlState.sql will indeed "resolve" my
problem in the sense of avoiding the issue.

It doesn't really answer my question, however.

My question is: what is the proper way to configure SQL
Server security for ASP.Net state, where the state is
being stored in tempdb (InstallSqlState.sql).

My suspicion is that there is no way to install this and
configure permissions such that you can shut down and
restart sql server without having to reset permissions
and possibly reinstall the state database.

Brian

>-----Original Message-----
>Hello Brian,
>
>I checked the InstallPersistSqlState.sql file on my

side.

>
>CREATE PROCEDURE TempInsertStateItemLong
> @id tSessionId,
> @itemLong tSessionItemLong,
> @timeout INT
>AS
> DECLARE @now as DATETIME
> SET @now = GETDATE()
>
> INSERT ASPState..ASPStateTempSessions
> (SessionId,
> SessionItemLong,
> Timeout,
> Expires,
> Locked,
> LockDate,
> LockCookie)
> VALUES
> (@id,
> @itemLong,
> @timeout,
> DATEADD(n, @timeout, @now),
> 0,
> @now,
> 1)
>
> RETURN 0
>GO
>
>Did you download the persist state file at

[url]http://support.microsoft.com/?id=311209?[/url] Thanks very much.

>
>Best regards,
>Yanhong Huang
>Microsoft Online Partner Support
>
>Get Secure! - [url]www.microsoft.com/security[/url]
>This posting is provided "AS IS" with no warranties, and

confers no rights.

>
>--------------------
>!Content-Class: urn:content-classes:message
>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
>!Subject: ASPState Bug/Security issue
>!Date: Thu, 24 Jul 2003 08:09:11 -0700
>!Lines: 93
>!Message-ID: <0df101c351f5$8999eef0$a401280a@phx.gbl>
>!MIME-Version: 1.0
>!Content-Type: text/plain;
>! charset="iso-8859-1"
>!Content-Transfer-Encoding: 7bit
>!X-Newsreader: Microsoft CDO for Windows 2000
>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>!Thread-Index: AcNR9YmZoldjojzWS/m5bSgeW0QzwA==
>!Newsgroups:

microsoft.public.dotnet.framework.aspnet.security

>!Path: cpmsftngxa06.phx.gbl
>!Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.aspnet.security: 6008

>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>!X-Tomcat-NG:

microsoft.public.dotnet.framework.aspnet.security

>!
>!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
>![url]http://msdn.microsoft.com/library/default.asp?[/url]
>!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
>!
>!
>
>
>.
>

[Yan-Hong Huang[MSFT]]

Hello Brian,

Now I totally understand what you meant.

Please refer to the following statement from SQL server books online:

* tempdb
tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such
as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all
users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts
with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on
disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from
one session of SQL Server to another.

From it, we can see that tempdb is recreated every time SQL Server is started. So I don't think there is any way to achieve
what you need by using InstallSqlState.sql. The only way to resolve it is to use persiststate version.

Did I answer your question? Thanks.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - [url]www.microsoft.com/security[/url]
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Brian Gambs" <nobgambsspam@healthshare.com>
!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
!References: <0df101c351f5$8999eef0$a401280a@phx.gbl> <dx1u0AOVDHA.2112@cpmsftngxa06.phx.gbl>
!Subject: RE: ASPState Bug/Security issue
!Date: Tue, 29 Jul 2003 14:18:22 -0700
!Lines: 191
!Message-ID: <04df01c35616$f0de8510$a401280a@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!Thread-Index: AcNWFvDeTJ2I3D+RTSmxKOeE2U6O6Q==
!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Newsgroups: microsoft.public.dotnet.framework.aspnet.security
!Path: cpmsftngxa06.phx.gbl
!Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet.security: 6068
!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.security
!
!Hi -- I am not sure what you are saying.
!
!InstallPersistSqlState.sql will indeed "resolve" my
!problem in the sense of avoiding the issue.
!
!It doesn't really answer my question, however.
!
!My question is: what is the proper way to configure SQL
!Server security for ASP.Net state, where the state is
!being stored in tempdb (InstallSqlState.sql).
!
!My suspicion is that there is no way to install this and
!configure permissions such that you can shut down and
!restart sql server without having to reset permissions
!and possibly reinstall the state database.
!
!Brian
!
!>-----Original Message-----
!>Hello Brian,
!>
!>I checked the InstallPersistSqlState.sql file on my
!side.
!>
!>CREATE PROCEDURE TempInsertStateItemLong
!> @id tSessionId,
!> @itemLong tSessionItemLong,
!> @timeout INT
!>AS
!> DECLARE @now as DATETIME
!> SET @now = GETDATE()
!>
!> INSERT ASPState..ASPStateTempSessions
!> (SessionId,
!> SessionItemLong,
!> Timeout,
!> Expires,
!> Locked,
!> LockDate,
!> LockCookie)
!> VALUES
!> (@id,
!> @itemLong,
!> @timeout,
!> DATEADD(n, @timeout, @now),
!> 0,
!> @now,
!> 1)
!>
!> RETURN 0
!>GO
!>
!>Did you download the persist state file at
![url]http://support.microsoft.com/?id=311209?[/url] Thanks very much.
!>
!>Best regards,
!>Yanhong Huang
!>Microsoft Online Partner Support
!>
!>Get Secure! - [url]www.microsoft.com/security[/url]
!>This posting is provided "AS IS" with no warranties, and
!confers no rights.
!>
!>--------------------
!>!Content-Class: urn:content-classes:message
!>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
!>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
!>!Subject: ASPState Bug/Security issue
!>!Date: Thu, 24 Jul 2003 08:09:11 -0700
!>!Lines: 93
!>!Message-ID: <0df101c351f5$8999eef0$a401280a@phx.gbl>
!>!MIME-Version: 1.0
!>!Content-Type: text/plain;
!>! charset="iso-8859-1"
!>!Content-Transfer-Encoding: 7bit
!>!X-Newsreader: Microsoft CDO for Windows 2000
!>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!>!Thread-Index: AcNR9YmZoldjojzWS/m5bSgeW0QzwA==
!>!Newsgroups:
!microsoft.public.dotnet.framework.aspnet.security
!>!Path: cpmsftngxa06.phx.gbl
!>!Xref: cpmsftngxa06.phx.gbl
!microsoft.public.dotnet.framework.aspnet.security :6008
!>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
!>!X-Tomcat-NG:
!microsoft.public.dotnet.framework.aspnet.security
!>!
!>!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
!>![url]http://msdn.microsoft.com/library/default.asp?[/url]
!>!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
!>!
!>!
!>
!>
!>.
!>
!

[Brian Gambs]

Okay -- I get it. But isn't this a horrible, horrible
design flaw in the non-persistent version of state?

I accept that my *state* doesn't persist after sql server
restarts.

It just seems crazy that the *security settings* that
allow users to use the state database don't survive a
restart.

It seems impossible that this could have gotten through
qa unless the aspnet account was running as system
administrator/sa on the sql server where the state
database lives. Because the problem doesn't manifest
itself if the aspnet account or account under which the
accessing app is running is god.

So, to recap:

(1) There is no documentation of how to set up security
on aspstate database to use it when the accessing account
is other than god.

(2) You cannot use the non-persistent version if your
accessing accounts are running as something other than
god, and expect to have your application continue working
after a sql server restart EVEN IF YOU DON'T CARE THAT
YOU LOSE THE STATE INFORMATION ITSELF after the restart.

(2) (2) is undocumented.

Again, this is what I thought going in. I'm just bemused,
since as far as I can tell, merely by following
Microsoft's guidelines for how to access tempdb properly,
Microsoft could have easily avoided (2).

Brian

>-----Original Message-----
>Hello Brian,
>
>Now I totally understand what you meant.
>
>Please refer to the following statement from SQL server

books online:

>
>* tempdb
>tempdb holds all temporary tables and temporary stored

procedures. It also fills any other temporary storage
needs such

>as work tables generated by SQL Server. tempdb is a

global resource; the temporary tables and stored
procedures for all

>users connected to the system are stored there. tempdb

is re-created every time SQL Server is started so the
system starts

>with a clean copy of the database. Because temporary

tables and stored procedures are dropped automatically on

>disconnect, and no connections are active when the

system is shut down, there is never anything in tempdb to
be saved from

>one session of SQL Server to another.
>
>From it, we can see that tempdb is recreated every time

SQL Server is started. So I don't think there is any way
to achieve

>what you need by using InstallSqlState.sql. The only way

to resolve it is to use persiststate version.

>
>Did I answer your question? Thanks.
>
>Best regards,
>Yanhong Huang
>Microsoft Online Partner Support
>
>Get Secure! - [url]www.microsoft.com/security[/url]
>This posting is provided "AS IS" with no warranties, and

confers no rights.

>
>--------------------
>!Content-Class: urn:content-classes:message
>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
>!References: <0df101c351f5$8999eef0$a401280a@phx.gbl>

<dx1u0AOVDHA.2112@cpmsftngxa06.phx.gbl>

>!Subject: RE: ASPState Bug/Security issue
>!Date: Tue, 29 Jul 2003 14:18:22 -0700
>!Lines: 191
>!Message-ID: <04df01c35616$f0de8510$a401280a@phx.gbl>
>!MIME-Version: 1.0
>!Content-Type: text/plain;
>! charset="iso-8859-1"
>!Content-Transfer-Encoding: 7bit
>!X-Newsreader: Microsoft CDO for Windows 2000
>!Thread-Index: AcNWFvDeTJ2I3D+RTSmxKOeE2U6O6Q==
>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>!Newsgroups:

microsoft.public.dotnet.framework.aspnet.security

>!Path: cpmsftngxa06.phx.gbl
>!Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.aspnet.security: 6068

>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>!X-Tomcat-NG:

microsoft.public.dotnet.framework.aspnet.security

>!
>!Hi -- I am not sure what you are saying.
>!
>!InstallPersistSqlState.sql will indeed "resolve" my
>!problem in the sense of avoiding the issue.
>!
>!It doesn't really answer my question, however.
>!
>!My question is: what is the proper way to configure SQL
>!Server security for ASP.Net state, where the state is
>!being stored in tempdb (InstallSqlState.sql).
>!
>!My suspicion is that there is no way to install this

and

>!configure permissions such that you can shut down and
>!restart sql server without having to reset permissions
>!and possibly reinstall the state database.
>!
>!Brian
>!
>!>-----Original Message-----
>!>Hello Brian,
>!>
>!>I checked the InstallPersistSqlState.sql file on my
>!side.
>!>
>!>CREATE PROCEDURE TempInsertStateItemLong
>!> @id tSessionId,
>!> @itemLong tSessionItemLong,
>!> @timeout INT
>!>AS
>!> DECLARE @now as DATETIME
>!> SET @now = GETDATE()
>!>
>!> INSERT ASPState..ASPStateTempSessions
>!> (SessionId,
>!> SessionItemLong,
>!> Timeout,
>!> Expires,
>!> Locked,
>!> LockDate,
>!> LockCookie)
>!> VALUES
>!> (@id,
>!> @itemLong,
>!> @timeout,
>!> DATEADD(n, @timeout, @now),
>!> 0,
>!> @now,
>!> 1)
>!>
>!> RETURN 0
>!>GO
>!>
>!>Did you download the persist state file at
>![url]http://support.microsoft.com/?id=311209?[/url] Thanks very

much.

>!>
>!>Best regards,
>!>Yanhong Huang
>!>Microsoft Online Partner Support
>!>
>!>Get Secure! - [url]www.microsoft.com/security[/url]
>!>This posting is provided "AS IS" with no warranties,

and

>!confers no rights.
>!>
>!>--------------------
>!>!Content-Class: urn:content-classes:message
>!>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
>!>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
>!>!Subject: ASPState Bug/Security issue
>!>!Date: Thu, 24 Jul 2003 08:09:11 -0700
>!>!Lines: 93
>!>!Message-ID: <0df101c351f5$8999eef0$a401280a@phx.gbl>
>!>!MIME-Version: 1.0
>!>!Content-Type: text/plain;
>!>! charset="iso-8859-1"
>!>!Content-Transfer-Encoding: 7bit
>!>!X-Newsreader: Microsoft CDO for Windows 2000
>!>!X-MimeOLE: Produced By Microsoft MimeOLE

V5.50.4910.0300

>!>!Thread-Index: AcNR9YmZoldjojzWS/m5bSgeW0QzwA==
>!>!Newsgroups:
>!microsoft.public.dotnet.framework.aspnet.securit y
>!>!Path: cpmsftngxa06.phx.gbl
>!>!Xref: cpmsftngxa06.phx.gbl
>!microsoft.public.dotnet.framework.aspnet.securit y:6008
>!>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>!>!X-Tomcat-NG:
>!microsoft.public.dotnet.framework.aspnet.securit y
>!>!
>!>!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
>!>![url]http://msdn.microsoft.com/library/default.asp?[/url]
>!>!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
>!>!
>!>!
>!>
>!>
>!>.
>!>
>!
>
>
>.
>

[Jim Cheshire [MSFT]]

Brian,

All of the documentation surrounding SQL Server session state with ASP.NET
uses the sa account specifically for this reason. If you want to use an
account that does not have dbo access, you will need to create a stored
procedure in SQL Server to add that account to your tempdb database and
then use the sp_procoption stored procedure to configure it as a startup
procedure.

Hope that helps.

Jim Cheshire [MSFT]
Developer Support
ASP.NET
[email]jamesche@online.microsoft.com[/email]

This post is provided as-is with no warranties and confers no rights.

--------------------

>Content-Class: urn:content-classes:message
>From: "Brian Gambs" <nosbgambspam@healthshare.com>
>Sender: "Brian Gambs" <nosbgambspam@healthshare.com>
>References: <0df101c351f5$8999eef0$a401280a@phx.gbl>

<dx1u0AOVDHA.2112@cpmsftngxa06.phx.gbl>
<04df01c35616$f0de8510$a401280a@phx.gbl>
<t5DvYK0VDHA.2160@cpmsftngxa06.phx.gbl>

>Subject: RE: ASPState Bug/Security issue
>Date: Thu, 31 Jul 2003 07:30:13 -0700
>Lines: 320
>Message-ID: <006b01c35770$40bb0d60$a101280a@phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcNXcEC4i4W+G7jERDmwPCGfSMIGCQ==
>Newsgroups: microsoft.public.dotnet.framework.aspnet.security
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.aspnet.security: 6086

>NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
>X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.security
>
>Okay -- I get it. But isn't this a horrible, horrible
>design flaw in the non-persistent version of state?
>
>I accept that my *state* doesn't persist after sql server
>restarts.
>
>It just seems crazy that the *security settings* that
>allow users to use the state database don't survive a
>restart.
>
>It seems impossible that this could have gotten through
>qa unless the aspnet account was running as system
>administrator/sa on the sql server where the state
>database lives. Because the problem doesn't manifest
>itself if the aspnet account or account under which the
>accessing app is running is god.
>
>So, to recap:
>
>(1) There is no documentation of how to set up security
>on aspstate database to use it when the accessing account
>is other than god.
>
>(2) You cannot use the non-persistent version if your
>accessing accounts are running as something other than
>god, and expect to have your application continue working
>after a sql server restart EVEN IF YOU DON'T CARE THAT
>YOU LOSE THE STATE INFORMATION ITSELF after the restart.
>
>(2) (2) is undocumented.
>
>Again, this is what I thought going in. I'm just bemused,
>since as far as I can tell, merely by following
>Microsoft's guidelines for how to access tempdb properly,
>Microsoft could have easily avoided (2).
>
>Brian
>
>
>
>

>>-----Original Message-----
>>Hello Brian,
>>
>>Now I totally understand what you meant.
>>
>>Please refer to the following statement from SQL server

>books online:

>>
>>* tempdb
>>tempdb holds all temporary tables and temporary stored

>procedures. It also fills any other temporary storage
>needs such

>>as work tables generated by SQL Server. tempdb is a

>global resource; the temporary tables and stored
>procedures for all

>>users connected to the system are stored there. tempdb

>is re-created every time SQL Server is started so the
>system starts

>>with a clean copy of the database. Because temporary

>tables and stored procedures are dropped automatically on

>>disconnect, and no connections are active when the

>system is shut down, there is never anything in tempdb to
>be saved from

>>one session of SQL Server to another.
>>
>>From it, we can see that tempdb is recreated every time

>SQL Server is started. So I don't think there is any way
>to achieve

>>what you need by using InstallSqlState.sql. The only way

>to resolve it is to use persiststate version.

>>
>>Did I answer your question? Thanks.
>>
>>Best regards,
>>Yanhong Huang
>>Microsoft Online Partner Support
>>
>>Get Secure! - [url]www.microsoft.com/security[/url]
>>This posting is provided "AS IS" with no warranties, and

>confers no rights.

>>
>>--------------------
>>!Content-Class: urn:content-classes:message
>>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
>>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
>>!References: <0df101c351f5$8999eef0$a401280a@phx.gbl>

><dx1u0AOVDHA.2112@cpmsftngxa06.phx.gbl>

>>!Subject: RE: ASPState Bug/Security issue
>>!Date: Tue, 29 Jul 2003 14:18:22 -0700
>>!Lines: 191
>>!Message-ID: <04df01c35616$f0de8510$a401280a@phx.gbl>
>>!MIME-Version: 1.0
>>!Content-Type: text/plain;
>>! charset="iso-8859-1"
>>!Content-Transfer-Encoding: 7bit
>>!X-Newsreader: Microsoft CDO for Windows 2000
>>!Thread-Index: AcNWFvDeTJ2I3D+RTSmxKOeE2U6O6Q==
>>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>!Newsgroups:

>microsoft.public.dotnet.framework.aspnet.securi ty

>>!Path: cpmsftngxa06.phx.gbl
>>!Xref: cpmsftngxa06.phx.gbl

>microsoft.public.dotnet.framework.aspnet.security :6068

>>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>>!X-Tomcat-NG:

>microsoft.public.dotnet.framework.aspnet.securi ty

>>!
>>!Hi -- I am not sure what you are saying.
>>!
>>!InstallPersistSqlState.sql will indeed "resolve" my
>>!problem in the sense of avoiding the issue.
>>!
>>!It doesn't really answer my question, however.
>>!
>>!My question is: what is the proper way to configure SQL
>>!Server security for ASP.Net state, where the state is
>>!being stored in tempdb (InstallSqlState.sql).
>>!
>>!My suspicion is that there is no way to install this

>and

>>!configure permissions such that you can shut down and
>>!restart sql server without having to reset permissions
>>!and possibly reinstall the state database.
>>!
>>!Brian
>>!
>>!>-----Original Message-----
>>!>Hello Brian,
>>!>
>>!>I checked the InstallPersistSqlState.sql file on my
>>!side.
>>!>
>>!>CREATE PROCEDURE TempInsertStateItemLong
>>!> @id tSessionId,
>>!> @itemLong tSessionItemLong,
>>!> @timeout INT
>>!>AS
>>!> DECLARE @now as DATETIME
>>!> SET @now = GETDATE()
>>!>
>>!> INSERT ASPState..ASPStateTempSessions
>>!> (SessionId,
>>!> SessionItemLong,
>>!> Timeout,
>>!> Expires,
>>!> Locked,
>>!> LockDate,
>>!> LockCookie)
>>!> VALUES
>>!> (@id,
>>!> @itemLong,
>>!> @timeout,
>>!> DATEADD(n, @timeout, @now),
>>!> 0,
>>!> @now,
>>!> 1)
>>!>
>>!> RETURN 0
>>!>GO
>>!>
>>!>Did you download the persist state file at
>>![url]http://support.microsoft.com/?id=311209?[/url] Thanks very

>much.

>>!>
>>!>Best regards,
>>!>Yanhong Huang
>>!>Microsoft Online Partner Support
>>!>
>>!>Get Secure! - [url]www.microsoft.com/security[/url]
>>!>This posting is provided "AS IS" with no warranties,

>and

>>!confers no rights.
>>!>
>>!>--------------------
>>!>!Content-Class: urn:content-classes:message
>>!>!From: "Brian Gambs" <nobgambsspam@healthshare.com>
>>!>!Sender: "Brian Gambs" <nobgambsspam@healthshare.com>
>>!>!Subject: ASPState Bug/Security issue
>>!>!Date: Thu, 24 Jul 2003 08:09:11 -0700
>>!>!Lines: 93
>>!>!Message-ID: <0df101c351f5$8999eef0$a401280a@phx.gbl>
>>!>!MIME-Version: 1.0
>>!>!Content-Type: text/plain;
>>!>! charset="iso-8859-1"
>>!>!Content-Transfer-Encoding: 7bit
>>!>!X-Newsreader: Microsoft CDO for Windows 2000
>>!>!X-MimeOLE: Produced By Microsoft MimeOLE

>V5.50.4910.0300

>>!>!Thread-Index: AcNR9YmZoldjojzWS/m5bSgeW0QzwA==
>>!>!Newsgroups:
>>!microsoft.public.dotnet.framework.aspnet.securi ty
>>!>!Path: cpmsftngxa06.phx.gbl
>>!>!Xref: cpmsftngxa06.phx.gbl
>>!microsoft.public.dotnet.framework.aspnet.securi ty:6008
>>!>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>>!>!X-Tomcat-NG:
>>!microsoft.public.dotnet.framework.aspnet.securi ty
>>!>!
>>!>!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
>>!>![url]http://msdn.microsoft.com/library/default.asp?[/url]
>>!>!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
>>!>!
>>!>!
>>!>
>>!>
>>!>.
>>!>
>>!
>>
>>
>>.
>>

>