sql_request 插件sql_request plugin

sql_request 插件将 SQL 查询发送到 SQL Server 网络终结点,并返回结果中的第一个行集。The sql_request plugin sends a SQL query to a SQL Server network endpoint and returns the first rowset in the results.

语法Syntax

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

参数Arguments

  • ConnectionString:一个 string 文本,用于指示指向 SQL Server 网络终结点的连接字符串。ConnectionString: A string literal indicating 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 文本,用于指示要对 SQL 终结点执行的查询。SqlQuery: A string literal indicating the query that is to be executed against the SQL endpoint. 必须返回一个或多个行集,但只有第一个行集可用于 Kusto 查询的其余部分。Must return one or more rowsets, but only the first one is made available for the rest of the Kusto query.

  • SqlParameters:dynamic 类型的常数值,用于保存作为参数随查询传递的键值对。SqlParameters: A constant value of type dynamic that holds key-value pairs to pass as parameters along with the query. 可选。Optional.

  • 选项dynamic 类型的常数值,它将更高级的设置保存为键值对。Options: A constant value of type dynamic that holds more advanced settings as key-value pairs. 目前只能设置 token,以便传递调用方提供的 Azure AD 访问令牌,该令牌将转发到 SQL 终结点,用于身份验证。Currently, only token can be set, to pass a caller-provided Azure AD access token that is forwarded to the SQL endpoint for authentication. 可选。Optional.

示例Examples

下面的示例向 Azure SQL DB 数据库发送 SQL 查询。The following example sends a SQL query to an Azure SQL DB database. 它从 [dbo].[Table] 检索所有记录,然后在 Kusto 端处理结果。It retrieves all records from [dbo].[Table], and then processes the results on the Kusto side. 身份验证会重复使用调用方用户的 Azure AD 令牌。Authentication reuses the calling user's Azure AD token.

备注

建议不要像此示例这样以此方式筛选或投影数据。This example should not be taken as a recommendation to filter or project data in this manner. 在构造 SQL 查询时,应当尽可能返回最小的数据集,因为 Kusto 优化器当前不会尝试优化 Kusto 与 SQL 之间的查询。SQL queries should be constructed to return the smallest data set possible, Since the Kusto optimizer doesn't currently attempt to optimize queries between Kusto and SQL.

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

下面的示例与上一个示例相同,只不过 SQL 身份验证是通过用户名/密码来执行的。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]')
| where Id > 0
| project Name

下面的示例向 Azure SQL DB 数据库发送一个 SQL 查询,从 [dbo].[Table] 检索所有记录,同时追加另一个 datetime 列,然后在 Kusto 端处理结果。The following example sends a SQL query to an Azure SQL DB database retrieving all records from [dbo].[Table], while appending another datetime column, and then processes the results on the Kusto side. 它指定要在 SQL 查询中使用的 SQL 参数 (@param0)。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)}))
| where Id > 0
| project Name

身份验证Authentication

sql_request 插件支持对 SQL Server 终结点使用以下三种身份验证方法:The sql_request plugin supports three methods of authentication to the SQL Server endpoint:

Azure AD 集成式身份验证Azure AD-integrated authentication

Authentication="Active Directory Integrated"

Azure AD 集成式身份验证是首选方法。Azure AD-integrated authentication is the preferred method. 此方法让用户或应用程序通过 Azure AD 向 Kusto 进行身份验证。This method has the user or application authenticate via Azure AD to Kusto. 然后,使用同一令牌访问 SQL Server 网络终结点。The same token is then used to access the SQL Server network endpoint.

用户名/密码身份验证Username/Password authentication

User ID=...; Password=...;

当无法执行 Azure AD 集成式身份验证时,会提供用户名和密码身份验证支持。Username and password authentication support is provided when Azure AD-integrated authentication can't be done. 如果可能,请尽量避免使用此方法,因为机密信息是通过 Kusto 发送的。Avoid this method, when possible, as secret information is sent through Kusto.

Azure AD 访问令牌Azure AD access token

dynamic({'token': h"eyJ0..."})

使用 Azure AD 访问令牌身份验证方法时,调用方会生成访问令牌,该令牌由 Kusto 转发到 SQL 终结点。With the Azure AD access token authentication method, the caller generates the access token, which is forwarded by Kusto to the SQL endpoint. 不应在连接字符串中包含身份验证信息(例如 AuthenticationUser IDPassword),The connection string shouldn't include authentication information like Authentication, User ID, or Password. 而应将访问令牌作为 sql_request 插件的 Options 参数中的 token 属性传递。Instead, the access token is passed as token property in the Options argument of the sql_request plugin.

警告

应该对包含机密信息(或应当受保护的信息)的连接字符串和查询进行模糊处理,以在任何 Kusto 跟踪中忽略它们。Connection strings and queries that include confidential information or information that should be guarded should be obfuscated to be omitted from any Kusto tracing. 有关详细信息,请参阅经过模糊处理的字符串文本For more informations, see obfuscated string literals.

加密和服务器验证Encryption and server validation

出于安全方面的原因,连接到 SQL Server 网络终结点时,会强制实施以下连接属性。The following connection properties are forced when connecting to a SQL Server network endpoint, for security reasons.

  • Encrypt 无条件地设置为 trueEncrypt is set to true unconditionally.
  • TrustServerCertificate 无条件地设置为 falseTrustServerCertificate is set to false unconditionally.

因此,必须为 SQL Server 配置有效的 SSL/TLS 服务器证书。As a result, the SQL Server must be configured with a valid SSL/TLS server certificate.

指定网络终结点Specify the network endpoint

必须将 SQL 网络终结点指定为连接字符串的一部分。Specifying the SQL network endpoint as part of the connection string is mandatory. 正确的语法为:The appropriate syntax is:

Server = tcp: FQDN [, Port]Server = tcp: FQDN [, Port]

其中:Where:

  • FQDN 是终结点的完全限定域名。FQDN is the fully qualified domain name of the endpoint.
  • Port 是终结点的 TCP 端口。Port is the TCP port of the endpoint. 默认为 1433By default, 1433 is assumed.

备注

不支持以其他形式指定网络终结点。Other forms of specifying the network endpoint are not supported. 例如,不能省略前缀 tcp:,即使在以编程方式使用 SQL 客户端库时可以省略它。One cannot omit, for example, the prefix tcp: even though it is possible to do so when using the SQL client libraries programmatically.

Azure Monitor 不支持此功能This capability isn't supported in Azure Monitor