MS-TDS clients and Azure Data Explorer

Article
04/27/2022
8 minutes to read

Azure Data Explorer implements TDS-compliant endpoints for MS-SQL clients. Compatibility is on the protocol level. Any library or application that can connect to the SQL Azure database with Azure Active Directory (Azure AD) authentication, will work with the Azure Data Explorer server. Therefore, you can use the server domain name like it was the SQL Azure server.

Azure Data Explorer doesn't support basic authentication with username and password. You must use Azure Active Directory with all clients listed below.

Azure Data Explorer implements a subset of the T-SQL and a subset of the SQL server emulation. For more information, see known issues for differences between the SQL Server's implementation of T-SQL and Azure Data Explorer's.

.NET SQL client

Azure Data Explorer supports Azure AD authentication for SQL clients. For more information, see .NET SQL Client (user authentication) and .NET SQL Client (application authentication)

JDBC

The Microsoft JDBC driver can be used to connect to Azure Data Explorer with Azure AD authentication.

Create an application to use one of the versions of mssql-jdbc JAR and adal4j JAR, and all their dependencies. For example,

mssql-jdbc-7.0.0.jre8.jar
adal4j-1.6.3.jar
accessors-smart-1.2.jar
activation-1.1.jar
asm-5.0.4.jar
commons-codec-1.11.jar
commons-lang3-3.5.jar
gson-2.8.0.jar
javax.mail-1.6.1.jar
jcip-annotations-1.0-1.jar
json-smart-2.3.jar
lang-tag-1.4.4.jar
nimbus-jose-jwt-6.5.jar
oauth2-oidc-sdk-5.64.4.jar
slf4j-api-1.7.21.jar

Create an application to use the JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver.

Use a connection string like the following.

jdbc:sqlserver://<cluster_name.region>.kusto.windows.net:1433;database=<database_name>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.kusto.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword

Note

If you want to use the Azure Active Directory integrated authentication mode, then replace ActiveDirectoryPassword with ActiveDirectoryIntegrated. For more information, see JDBC (user authentication) and JDBC (application authentication).

ODBC

Applications that support ODBC can connect to Azure Data Explorer.

Create an ODBC data source:

Launch the ODBC Data Source Administrator.
Select Add to create a new data source, and set ODBC Driver 17 for SQL Server.
Give the data source a name, and specify the Azure Data Explorer cluster name in the Server field. For example, mykusto.kusto.windows.net.
Set Active Directory Integrated, for the authentication option.
Select Next to set the database.
You can just leave the defaults for all the other settings in the tabs that follow.
Select Finish to open the data source summary window, where the connection can be tested.

You can now use the ODBC data source with the applications.

If the ODBC application can accept a connection string instead of, or in addition to DSN, then use the following.

"Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.windows.net;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated"

Some ODBC applications don't work well with the NVARCHAR(MAX) type. For more information, see Using Large Value Types in SQL Server Native Client.

The common workaround, is to cast the returned data to NVARCHAR(n), with some value for n. For example, NVARCHAR(4000). Such a workaround, however, won't work for Azure Data Explorer, since Azure Data Explorer has only one string type and for SQL clients it's encoded as NVARCHAR(MAX).

Azure Data Explorer offers a different workaround. You can configure Azure Data Explorer to encode all strings as NVARCHAR(n) via a connection string. The language field in the connection string can be used to specify tuning options in the format, language@OptionName1:OptionValue1,OptionName2:OptionValue2.

For example, the following connection string will instruct Azure Data Explorer to encode strings as NVARCHAR(5000). It can be used, for example, to work with the SAP Smart Data Integration, which has a default mapping for type NVARCHAR with precision > 5000.

"Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.windows.net;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated;Language=any@MaxStringSize:5000"

You can also use service principal authentication with ODBC. To do so, you must provide an Azure Active Directory tenant ID in the ODBC connection string. The tenant ID can be specified in the Language field using the following syntax:

"Driver={ODBC Driver 17 for SQL Server};Server=<adx_cluster_name>.<region_name>.kusto.windows.net;Database=<adx_database_name>;Authentication=ActiveDirectoryServicePrincipal;Language=any@MaxStringSize:4000,AadAuthority:<aad_tenant_id>;UID=<aad_application_id>;PWD=<aad_application_secret>"

You can also change the Language field in the ODBC data source (DSN). To do so, add the Language value in the DSN registry, as shown in the following example:

[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 Azure AD tenant ID for SQL clients can also be configured at the cluster level. If configured, you don't need to specify the ID on client. To change the tenant ID at the cluster level, please open a support request in the Azure portal about configuring SecuritySettings.TdsEndpointDefaultAuthority with the required tenant ID.

PowerShell

Here is an example of the PowerShell script that uses the ODBC driver.

$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()

LINQPad

A Linq application can be used with Azure Data Explorer, by connecting it like it's an SQL server. Use LINQPad to explore Linq compatibility and to browse Azure Data Explorer. It can also execute SQL queries, and is the recommended tool to explore Azure Data Explorer TDS (SQL) endpoints.

Connect like you do, to the Microsoft SQL Server. LINQPad supports Active Directory authentication.

Select Add connection.
Set Build data context automatically.
Set Default (LINQ to SQL), the LINQPad driver.
Set SQL Azure.
For the server, specify the name of the Azure Data Explorer cluster. For example, mykusto.kusto.windows.net.
Set Windows Authentication (Active Directory), for signing in.
Select Test to verify connectivity.
Select OK. The browser window displays the tree view with the databases.
You can browse through the databases, tables, and columns.
You can run SQL queries in the query window. Specify the SQL language, and select a connection to the database.
You can also run LINQ queries in the query window. For example, select a table in the browser window. Select Count, and let it run.

Azure Data Studio (1.3.4 and above)

Make a new connection.

Set the connection type to Microsoft SQL Server.
Specify the name of the Azure Data Explorer cluster as a server name. For example, mykusto.kusto.windows.net.
Set the authentication type Azure Active Directory - Universal with MFA support.
Specify the account that is provisioned in the Azure AD. For example, myname@contoso.com. Add the account the first time.
Use Database picker to select the database.
Select Connect to take you to the database dashboard and set the connection.
Select New Query to open the query window, or select the New Query task on the dashboard.

Power BI desktop

Connect like you do, to the SQL Azure Database.

Under Get Data, select More.
Set Azure, and then Azure SQL Database.
Specify the Azure Data Explorer server name. For example, mykusto.kusto.windows.net.
Select DirectQuery.
Set Microsoft account authentication (not Windows), and select sign in.
The database picker shows the available databases. Continue like you do, with a real SQL server.

Excel

Connect like you do, to the SQL Azure Database.

Select Get Data under the Data tab, then set From Azure and From Azure SQL Database.
Specify the Azure Data Explorer server name. For example, mykusto.kusto.windows.net.
Set Microsoft account authentication (not Windows), and select sign in.
The database picker shows the available databases. Continue like you do, with a real SQL server.
Once signed in, select Connect.
The database picker shows the available databases. Continue like you do, with a real SQL server.

Tableau

Create an ODBC data source. For more information, see the ODBC section.

Connect via Other Databases (ODBC).
Set the ODBC data source in DSN.
Select Connect to establish a connection.
Select Sign In, once the button is available, and sign in to Azure Data Explorer.

DBeaver (5.3.3 and above)

Configure DBeaver for handling result sets in a manner that is compatible with Azure Data Explorer.

Select Preferences in the Window menu.
Select Data Editor in the Editors section.
Make sure that Refresh data on next page reading is marked.

Create a connection to the Azure Data Explorer database.

Select New Connection in the Database menu.
Look for Azure and set Azure SQL Database. Select Next.
Specify the host. For example, mykusto.kusto.windows.net.
Specify the database. For example, mydatabase.

Warning

Don't use master as the database name. Azure Data Explorer requires a connection to a specific database.
Set Active Directory - Password for Authentication.
Specify the credentials of the active directory user. For example, myname@contoso.com, and set the corresponding password for this user.
Select Test Connection … to verify that the connection details are correct.

Microsoft SQL Server Management Studio (v18.x)

Select Connect, and then Database Engine under Object Explorer.
Specify the name of Azure Data Explorer cluster as a server name. For example, mykusto.region.kusto.windows.net.
Set Azure Active Directory - Universal with MFA for authentication and specify the username.
Select Options.
Select Browse Server under Connect to database to browse available databases.
Select Yes to continue browsing.
The window displays a tree view with all the available databases. Select one, to connect to the database.
Another possibility, is to select default under Connect to database, and then select Connect. The object Explorer will display all the databases.

Note

Browsing database objects via SSMS is not supported yet, since SSMS uses correlate subqueries to browse database schema. Correlated subqueries are not supported by Azure Data Explorer. For more information, see correlated subqueries.
Select New Query to open the query window and set your database.

You can run custom SQL queries from the query window.

MATLAB (via JDBC)

Add the required JAR-files to the front of MATLAB's static classpath by creating a "javaclasspath.txt" file in your preferences directory.

Run the following command in Matlab's command window.
```
edit(fullfile(prefdir,'javaclasspath.txt'))
```

Add the full paths to the required JAR-files.

<before>
c:\full\path\to\accessors-smart-1.2.jar
c:\full\path\to\activation-1.1.jar
c:\full\path\to\adal4j-1.6.3.jar
c:\full\path\to\asm-5.0.4.jar
c:\full\path\to\commons-codec-1.11.jar
c:\full\path\to\commons-lang3-3.5.jar
c:\full\path\to\gson-2.8.0.jar
c:\full\path\to\javax.mail-1.6.1.jar
c:\full\path\to\jcip-annotations-1.0-1.jar
c:\full\path\to\json-smart-2.3.jar
c:\full\path\to\lang-tag-1.4.4.jar
c:\full\path\to\mssql-jdbc-7.0.0.jre8.jar
c:\full\path\to\nimbus-jose-jwt-6.5.jar
c:\full\path\to\oauth2-oidc-sdk-5.64.4.jar
c:\full\path\to\slf4j-api-1.7.21.jar