Sharing Session in IIS

Forget about why I had to do this and just accept that it was necessary.  I needed to share session between two completely separate applications in IIS.  In my particular setup, one application was a "child" of the other.

After quite a bit of searching and trial and error, I found a pretty simple solution that worked perfectly in my scenario.  This doesn't mean it'll work for everyone, but it definitely did for me.

We had previously configured session to use a SQL database, which I've found to be a pretty common scenario.  In each connection string, I added an ApplicationName piece and specified the same application name in both applications (e.g. ApplicationName = 'WhateverApp').

The next part made me feel a little icky, but it worked.  In the SQL Server ASPState database I modified the TempGetAppID stored procedure to

use [ASPState]
GO
SET ansi_nulls ON
GO
SET quoted_identifier OFF
GO

ALTER PROCEDURE [dbo].[TempGetAppID] 
(
     @appName tAppName
    ,@appId INT OUTPUT
)
AS
BEGIN
    -- 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 @connectionStringApplicationName NVARCHAR(50)
    SET @connectionStringApplicationName = App_name()

    IF @connectionStringApplicationName = 'WhateverApp'
    BEGIN
        SET @appName = @connectionStringApplicationName
    END

    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
END 

This relatively small change allows us to detect whether an application name was passed in the connection string.  If it was (which it would be in both of our applications) then we set the @appName variable to be the name that was passed in the connection string instead of whatever it was going to use originally (for sub-applications, this can look like /lm/w3svc/1/root).  Since both connection strings pass the same application name, they're now sharing session.