ASP.NET SessionState with allowCustomSqlDatabase="true" doesn't call specified database

Question

The Microsoft documentation here

http://msdn.microsoft.com/en-us/library/h6bb9cz9(v=vs.90).aspx

and several SO articles indicate that specifying allowCustomSqlDatabase="true" in the sessionState declaration of an ASP.NET web.config file will allow me to override using the default ASPState database and specify my own database name.

I set these properties in my web.config file.

<sessionState allowCustomSqlDatabase="true" mode="SQLServer" sqlConnectionString="Data Source=mysqlservername;Initial Catalog=DR_ASPState;Integrated Security=true;Connect Timeout=15;" />

When I attempt to bring up the default website page, I get an error message indicating the ASPState database cannot be accessed.

I have monitored the SQL traffic using SQL Profiler and notice that the failure is caused by this request:

SELECT @appId = AppId
FROM [ASPState].dbo.ASPStateTempApplications
WHERE AppName = @appName

I have recycled the application pools and restarted IIS in an attempt to flush whatever cached database name reference might exist but that does not help. My understanding is that the ASP.NET SessionState would use the database name which I have specified, DR_ASPState, to generate the SQL statements which it uses to connect to the SessionState database. But it sure seems like [ASPState] is hard-coded into the framework.

Can anybody spot anything which I have done wrong or provide useful troubleshooting information on how to resolve this?

Answer 1

I have uncovered the issue. Our IT team had created the custom ASP session state database, DR_ASPState, by restoring a backup of our existing ASPState database. However, just providing a different name does not change stored procedures which were generated with hard-coded database references.

The website is calling the correct alternate database DR_ASPState as defined in my connection string. However, at least one of the stored procedures generated by the .NET command has hard-coded database names in the stored procedure statements.

Using SQL Profiler, I identified that this is the statement which was causing problems by using a renamed database

declare @p2 int
set @p2=NULL
exec dbo.TempGetAppID @appName='/LM/W3SVC/10/ROOT',@appId=@p2 output
select @p2

Examining the stored procedure dbo.TempGetAppID shows statements such as these:

SELECT @appId = AppId
FROM [ASPState].dbo.ASPStateTempApplications
WHERE AppName = @appName

This would be properly resolved by installing DR_ASPState properly as a new database per the instructions in the .NET documentation here.

http://msdn.microsoft.com/en-us/library/ms229862(v=vs.80).aspx

This statement should be executed from our web server.

aspnet_regsql.exe -S *servername* -E -ssadd -sstype c –d DR_ASPState