Sometimes you want to share session between web apps, not just between web servers.  By default you cannot do this with the SQL Server session state provider because it keeps differently named applications session's separate.  To allow this, we can modify the TempGetAppID sproc in the ASPState database.  Most solutions I've seen to this just override the sproc and allow ALL web apps using this database to share session, or they are hardcoded to look for specific app names.  Neither of these options was appealing, and worse yet the second option wasn't even viable when using the builtin Visual Studio 2005 web server. 

The solution I came up with was to use the connection string "Application Name" as the appName.  If you are familiar with SQL Profiler you will recognize this.  By default all .NET applications report they have an application name of ".NET SQLClient Data Provider" unless you have overridden it in the connection string.  This allows me to pool sessions between web applications conditionally through a configuration change, that even works on the built-in Visual Studio 2005 web server (webdev.webserver.exe).  Here's the new sproc:

    USE ASPState
GO

ALTER PROCEDURE dbo.TempGetAppID
    @appName tAppName,
    @appId int OUTPUT
AS

    -- start change

    -- Use the application name specified in the connection for the appname if specified
    -- This allows us to share session between sites just by making sure they have the
    -- the same application name in the connection string.
    DECLARE @connStrAppName nvarchar(50)
    SET @connStrAppName = APP_NAME()

    -- .NET SQLClient Data Provider is the default application name for .NET apps
    IF (@connStrAppName <> '.NET SQLClient Data Provider')
        SET @appName = @connStrAppName

    -- end change

SET @appName = LOWER(@appName)
SET @appId = NULL

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

IF @appId IS NULL BEGIN
BEGIN TRAN

SELECT @appId = AppId
FROM [ASPState].dbo.ASPStateTempApplications WITH (TABLOCKX)
WHERE AppName = @appName

IF @appId IS NULL
BEGIN
EXEC GetHashCode @appName, @appId OUTPUT

INSERT [ASPState].dbo.ASPStateTempApplications
VALUES
(@appId, @appName)

IF @@ERROR = 2627
BEGIN
DECLARE @dupApp tAppName

SELECT @dupApp = RTRIM(AppName)
FROM [ASPState].dbo.ASPStateTempApplications
WHERE AppId = @appId

RAISERROR('SQL session state fatal error: hash-code collision between applications ''%s'' and ''%s''. Please rename the 1st application to resolve the problem.',
18, 1, @appName, @dupApp)
END
END

COMMIT
END

RETURN 0
GO