 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / September 2007Session state : SQLServer permissions
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 (wawang@online.microsoft.com, 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 > Hi Andrew, > [quoted text clipped - 48 lines] > > This posting is provided "AS IS" with no warranties, and confers no rights. 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/ ====================================== >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 [quoted text clipped - 62 lines] >> >> This posting is provided "AS IS" with no warranties, and confers no rights. 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 (wawang@online.microsoft.com, 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 Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.