We have implemented Row Level Security (RLS) in Azure SQL Database for isolating the data of each Tenant following the example here https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15#MidTier

For integrating this with LLBLGen we have the following code

public DataAccessAdapterBase Get(decimal tenantId)
{
       var adp = new DataAccessAdapter();
       adp.KeepConnectionOpen = true;
       adp.ActiveRecoveryStrategy = new SqlAzureRecoveryStrategy();
       var sql = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId}, @read_only=1;";
       adp.ExecuteSQL(sql);
       return adp;
}

Is this approach secure? Is it safe to set the session this way for each adapter?

As far as I can see, this is safe from LLBLGen Pro side. Most importantly to be connected to the database by a user principal that has deny update on the TenantID column.

Thanks for the feedback Walaa. The concern you are sharing is valid. We control this through a Security Policy.

I'd use parameters and not embed values in the querystring. https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_executing_plain_sql.htm#executing-a-statement-with-executesql I know 'tenantid' is likely a number and not coming from the outside and no harm done, but doing so here, likely also means you're doing it elsewhere too. Just never embed values in the query string, ever, no exceptions. This way you won't run the risk of sql injection through the plain sql api.

Additionally, you need to close the connection and dispose the adapter manually now. Especially that last part might be overlooked in this case, so keep an eye out for that.

Thanks Otis for the feedback!