minimum permissions required for access to a SQL Server session state server

[Mark Kamoski]

Hi--

What are the minimum permissions required for the account used to access a
SQL Server session state server?

The situation is this. I have an understanding and VERY generous hosting
provider who will let me use SQL Server session state.

They have run the SQL Server session state server setup script and all is
well.

Now, I need to know the minimum permissions required for access to this
server so that when I set this in my web.config file, I can make sure that
I am going to be able to get it to work.

They will want to set up a "minimum permissions" account for this, for
security reasons.

So, that's the situation.

Any thoughts, hints, or recommendations would be greatly appreciated.

Thank you.

--Mark

 

[Carl Prothman [MVP]]

When your ISP runs "InstallSqlState.sql", it will create a new database called ASPState.
That database will contain stored procedures and tables. So to secure it, you would
want to only give Execute permissions to the stored procedures for the User account
specified in the web.config's sessionState's sqlConnectionString. The User account
should not have any direct access to the tables
http://msdn.microsoft.com/library/en-us/cpgenref/html/gngrfsessionstatesection.asp

Sorry, there are no user tables in the ASPState database. The user tables are created
in the Temp db.

If your ISP has installed SQL Server 2000 **SP3** and left the cross-database
ownership chaining off (it's off by default), then you'll get the following error messages:

SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
INSERT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.

In this case, your ISP will need to enable cross-database ownership chaining.


Here is what the SP3 readme says:
*******
Note Enabling cross-database ownership chaining for all databases is not
recommended.

After installation, you can use the following methods to turn cross-database
ownership chaining on and off for all databases in the instance:

a.. Use the new Cross DB Ownership Chaining argument of the sp_configure
system stored procedure.

b.. Use the Allow Cross-Database Ownership Chaining option on the Security
tab of the SQL Server Properties dialog box in Enterprise Manager.

c.. Use the SQLServer.Configuration.ConfigValues Database Management
Objects (DMO) collection. When the SQLServer object references an instance
of SQL Server 2000 SP3 or later, this collection contains a ConfigValue
object named Cross DB Ownership Chaining.

If cross-database ownership chaining is turned off for the instance, you can
configure it for individual databases. Use the following methods to turn
cross-database ownership chaining on and off for a database:

a.. Use the new db chaining option of the sp_dboption system stored
procedure.

b.. Use the Allow Cross-Database Ownership Chaining option on the Options
tab of the Database Properties dialog box in Enterprise Manager.

c.. Use the DBChaining property of the DBOption2 DMO object.

Note If you previously enabled cross-database ownership chaining on a
pre-release version of SP3 (earlier than build 8.00.[BUILD_NUMBER]), you
must enable it again after installing the release version of SP3.

For more information, click the Help button on the Backwards Compatibility
Checklist page when you run Setup, download the updated edition of SQL
Server 2000 Books Online, or see Knowledge Base article 810474.
*******

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Mark Kamoski]

Carl--

Thank you very much for the responses.

(I'm glad SOMEBODY knows what they are talking about when it comes to this
stuff.)

I understand your posts.

However, to simplify, how aobut just doing the following.

Ask the web host to create a new user and assign that new user execute
rights to all SPs in the ASPState database.

Then, I just reference that user in my web.config file something like
this...

<sessionState mode="SQLServer" sqlConnectionString="data
source=NameOfDbWithStateInstalled;user
id=StateUser;password=SomePasswordValue" cookieless="true" timeout="20" />

....and then move on from there?

(I'd rather not have to ask the web host to do a lot of specialized
configuration because that may make them hesitate.)

What do you think?

Please advise.

--Mark

When your ISP runs "InstallSqlState.sql", it will create a new database called ASPState.
That database will contain stored procedures and tables. So to secure it, you would
want to only give Execute permissions to the stored procedures for the User account
specified in the web.config's sessionState's sqlConnectionString. The User account
should not have any direct access to the tables
http://msdn.microsoft.com/library/en-us/cpgenref/html/gngrfsessionstatesection.asp

Sorry, there are no user tables in the ASPState database. The user tables
are created
in the Temp db.

If your ISP has installed SQL Server 2000 **SP3** and left the
cross-database
ownership chaining off (it's off by default), then you'll get the following
error messages:

SELECT permission denied on object 'ASPStateTempApplications', database
'tempdb', owner 'dbo'.
INSERT permission denied on object 'ASPStateTempApplications', database
'tempdb', owner 'dbo'.
SELECT permission denied on object 'ASPStateTempApplications', database
'tempdb', owner 'dbo'.

In this case, your ISP will need to enable cross-database ownership
chaining.


Here is what the SP3 readme says:
*******
Note Enabling cross-database ownership chaining for all databases is not
recommended.

After installation, you can use the following methods to turn
cross-database
ownership chaining on and off for all databases in the instance:

a.. Use the new Cross DB Ownership Chaining argument of the sp_configure
system stored procedure.

b.. Use the Allow Cross-Database Ownership Chaining option on the
Security
tab of the SQL Server Properties dialog box in Enterprise Manager.

c.. Use the SQLServer.Configuration.ConfigValues Database Management
Objects (DMO) collection. When the SQLServer object references an instance
of SQL Server 2000 SP3 or later, this collection contains a ConfigValue
object named Cross DB Ownership Chaining.

If cross-database ownership chaining is turned off for the instance, you
can
configure it for individual databases. Use the following methods to turn
cross-database ownership chaining on and off for a database:

a.. Use the new db chaining option of the sp_dboption system stored
procedure.

b.. Use the Allow Cross-Database Ownership Chaining option on the Options
tab of the Database Properties dialog box in Enterprise Manager.

c.. Use the DBChaining property of the DBOption2 DMO object.

Note If you previously enabled cross-database ownership chaining on a
pre-release version of SP3 (earlier than build 8.00.[BUILD_NUMBER]), you
must enable it again after installing the release version of SP3.

For more information, click the Help button on the Backwards Compatibility
Checklist page when you run Setup, download the updated edition of SQL
Server 2000 Books Online, or see Knowledge Base article 810474.
*******

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Carl Prothman [MVP]]

I understand your posts.
However, to simplify, how aobut just doing the following.
Ask the web host to create a new user and assign that new user execute
rights to all SPs in the ASPState database.
Then, I just reference that user in my web.config file something like
this...
<sessionState mode="SQLServer" sqlConnectionString="data
source=NameOfDbWithStateInstalled;user
id=StateUser;password=SomePasswordValue" cookieless="true" timeout="20" />

Mark,
Yes, but your connection string does not look correct. Try this:
sqlConnectionString="data source=(local);user id=User1;password=myPassword"

Note if the SQL Server is not located on the Web Server, then change "(local)" to the
name (or IP address) of where the SQL Server is located. The sessionState feature
knows to look in the ASPState database.

And if the ISP has installed SQL Server 2000 **SP3** then they will need enable
cross-database ownership chaining for the ASPState database to the Temp database.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Diane]

Mark,
To Configuring Sql Server, you could go through following steps:
1. Run InstallSqlState.sql in Query Analyzer to build ASPState database
2. Modified your web.config file
3. Make sure that SQLServerAgent is running

Diane Wang
Sr Software Engineer, MCAD

 

[Carl Prothman [MVP]]

The only other thing you need to do is make sure the SQL Server User account

has Public, db_datareader, and db_datawriter permissions on the tempdb database.

Hmmm, that works until you restart SQL Server, then the User permissions
go away on tempdb. Arg!

The only solution for this problem is to make sure the ASPState database
is owned by sa (in addition to the other settings)

use ASPState
exec sp_changedbowner 'sa','true'

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Carl Prothman [MVP]]

The OWNER of the ASPState database MUST be sa.

However, I am assuming that one can still setup users that are allowed to
connect to this database. That is, I am assuming that one does NOT need to
user the sa user in their sessionState node of "web.config"-- rather one
can set up another user for this.

Is my assumption correct?

Correct.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[Mark Kamoski]

Carl--

Thanks a million for all of your help with this.

I appreciate it a lot.

Thanks again.

--Mark

The OWNER of the ASPState database MUST be sa.

However, I am assuming that one can still setup users that are allowed to
connect to this database. That is, I am assuming that one does NOT need to
user the sa user in their sessionState node of "web.config"-- rather one
can set up another user for this.

Is my assumption correct?

Correct.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com