sql_request plugin

Applies to: ✅ Azure Data Explorer

The sql_request plugin sends a SQL query to an Azure SQL Server network endpoint and returns the results. If more than one rowset is returned by SQL, only the first one is used. The plugin is invoked with the evaluate operator.

Syntax

evaluate sql_request ( ConnectionString , SqlQuery [, SqlParameters [, Options]] ) [: OutputSchema]

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
ConnectionString	string	✔️	The connection string that points at the SQL Server network endpoint. See valid methods of authentication and how to specify the network endpoint.
SqlQuery	string	✔️	The query that is to be executed against the SQL endpoint. The query must return one or more row sets, but only the first one is made available for the rest of the Kusto query.
SqlParameters	dynamic		A property bag of key-value pairs to pass as parameters along with the query.
Options	dynamic		A property bag of key-value pairs to pass more advanced settings along with the query. Currently, only token can be set, to pass a caller-provided Microsoft Entra access token that is forwarded to the SQL endpoint for authentication.
OutputSchema	string		The names and types for the expected columns of the sql_request plugin output. Use the following syntax: ( ColumnName : ColumnType [, ...] ).

Authentication and authorization

The sql_request plugin supports the following three methods of authentication to the SQL Server endpoint.

Authentication method	Syntax	How	Description
Microsoft Entra integrated	Authentication="Active Directory Integrated"	Add to the ConnectionString parameter.	The user or application authenticates via Microsoft Entra ID to your cluster, and the same token is used to access the SQL Server network endpoint. The principal must have the appropriate permissions on the SQL resource to perform the requested action. For example, to read from the database the principal needs table SELECT permissions, and to write to an existing table the principal needs UPDATE and INSERT permissions. To write to a new table, CREATE permissions are also required.
Managed identity	Authentication="Active Directory Managed Identity";User Id={object_id}	Add to the ConnectionString parameter.	The request is executed on behalf of a managed identity. The managed identity must have the appropriate permissions on the SQL resource to perform the requested action. To enable managed identity authentication, you must add the managed identity to your cluster and alter the managed identity policy. For more information, see Managed Identity policy.
Username and password	User ID=...; Password=...;	Add to the ConnectionString parameter.	When possible, avoid this method as it may be less secure.
Microsoft Entra access token	dynamic({'token': h"eyJ0..."})	Add in the Options parameter.	The access token is passed as token property in the Options argument of the plugin.

Examples

Send a SQL query using Microsoft Entra integrated authentication

The following example sends a SQL query to an Azure SQL DB database. It retrieves all records from [dbo].[Table], and then processes the results on the Kusto side. Authentication reuses the calling user's Microsoft Entra token.

evaluate sql_request(
  'Server=tcp:contoso.database.chinacloudapi.cn,1433;'
    'Authentication="Active Directory Integrated";'
    'Initial Catalog=Fabrikam;',
  'select * from [dbo].[Table]') : (Id:long, Name:string)
| where Id > 0
| project Name

Send a SQL query using Username/Password authentication

The following example is identical to the previous one, except that SQL authentication is done by username/password. For confidentiality, we use obfuscated strings here.

evaluate sql_request(
  'Server=tcp:contoso.database.chinacloudapi.cn,1433;'
    'Initial Catalog=Fabrikam;'
    h'User ID=USERNAME;'
    h'Password=PASSWORD;',
  'select * from [dbo].[Table]') : (Id:long, Name:string)
| where Id > 0
| project Name

Send a SQL query using a Microsoft Entra access token

The following example sends a SQL query to an Azure SQL database retrieving all records from [dbo].[Table], while appending another datetime column, and then processes the results on the Kusto side. It specifies a SQL parameter (@param0) to be used in the SQL query.

evaluate sql_request(
  'Server=tcp:contoso.database.chinacloudapi.cn,1433;'
    'Authentication="Active Directory Integrated";'
    'Initial Catalog=Fabrikam;',
  'select *, @param0 as dt from [dbo].[Table]',
  dynamic({'param0': datetime(2020-01-01 16:47:26.7423305)})) : (Id:long, Name:string, dt: datetime)
| where Id > 0
| project Name

Send a SQL query without a query-defined output schema

The following example sends a SQL query to an Azure SQL database without an output schema. This is not recommended unless the schema is unknown, as it may impact the performance of the query

evaluate sql_request(
  'Server=tcp:contoso.database.chinacloudapi.cn,1433;'
    'Initial Catalog=Fabrikam;'
    h'User ID=USERNAME;'
    h'Password=PASSWORD;',
  'select * from [dbo].[Table]')
| where Id > 0
| project Name

Encryption and server validation

The following connection properties are forced when connecting to a SQL Server network endpoint, for security reasons.

• Encrypt is set to true unconditionally.
• TrustServerCertificate is set to false unconditionally.

As a result, the SQL Server must be configured with a valid SSL/TLS server certificate.

Specify the network endpoint

Specifying the SQL network endpoint as part of the connection string is mandatory. The appropriate syntax is:

Server = tcp: FQDN [, Port]

Where:

• FQDN is the fully qualified domain name of the endpoint.
• Port is the TCP port of the endpoint. By default, 1433 is assumed.