SQL External table in KQL database using user assigned managed identity

MatthiasA — Wed, 29 May 2024 11:18:18 GMT

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?

Re: SQL External table in KQL database using user assigned managed identity

DallasBaba — Tue, 04 Jun 2024 17:03:49 GMT

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.

topic Re: SQL External table in KQL database using user assigned managed identity in Eventhouse and KQL

SQL External table in KQL database using user assigned managed identity

Re: SQL External table in KQL database using user assigned managed identity