Azure SQL external tables

• 1: Create and alter Azure SQL external tables
• 2: Query SQL external tables
• 3: Use row-level security with Azure SQL external tables

1 - Create and alter Azure SQL external tables

Creates or alters an Azure SQL external table in the database in which the command is executed.

Supported Azure SQL external table types

1. SQL Server
2. MySQL
3. PostgreSQL
4. Cosmos DB

Permissions

To .create requires at least Database User permissions and to .alter requires at least Table Admin permissions.

To .create, .alter, or .create-or-alter an external table using managed identity authentication requires Database Admin permissions. This method is supported for SQL Server and Cosmos DB external tables.

Syntax

(.create | .alter | .create-or-alter) external table TableName (Schema) kind = sql [ table = SqlTableName ] (SqlConnectionString) [with ( [ sqlDialect = SqlDialect ] , [ Property , … ])]

Parameters

Optional properties

Authentication and authorization

To interact with an external Azure SQL table, you must specify authentication means as part of the SqlConnectionString. The SqlConnectionString defines the resource to access and its authentication information.

For more information, see Azure SQL external table authentication methods.

Examples

The following examples show how to create each type of Azure SQL external table.

SQL Server

.create external table MySqlExternalTable (x:long, s:string) 
kind=sql
table=MySqlTable
( 
   h@'Server=tcp:myserver.database.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;'
)
with 
(
   docstring = "Docs",
   folder = "ExternalTables", 
   createifnotexists = true,
   primarykey = x,
   firetriggers=true
)  

Output

MySQL

.create external table MySqlExternalTable (x:long, s:string) 
kind=sql
table=MySqlTable
( 
   h@'Server=myserver.mysql.database.windows.net;Port = 3306;UID = USERNAME;Pwd = PASSWORD;Database = mydatabase;'
)
with 
(
   sqlDialect = "MySql",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

PostgreSQL

.create external table PostgreSqlExternalTable (x:long, s:string) 
kind=sql
table=PostgreSqlTable
( 
   h@'Host = hostname.postgres.database.azure.com; Port = 5432; Database= db; User Id=user; Password=pass; Timeout = 30;'
)
with 
(
   sqlDialect = "PostgreSQL",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

Cosmos DB

.create external table CosmosDBSQLExternalTable (x:long, s:string) 
kind=sql
( 
   h@'AccountEndpoint=https://cosmosdbacc.documents.azure.com/;Database=MyDatabase;Collection=MyCollection;AccountKey=' h'R8PM...;'
)
with 
(
   sqlDialect = "CosmosDbSQL",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

Related content

• External tables overview
• SQL external table authentication methods
• Create and alter Azure Storage external tables

2 - Query SQL external tables

You can query a SQL external table just as you would query an Azure Data Explorer or a table in a KQL Database.

How it works

Azure SQL external table queries are translated from Kusto Query Language (KQL) to SQL. The operators after the external_table function call, such as where, project, count, and so on, are pushed down and translated into a single SQL query to be executed against the target SQL table.

Example

For example, consider an external table named MySqlExternalTable with two columns x and s. In this case, the following KQL query is translated into the following SQL query.

KQL query

external_table(MySqlExternalTable)
| where x > 5 
| count

SQL query

SELECT COUNT(*) FROM (SELECT x, s FROM MySqlTable WHERE x > 5) AS Subquery1

3 - Use row-level security with Azure SQL external tables

Apply row-level security on Azure SQL external tables

This document describes how to apply a row-level security (RLS) solution with SQL external tables. row-level security implements data isolation at the user level, restricting the access to data based on the current user credential. However, Kusto external tables don’t support RLS policy definitions, so data isolation on external SQL tables require a different approach. The following solution employs using row-level security in SQL Server, and Microsoft Entra ID Impersonation in the SQL Server connection string. This combination provides the same behavior as applying user access control with RLS on standard Kusto tables, such that the users querying the SQL External Table are able to only see the records addressed to them, based on the row-level security policy defined in the source database.

Prerequisites

• ALTER ANY SECURITY POLICY permission on the SQL Server
• Table admin level permissions on the Kusto-side SQL external table

Sample table

The example source is a SQL Server table called SourceTable, with the following schema. The systemuser column contains the user email to whom the data record belongs. This is the same user who should have access to this data.

CREATE TABLE SourceTable (
    id INT,
    region VARCHAR(5),
    central VARCHAR(5),
    systemuser VARCHAR(200)
)

Configure row-level security in the source SQL Server - SQL Server side

For general information on SQL Server row-level security, see row-level security in SQL Server.

1. Create a SQL Function with the logic for the data access policy. In this example, the row-level security is based on the current user’s email matching the systemuser column. This logic could be modified to meet any other business requirement.

   CREATE SCHEMA Security;
   GO
   
   CREATE FUNCTION Security.mySecurityPredicate(@CheckColumn AS nvarchar(100))
       RETURNS TABLE
   WITH SCHEMABINDING
   AS
       RETURN SELECT 1 AS mySecurityPredicate_result
       WHERE @CheckColumn = ORIGINAL_LOGIN() OR USER_NAME() = 'Manager';
   GO
   

2. Create the Security Policy on the table SourceTable with passing the column name as the parameter:

   CREATE SECURITY POLICY SourceTableFilter
   ADD FILTER PREDICATE Security.mySecurityPredicate(systemuser)
   ON dbo.SourceTable
   WITH (STATE = ON)
   GO
   

   [!NOTE] At this point, the data is already restricted by the mySecurityPredicate function logic.

Allow user access to SQL Server - SQL Server side

The following steps depend on the SQL Server version that you’re using.

1. Create a sign in and User for each Microsoft Entra ID credential that is going to access the data stored in SQL Server:

   CREATE LOGIN [user@domain.com] FROM EXTERNAL PROVIDER --MASTER
   
   CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER --DATABASE
   

2. Grant SELECT on the Security function to the Microsoft Entra ID user:

   GRANT SELECT ON Security.mySecurityPredicate to [user@domain.com]
   

3. Grant SELECT on the SourceTable to the Microsoft Entra ID user:

   GRANT SELECT ON dbo.SourceTable to [user@domain.com]
   

Define SQL external table connection String - Kusto side

For more information on the connection string, see SQL External Table Connection Strings.

1. Create a SQL External Table with using Connection String with Active Directory Integrated authentication type. For more information, see Microsoft Entra integrated (impersonation).

   .create external table SQLSourceTable (id:long, region:string, central:string, systemser:string) 
   kind=sql
   table=SourceTable
   ( 
      h@'Server=tcp:[sql server endpoint],1433;Authentication=Active Directory Integrated;Initial Catalog=[database name];'
   )
   with 
   (
      docstring = "Docs",
      folder = "ExternalTables", 
      createifnotexists = false,
      primarykey = 'id'
   )
   

   Connection String:

   Server=tcp:[sql server endpoint],1433;Authentication=Active Directory Integrated;Initial Catalog=[database name];
   

2. Validate the data isolation based on the Microsoft Entra ID, like it would work with row-level security on in Kusto. In this case, the data is filtered based on the SourceTable’s systemuser column, matching the Microsoft Entra ID user (email address) from the Kusto impersonation:

   external_table('SQLSourceTable')
   

   [!NOTE] The policy can be disabled and enabled again, on the SQL Server side, for testing purposes.

To disable and enable the policy, use the following SQL commands:

ALTER SECURITY POLICY SourceTableFilter
WITH (STATE = OFF);

ALTER SECURITY POLICY SourceTableFilter
WITH (STATE = ON);

With the Security Policy enabled on the SQL Server side, Kusto users only see the records matching their Microsoft Entra IDs, as the result of the query against the SQL External table. With the Security Policy disabled, all users are able to access the full table content as the result of the query against the SQL External table.

Related content

• Row-level security in SQL Server
• SQL external table connection strings