Replacement of Context_Info SQL Azure

I am currently using the CONTEXT_INFO property of the Master database for storing the logged in username to use it later in Table Triggers for auditing.

While migrating to SQL Azure, the issue of Cross-Database connections popped and I couldn't find direct solutions to this issue.

Following are the Issue Details:

1. I call Stored Procedure XXX from Data Access Layer and pass the Username as Parameter
2. The username is used to set the CONTEXT_INFO value in XXX
3. The CONTEXT_INFO value is then used in Tables Insert/Update/Delete Triggers to Store Username for Application Auditing

Solutions that I found so far:

1. Create Table In Database to work as CONTEXT_INFO
2. Use 2 Connection Strings in Data Access Layer, one for Master Database (to set CONTEXT_INFO) and the other is for the application and execute the SET CONTEXT_INFO each time before opening the connection to my application

But I find both solutions risky, specially when expanding the Database over multiple SQL Azure Databases in the future.

Appreciate your support.

The approach I took is shown below. On trick was to check to see running not running on SQL Azure, then we would need to call 'SET CONTEXT_INFO ...'. This allows the same code to be execute on local SQL Server Express and Azure without changes.

1. Create a table to store the context info (not in master but in the same database)

   CREATE TABLE [dbo].[ContextInfo] (
       [ContextInfo] varbinary(128) not null,
       [ApplicationUsername] nvarchar(128) not null,
       [UpdatedAt] datetime NOT NULL,
       CONSTRAINT [PK_UserContextInfo] PRIMARY KEY CLUSTERED ([ContextInfo] ASC)
   )
   

2. Create a stored procedure to 'Set Context Info' which is called from application

   CREATE PROCEDURE [dbo].[SetContextInfo]
     @ApplicationUsername nvarchar(128)
   AS
   
   SET NOCOUNT ON
   
   -- Remove all context items older than an 5 minutes ago
   DELETE
     FROM [dbo].[ContextInfo]
    WHERE [UpdatedAt] < DATEADD(mi, -5, GETUTCDATE())
   
   --
   -- Use the MERGE command to do an update/insert
   -- See: http://technet.microsoft.com/en-us/library/bb510625.aspx
   --
   
   IF SERVERPROPERTY('edition') <> 'SQL Azure'
   BEGIN
       DECLARE @b varbinary(128)
       SET @b = CONVERT(varbinary(128),newid())
       EXEC sp_executesql @statement=N'SET CONTEXT_INFO @b',@params=N'@b varbinary(128)',@b=@b
   END
   
   DECLARE @ContextInfo varbinary(128)
   SELECT @ContextInfo = CONTEXT_INFO()
   
   MERGE [dbo].[ContextInfo] AS target
   USING (SELECT @ContextInfo, @ApplicationUsername) AS source ([ContextInfo], [ApplicationUsername])
      ON (target.[ContextInfo] = source.[ContextInfo])
    WHEN MATCHED THEN 
           UPDATE SET [ApplicationUsername] = source.[ApplicationUsername], [UpdatedAt] = GETUTCDATE()
    WHEN NOT MATCHED THEN  
           INSERT ([ContextInfo], [ApplicationUsername], [UpdatedAt])
           VALUES (source.[ContextInfo], source.[ApplicationUsername], GETUTCDATE());
   

3. Create a stored procedure to 'Get Context Info'

   CREATE PROCEDURE [dbo].[GetContextInfo]
   AS
       SET NOCOUNT ON
       DECLARE @ContextInfo varbinary(128)
       SELECT @ContextInfo = CONTEXT_INFO()
   
       SELECT [ApplicationUsername]
         FROM [dbo].[ContextInfo]
        WHERE [ContextInfo] = @ContextInfo
   GO
   

4. In trigger source, use:

   DECLARE @UserContext TABLE ([Username] VARCHAR(128))
   INSERT INTO @UserContext (Username)
   EXEC [dbo].[GetContextInfo]
   

Now you have the username stored in the table variable. In case changes are applied by an administrator outside of your application, you may also want to check if the username was not set and default to something like *SYSTEM_USER*.