Synapse Serverless SQL instance in managed application cannot access storage account. AADSTS700016

Question

I have a managed application that deploys (amongst other resources) a storage account with blob storage and 2 containers, and a synapse studio workspace.

I have a synapse serverless SQL instance with a single DB. This has an external datasource that connects to blob containers in the storage account. I create this using the following SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxx'
GO

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity'
GO
    
CREATE EXTERNAL DATA SOURCE [DatasourceName] WITH 
(
  LOCATION = N'https://storageaccountname.dfs.core.windows.net/container-name/', 
  CREDENTIAL = [WorkspaceIdentity]
)
GO

I then have some external tables that use the data source, created like this:

CREATE EXTERNAL TABLE [dbo].[Example]
(
    [ID] [int]
)
WITH (DATA_SOURCE = [DatasourceName],LOCATION = N'Example/*',FILE_FORMAT = [parquet])
GO


SELECT * FROM Example

This works when I deploy all resources to our own subscription, however when I deploy all resources as a managed application into the customer's subscription, everything deploys correctly, however when I run the 'SELECT' statement from the table I receive the following error:

*Cannot obtain AAD token to access storage. Error message: AADSTS700016: Application with identifier '[Application ID]' *was not found in the directory 'PUBLISHER DIRECTORY'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant.*

In Scenario 1 (as expected): Synapse Workspace (SQL), Storage account and containers are all in the same resource group within my (the publisher) tenant. I run the SQL Command and the query returns rows as expected.

In Scenario 2 (throwing error): Synapse Workspace (SQL), storage account and containers are all deployed together as a managed application into a customer tenant. All the resources are in the same managed resource group on the customer tenant, there is no cross-tenant behaviour desired between the resources. (except that it is a managed app so I will be managing it from my tenant (The publisher)).

I have deployed (from the publisher tenant, into the customer tenant) a role assignment which grants the role of Storage Blob Data Contributor to the system assigned managed identity of the Synapse Workspace at the scope of the storage account containers. I used the delegatedManagedIdentityResourceId to ensure I get the identity from the customer's tenant, and not the publisher's. Here is the Bicep I use (which I found in this SO Answer)

//determine if we are in another tenant, eg. application deployment. 
var crossTenant = tenant().tenantId != subscription().tenantId 


resource roleAssignmentTsPerformanceDataContiner 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  scope: synapseTsPerformanceDataContainer
  name: guid(resourceGroup().id, synapseWorkspacePrincipleID, storageBlobDataContributorRoleID, synapseTsPerformanceDataContainer.id)
  properties: {
    roleDefinitionId: storageBlobDataContributorRole.id
    principalId: synapseWorkspacePrincipleID
    principalType: 'ServicePrincipal'
    delegatedManagedIdentityResourceId: crossTenant ? synapseWorkspaceIdentity : null  
  }
} 

I can see the role assignment in the storage account's IAM, with the *name *of the synapse workspace so it seems like that is there. But when I check the *ID *on the role assignment, it does not match the managed identity ID shown on the customer synapse workspace.

From trying to understand the error message, it seems that the SQL Serverless instance is trying to get the AAD token from the PUBLISHER's tenant, rather than the customer's tenant. The managed identity and the storage account exist in the customer tenant, so obviously it cannot be found. It also says it can happen when not installed by the administrator, which I suppose it is not, as it is installed as part of a managed application.

However what I don't know is how to ensure that it tries to authenticate against the customer tenant, or if I'm just missing something else entirely.