[Bug]: MSSQL (and related) - EXEC sp_set_session_context is executed with @read_only = 1

[M4Al]

What happened?

Running on Analytics SQL Endpoint on Fabric

I was trying a row-level-security scenario, based on the one in the documentation.

So I did: "data-source": { "database-type": "DWSQL", "connection-string": "Data Source=XXXXXXXl", "options": { "set-session-context": true } },

And then configured JWT AzureAD authentication with a custom role. This all works fine.

When I do a query now, just prior to executing the query, in the `src\Core\Resolvers\MsSqlQueryExecutor.cs:222we see the following:string statementToSetReadOnlyParam = "EXEC sp_set_session_context " + $"'{claimType}', " + paramName + ", @read_only = 1;";`

This is executed on every query execution from the frontend:

string sessionParamsQuery = GetSessionParamsQuery(httpContext, parameters, dataSourceName); cmd.CommandText = sessionParamsQuery + sqltext;

I see two issues:

• This second request fails with Cannot set key 'roles' in the session context. The key has been set as read_only for this session. from the database, as the context is created with read_only=1
• If a second request comes in, from a different user it will re-use the existing connection, leading to possible race conditions if multiple users are querying since the connection is shared between all users.

The second issue could be my misunderstading of how the backend SQL engine handles these cases.

Version

1.2.10

What database are you using?

Azure SQL

What hosting model are you using?

Container Apps

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct