Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MatthiasA
Frequent Visitor

SQL External table in KQL database using user assigned managed identity

Hello,

 

We would like to create an external table within the KQL database to an azure SQL database containing metadata values.

Ideally we would like to use system assigned managed identities, but although we have a P1 capacity this feature is not enabled (you need a fabric capacity).

The idea was to create an application registration within Azure Entra ID, assign it the proper permissions to the Azure SQL and use the credentials of the application registration in the connection string (link to connection string with service principal authentication type) when creating the external table inside KQL database.

However, when querying the external table we do get an error message stating "login failed for login". Using the same connection string in a simple C# program succeeds in retrieving the data from the SQL (if you would use the Microsoft.Data.SqlClient nuget package instead of the System.Data.SqlClient one). I am assuming it is thus (currently) impossible to create an external table using a user assigned managed identity. Is there another good way to create these external tables in a secure way or is username/password (or managed identity when using F64 and up) the only way?

1 REPLY 1
DallasBaba
Super User
Super User

@MatthiasA Can you set up a user-assigned managed identity for your Azure Data Explorer cluster, and allow the managed identity read and write permissions to the external resource. Then, use the managed identity’s credentials in the connection string for your external table.

 

You can also run this command to set a managed identity policy on the cluster, enabling its use with external tables.

 

 

.alter-merge policy managed_identity

 

Kindy see @Jose Manuel Jurado Diaz blogpost and learn microsoft

 

 If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

Thanks
Dallas

Helpful resources

Announcements
FabricCarousel_June2024

Fabric Monthly Update - June 2024

Check out the June 2024 Fabric update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors