Session state : SQLServer permissions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been looking at using SQLServer to maintain session state. I'm confused
over what permissions are required.

There is an autoexec stored procedure in master called ASPState_Startup that
creates two tables in tempdb.
There is a database called ASPState that contains the stored procedures
called by web pages that have session state enabled.

Assume my connection string logs in as user StateUser...
What permissions does it need in ASPState?
What permissions does it need in tempdb?
Tempdb is recreated on system startup so does the ASPState_Startup procedure
need modifying to apply the tempdb permissions?

I looked in msdn2 but couldn't find this information.
Cheers,
Andrew
 
Hi Andrew,

Based on my understanding, you're using sqlserver to store ASP.NET session
state and you want to know which permissions are required to use related
session state databases at run time. Please feel free to correct me if I've
misunderstood anything.

First, we need to understand that those two tables created in tempdb or
ASPState are executed by a startup stored procedure in master. Since a
startup stored procedure is executed by the sqlserver service account when
the last database is recovered at startup
(http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx), the login
account that used to connect to the sqlserver doesn't need to have this
permission.

Based on whether or not you're using tempdb or ASPState to store the
session state, your login account will need read/write permission to tempdb
or ASPState; but not both.

Please refer to following document:

#ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
http://msdn2.microsoft.com/en-us/library/ms229862(vs.80).aspx
(search for "Session State Options")

t - Temporary. Session state data is stored in the SQL Server tempdb
database. Stored procedures for managing session state are installed in the
SQL Server ASPState database. Data is not persisted if you restart SQL.
This is the default.

p - Persisted. Both session state data and stored procedures are stored in
the SQL Server ASPState database.


If you use following commands to extract two sql scripts, you will find the
default one uses tempdb to create the tables; and the "p" option will use
ASPState to create the tables:

aspnet_regsql -ssadd -sstype t -S <server> -E -sqlexportonly tempdb.sql
aspnet_regsql -ssadd -sstype p -S <server> -E -sqlexportonly ASPState.sql


Hope this helps.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
I understand how to create the session state databases but the documentation
doesn't say what sql permissions the account in the web site's web.config
connection string needs. It seems wrong to give it execute rights on all
stored procedures in ASPState.

So for the connection string account in the web.config...
What permissions does the account need in ASPState?
What permissions does the account need in tempdb?
Tempdb is recreated on system startup so does the ASPState_Startup procedure
need modifying to reapply the tempdb permissions?

Thanks,
Andrew




"Walter Wang [MSFT]" said:
Hi Andrew,

Based on my understanding, you're using sqlserver to store ASP.NET session
state and you want to know which permissions are required to use related
session state databases at run time. Please feel free to correct me if I've
misunderstood anything.

First, we need to understand that those two tables created in tempdb or
ASPState are executed by a startup stored procedure in master. Since a
startup stored procedure is executed by the sqlserver service account when
the last database is recovered at startup
(http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx), the login
account that used to connect to the sqlserver doesn't need to have this
permission.

Based on whether or not you're using tempdb or ASPState to store the
session state, your login account will need read/write permission to tempdb
or ASPState; but not both.

Please refer to following document:

#ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
http://msdn2.microsoft.com/en-us/library/ms229862(vs.80).aspx
(search for "Session State Options")

t - Temporary. Session state data is stored in the SQL Server tempdb
database. Stored procedures for managing session state are installed in the
SQL Server ASPState database. Data is not persisted if you restart SQL.
This is the default.

p - Persisted. Both session state data and stored procedures are stored in
the SQL Server ASPState database.


If you use following commands to extract two sql scripts, you will find the
default one uses tempdb to create the tables; and the "p" option will use
ASPState to create the tables:

aspnet_regsql -ssadd -sstype t -S <server> -E -sqlexportonly tempdb.sql
aspnet_regsql -ssadd -sstype p -S <server> -E -sqlexportonly ASPState.sql


Hope this helps.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
 
Hi, Duke.

This MSDN article :
"Securing Your ASP.NET Application and Web Services"
http://msdn2.microsoft.com/en-us/library/aa302435.aspx#c19618429_013
....in its section on "Securing a SQL Server Session State Store",
suggests this :

If you use a SQL Server session state store,
use the following recommendations to help secure the session state:

a. Use Windows authentication to the database
b. Encrypt sqlConnectionString
c. Limit the application's login in the database
d. Secure the channel

There's specific instructions on how to implement each of those recommendations in the article.

The last recommendation will only apply if the session state database
is on a different server than the IIS server and, even then, probably should
only be done if your SQL Server is "in the wild".

If it's in your internal network, you can probably skip that step,
although it won't hurt you to implement it regardless of where
your SQL Server is, if it's in a different box that your web server.




Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
======================================
Duke (AN247) said:
I understand how to create the session state databases but the documentation
doesn't say what sql permissions the account in the web site's web.config
connection string needs. It seems wrong to give it execute rights on all
stored procedures in ASPState.

So for the connection string account in the web.config...
What permissions does the account need in ASPState?
What permissions does the account need in tempdb?
Tempdb is recreated on system startup so does the ASPState_Startup procedure
need modifying to reapply the tempdb permissions?

Thanks,
Andrew




"Walter Wang [MSFT]" said:
Hi Andrew,

Based on my understanding, you're using sqlserver to store ASP.NET session
state and you want to know which permissions are required to use related
session state databases at run time. Please feel free to correct me if I've
misunderstood anything.

First, we need to understand that those two tables created in tempdb or
ASPState are executed by a startup stored procedure in master. Since a
startup stored procedure is executed by the sqlserver service account when
the last database is recovered at startup
(http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx), the login
account that used to connect to the sqlserver doesn't need to have this
permission.

Based on whether or not you're using tempdb or ASPState to store the
session state, your login account will need read/write permission to tempdb
or ASPState; but not both.

Please refer to following document:

#ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
http://msdn2.microsoft.com/en-us/library/ms229862(vs.80).aspx
(search for "Session State Options")

t - Temporary. Session state data is stored in the SQL Server tempdb
database. Stored procedures for managing session state are installed in the
SQL Server ASPState database. Data is not persisted if you restart SQL.
This is the default.

p - Persisted. Both session state data and stored procedures are stored in
the SQL Server ASPState database.


If you use following commands to extract two sql scripts, you will find the
default one uses tempdb to create the tables; and the "p" option will use
ASPState to create the tables:

aspnet_regsql -ssadd -sstype t -S <server> -E -sqlexportonly tempdb.sql
aspnet_regsql -ssadd -sstype p -S <server> -E -sqlexportonly ASPState.sql


Hope this helps.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
Click to expand...
 
Thanks Juan for your informative input.

Hi Duke,

If you're using the default ASPState database, the stored procedures in
ASPState database are all supposed to be used by session state handling.
Therefore I think it's necessary for the login account that you've
configured to use the session state database to have executive permission
on those stored procedures.

For the tables part, as I pointed in my last reply, depending on you're
using the tempdb or ASPState database, you will also need to make sure the
login account has read/write permission on the tables.

Please feel free to let us know if you have anything else unclear. Thanks.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thank you both for your answers. I'll try setting it up as described in the
document.

Cheers,
Andrew
 
Back
Top