Forum Discussion
JasonWorlin
Feb 18, 2025Copper Contributor
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