ADX/KQL through SQL linked server

Question

Hello,

Recently we have created a linked server in SQL Server and connection operates well.

This link server targets an ADX cluster, and a named database.Nevertheless, I admit that I am quite familiar with SQL language, but I never been able to retrieve a single data from ADX through this linked server.

Either using T-SQL syntax or KQL query.Has someone has already implemented such mecanism and could help me with tips / guidance ?

Thanks a lot for your time and expertise.

Answer 1

Hello David Chatel,

To retrieve data from ADX through the linked server, you can use the OPENQUERY function in T-SQL against the ADX cluster.

1. Set up the linked server using an ODBC connection.
2. Query ADX data using OpenQuery

SELECT * FROM OpenQuery(LINKEDSERVER, 'SELECT * from MyFunction(10)')

SQL Server has a limitation where it can't use remote tabular functions from linked servers directly in its own queries. To overcome this limitation, use the OpenQuery function to run a query on the linked server. You can then use the outer T-SQL query to combine the data from the SQL server with the data returned from the Azure Data Explorer stored function.

Reference document:

https://learn.microsoft.com/en-us/azure/data-explorer/linked-server

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Answer 2

Hi @David Chatel

Please refer to this doc for more details about how to run stored functions and KQL queries from an SQL compatible client.

In order to authenticate to Azure Data Explorer, the SQL Server must use a Microsoft Entra account. Check this doc for more details: Linked server for SQL Server with Microsoft Entra authentication

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".