title | description | ms.reviewer | ms.topic | ms.date |
---|---|---|---|---|
Connect to Azure Data Explorer with ODBC |
In this article, you learn how to set up an Open Database Connectivity (ODBC) connection to Azure Data Explorer. |
gabil |
how-to |
11/16/2023 |
Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. Azure Data Explorer is compatible with a subset of the SQL Server communication protocol (MS-TDS). This compatibility enables the use of the ODBC driver for SQL Server with Azure Data Explorer.
Consequently, you can establish a connection to Azure Data Explorer from any application that is equipped with support for the ODBC driver for SQL Server.
Watch the following video to learn to create an ODBC connection.
Alternatively, follow the steps to configure the ODBC data source.
Note
We recommend using dedicated connectors for Azure Data Explorer whenever possible. For a list of available connectors, see Connectors overview.
- Microsoft ODBC Driver for SQL Server version 17.2.0.1 or later for your operating system.
To configure an ODBC data source using the ODBC driver for SQL Server:
-
In Windows, search for ODBC Data Sources, and open the ODBC Data Sources desktop app.
-
Select Add.
-
Select ODBC Driver 17 for SQL Server then Finish.
-
Enter a name and description for the connection and the cluster you want to connect to, then select Next. The cluster URL should be in the form <ClusterName>.<Region>.kusto.windows.net.
[!NOTE] When entering the cluster URL, do not include the prefix "https://".
-
Select Active Directory Integrated then Next.
-
Select the database with the sample data then Next.
-
On the next screen, leave all options as defaults then select Finish.
-
Select Test Data Source.
-
Verify that the test succeeded then select OK. If the test didn't succeed, check the values that you specified in previous steps, and ensure you have sufficient permissions to connect to the cluster.
From an application that supports ODBC connection, you can connect to Azure Data Explorer with a connection string of the following format:
"Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.windows.net;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated"
Note
Azure Data Explorer considers string values as NVARCHAR(MAX)
, which may not work well with some ODBC applications. Cast the data to NVARCHAR(
n)
using the Language
parameter in the connection string. For example, Language=any@MaxStringSize:5000
will encode strings as NVARCHAR(5000)
. For more information, see tuning options.
To use application principal authentication with ODBC, you must provide the Microsoft Entra tenant ID. You can set this configuration in the connection string, the Windows registry, or the odbc.ini file. See examples in the following tabs. For more information, see tuning options.
Set the application principal with Language=any@AadAuthority:<aad_tenant_id>
in the connection string. Replace <aad_tenant_id>
, <aad_application_id>
, and <aad_application_secret>
with the Microsoft Entra tenant ID, Microsoft Entra application ID, and the Microsoft Entra application secret respectively.
"Driver={ODBC Driver 17 for SQL Server};Server=<adx_cluster_name>.<region_name>.kusto.windows.net;Database=<adx_database_name>;Authentication=ActiveDirectoryServicePrincipal;Language=any@AadAuthority:<aad_tenant_id>;UID=<aad_application_id>;PWD=<aad_application_secret>"
Edit the Language
field in the ODBC data source (DSN) in the registry for Windows as follows.
[HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\MyUserDSN]
"Language"="any@AadAuthority:<aad_tenant_id>"
For Linux and macOS, edit the odbc.ini file, as follows.
# [DSN name]
[MSSQLTest]
Driver = ODBC Driver 17 for SQL Server
# Server = [protocol:]server[,port]
Server = tcp:<adx_cluster_name>.<region_name>.kusto.windows.net,1433
Language = any@AadAuthority:<aad_tenant_id>
The following example shows how to connect to Azure Data Explorer using an ODBC driver in PowerShell. For this to work, you must first follow the steps in Configure the ODBC data source.
$conn = [System.Data.Common.DbProviderFactories]::GetFactory("System.Data.Odbc").CreateConnection()
$conn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.windows.net;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated"
$conn.Open()
$conn.GetSchema("Tables")
$conn.Close()