MSSQL backend does not use correct task hub

[nickwilliams-codynamic]

When using durable functions monitor with the MSSQL backend in the default multi-tenant configuration, the monitor seems to discover the task hubs correctly, but does not use them when querying instances.

I have a SQL server deployment with 3 task hubs, the task hubs are correctly identified:

Regardless of which task hub is selected, the dbo task hub contents is shown in the list

DBO

DIO

Nicktest

---

I can see the data is available by querying the table directly.

---

If I try and navigate to an instance directly using the id from the database, I get a not found error.

---

I'm using the docker container to run functions monitor with this command:

docker run --rm -p 8072:80 \
    -e DFM_SQL_CONNECTION_STRING='<REDACTED>' \
    -e DFM_NONCE="i_sure_know_what_i_am_doing" \
    scaletone/durablefunctionsmonitor.mssql:6.6

[nickwilliams-codynamic]

When running durable functions monitor under the same database user as the consuming applicationm, which only has the dt_runtime role, the listing of task hubs doesn't work - which is expected. However, manually navigating to the task hub for the user seems to work.

Failure to list task hubs (expected):

Manually navigating to /{taskhub}

[scale-tone]

I can see the data is available by querying the table directly.

@nickwilliams-codynamic , what's the full name of that table? I'm mostly interested in the schema name part of it.

[nickwilliams-codynamic]

I was using this query. So the default dt schema.

SELECT * FROM dt.Instances

I have been doing a bit more investigation and in hind sight I think this is a setup that durable functions monitor can't support, without changes made to the MSSQL backend.

The monitor works perfectly if I disable shared schema mode. It just means each function app must be trusted to specify its taskhub name correctly.

I have raised a feature request in the MSSQL backend to allow support for monitoring use cases, which I think would work well for durable functions monitor. Please feel free to add your thoughts / point out where I may be incorrect.

I think the fundamental problem is that "Shared Schema Mode" restricts access to a single task hub using the SQL username - which is good. But there is no ability at the moment to grant a specific user access to view all task hubs.

[rncwnd79]

@nickwilliams-codynamic see here: https://github.com/microsoft/DurableFunctionsMonitor/discussions/208 Up to v6.6 I got it working with distinct users for each task hub and the DFM_ALTERNATIVE_CONNECTION_STRING_* variables.