Hi,

We use CONTEXT_INFO in an application we currently are looking to move to SQL Azure however CONTEXT_INFO is already used to troubleshooting purposes by SQL Azure. Are there any built in alternatives in SQL Azure?

Thanks,

• Moved by Brian AurichMicrosoft employee Wednesday, September 29, 2010 12:08 AM migration (From:SQL Azure)
• Moved by Brad Calder Saturday, March 26, 2011 5:47 PM SQL question (From:Windows Azure Storage)

Hi There, could you explain how you use the field? there isn't an obvious workaround to this but there may be other fields you can use in the connection properties. There isn't an official workaround to this but there may be stop gap solutions to solve the issue.

thanks

-cihangir

Hi,

We use it to enable security to row level data. As we use a single connection string as part of a trusted subsystem design, it stores the userId of the requestor and then data is filtered based on that.

So when a request is made to the database, CONTEXT_INFO is always set first then the request for data. Make sense?

Thanks,

Ross

 

 

• Marked as answer by rossco_1 Saturday, September 04, 2010 12:33 PM
• Unmarked as answer by rossco_1 Monday, September 06, 2010 8:38 AM

We're doing something very similar. Any suggestions on this?

We are having the same problem. We use CONTEXT_INFO to store the tenant ID in a multi-tenant scenario. Any news?

Hmm, still no answer since August of 2010?  I hope MS has something for us that use this.  I'll give an example I use CONTEXT_INFO in an applicaiton.

I use CONTEXT_INFO to prevent users from modifying a table using SQL Studio.  I do this by setting a CONTEXT_INFO in my c#/vb code like so after a begin transaction, along with other TSQL commands, I have this little guy.

    DECLARE @x varbinary(4); SET @x = cast(-1 as varbinary(4)); SET CONTEXT_INFO @x

  update dbo.CusCharges set PaymentAmount=5 where CusChargeAmount=10 and CustomerId=30

So then in a particular table, in the trigger (after update, insert) I have this piece of code (shown below)
This will allow my application to update the table, but casual TSQL users which don't know about this won't be able to perform an update to this table directly.
This works great, except in Azure-land where sysprocesses is not available. So how can I pass down a special variable to a trigger in the context of a transaction?
If course, someone could still use TSQL studio, but they would really need to examine the trigger and know what they are doing. This just prevents the
beginning DBA from wiping out Customer Charges for the past decade in a table (it's happened, which is why I put it in).
  declare @SessionNid int
    select @SessionNid = cast(substring(CONTEXT_INFO,1,4) as int) FROM master.dbo.sysprocesses WHERE spid = @@SPID
    if @SessionNid = 0 begin
        raiserror('You cannot directly modify dbo.CusCharges.', 16, 1)
        rollback transaction
        return
    end

We're doing something almost identical. The only work around I've found is to sub in code that creates a temp table where yu would otherwise be setting the context_info that holds this information. When then use a stored procedure to pull values out of the temp table in subsequent calls and in triggers and stuff where we need to know the user information. I haven't come up with any alternative solution, but this one just feels wrong. Any suggestions would be appreciated.

Hi Microsoft - any updates on this? My team is running into similar issues with our SQL Azure upgrade. In SQL 2008, we were storing a customer ID in CONTEXT_INFO and this value was in turn used by all the SQL views to only provide contents for that specific customer Id (this is done as per a security requirement). Wondering if there is an alternate to this in Azure.

Wondering if there are any variable or storage similar to CONTEXT_INFO in SQL Azure that would only exist for the current connection and does not allow concurrent access?

Hi there,

any News on this Topic?

We are using CONTEXT_INFO to store the caller stack (PROCIDs) of Procedures and Triggers. Because some Triggers have to behave differently when called from certain stored procedures.

I don't know the Performance Impact when we switch over to a temporary table as proposed above.

It would be really nice if SQL Server let access the caller stack out of the box @@PROCID(<nestlevel>)

or some sort of connection local variables that can be set up per database.

best regards

Martin

Hi 

Same applies for us. We use CONTEXT_INFO heavily in triggers to control execution. Recursion and custom replication are the main fields. The main idea is to prevent part of trigger's code execution under certain conditions, based on client.

Any news would be great.

Thanks George

I suppose/hope that this is no longer an issue on sql azure v12??

The built in row-level-security feature for a multi-tenant app is almost impossible to implement without using context_info 

Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security

Yes, CONTEXT_INFO is available for use in the latest Azure SQL Database Update (V12). So this is no longer an issue, and of course it can be used as described in the Row-Level Security post.

• Proposed as answer by Tommy MullaneyMicrosoft employee 14 hours 28 minutes ago
• Marked as answer by Casey KarstMicrosoft employee, Moderator 14 hours 28 minutes ago

Yes, CONTEXT_INFO is available for use in the latest Azure SQL Database Update (V12). So this is no longer an issue, and of course it can be used as described in the Row-Level Security post.

• Proposed as answer by Tommy MullaneyMicrosoft employee 14 hours 19 minutes ago
• Marked as answer by Casey KarstMicrosoft employee, Moderator 14 hours 18 minutes ago

Yes, CONTEXT_INFO is available for use in the latest Azure SQL Database Update (V12). So this is no longer an issue, and of course it can be used as described in the Row-Level Security post.

• Proposed as answer by Tommy MullaneyMicrosoft employee Monday, March 16, 2015 4:46 PM
• Marked as answer by Casey KarstMicrosoft employee, Moderator Monday, March 16, 2015 4:47 PM