sql_request plugin

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

The sql_request plugin sends a SQL query to a SQL Server network endpoint and returns the first rowset in the results.

Arguments

  • ConnectionString: A string literal indicating the connection string that points at the SQL Server network endpoint. See below for valid methods of authentication and how to specify the network endpoint.

  • SqlQuery: A string literal indicating the query that is to be executed against the SQL endpoint. Must return one or more rowsets, but only the first one is made available for the rest of the Kusto query.

  • SqlParameters: A constant value of type dynamic that holds key-value pairs to pass as parameters along with the query. Optional.

  • Options: A constant value of type dynamic that holds more advanced settings as key-value pairs. Currently only token can be set, to pass a caller-provided AAD access token that is forwarded to the SQL endpoint for authentication. Optional.

Examples

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

Note: This example should not be taken as a recommendation to filter/project data in this manner. It's usually preferable that SQL queries will be constructed to return the smallest data set possible, as currently the Kusto optimizer does not attempt to optimize queries between Kusto and SQL.

evaluate sql_request(
  'Server=tcp:zivckusto2.database.windows.net,1433;'
    'Authentication="Active Directory Integrated";'
    'Initial Catalog=zivckusto2;',
  'select * from [dbo].[Table]')
| where Id > 0
| project Name

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

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

Authentication

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

  • AAD integrated authentication (Authentication="Active Directory Integrated"): This is the preferred method, in which the user or application authenticates via AAD to Kusto, and the same token is then used to access the SQL Server network endpoint.

  • Username/Password authentication (User ID=...; Password=...;): Support for this method is provided when AAD integrated authentication cannot be performed. Avoid this method, when possible, as secret information is sent through Kusto.

  • AAD access token (dynamic({'token': h"eyJ0..."})): With this authentication method, the access token is generated by the caller and forwarded by Kusto to the SQL endpoint. The connection string should not include authentication information like Authentication, User ID, or Password. Instead, the access token is passed as token property in the Options argument of the sql_request plugin.

Warning

Connection strings and queries that include confidential information or information that should be guarded should be obfuscated so that they'll be omitted from any Kusto tracing. See obfuscated string literals for more details.

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.

Specifying 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.

Note

Other forms of specifying the network endpoint are not supported. One cannot omit, for example, the prefix tcp: even though it is possible to do so when using the SQL client libraries programmatically.