Forum Discussion

JasonWorlin's avatar
JasonWorlin
Copper Contributor
Feb 18, 2025

External Table in ADX

Hi,

I'm trying to create an external table in ADX which uses a Synapse Analytics (SA) database view (called undelivered). The undelivered view itself is query data from a Cosmos analytical store

  • I've create a user defined idenity
    • Added the identiy to the ADX cluster, SA and Cosmos
  • Updated the ADX database:
    .alter-merge cluster policy managed_identity[
        {
          "ObjectId": "a3d7ddcd-d625-4715-be6f-c099c56e1567",
          "AllowedUsages": "ExternalTable"
        }
    ]
  • Created the database users in SA

    -- Create a database user for the ADX Managed Identity
    CREATE USER [adx-synapse-identity] FROM EXTERNAL PROVIDER;

    -- Grant read permissions
    ALTER ROLE db_datareader ADD MEMBER [adx-synapse-identity];
    GRANT SELECT ON OBJECT::undelivered TO [adx-synapse-identity];

  • From within SA I can "SELECT * FROM undelivered" and the correct information is returned
  • But when I come to create the external table in ADX:
    .create-or-alter external table MyExternalTable
    (
        Status: string
    )
    kind=sql 
    table=undelivered
    (
        h@'Server=tcp:synapse-xxxxx.sql.azuresynapse.net,1433;Database="Registration";ManagedIdentityClientId=<key>;Authentication=Active Directory Managed Identity;'
    )
    with (
        managed_identity = "<key>"
    )
    I get the error: Managed Identity 'system' is not allowed by the managed_identity policy for usage: ExternalTable

So even with me specifying the managed identity I want to use it is still trying to use the system one. 

How can I get the external table created with the correct managed identity?
Any questions please just ask
Thanks

 

No RepliesBe the first to reply

Resources