Azure SQL external table connection strings
Applies to: ✅ Azure Data Explorer
To access an SQL external table, a connection string is provided during its creation. This connection string specifies the resource to be accessed and its authentication information.
Supported SQL external table types:
- Azure SQL Database
- Azure Database for MySQL
- Azure Database for PostgreSQL
- Azure Cosmos DB.
For information on how to manage SQL external tables, see Create and alter SQL external tables.
Regardless of the authentication method used, the principal must have the necessary permissions on the SQL database to perform the desired actions. For more information, see Required permissions on the SQL database.
Supported authentication methods by database type
The following table shows the supported authentication methods for each type of database acting as the source for the external table.
Note
Where possible, the preferred authentication method is managed identity.
| Authentication method | SQL Server | PostgreSQL | MySQL | Cosmos DB |
|---|---|---|---|---|
| Microsoft Entra integrated (impersonation) | ✔️ | ❌ | ❌ | ✔️ |
| Managed identity | ✔️ | ❌ | ❌ | ✔️ |
| Username and Password | ✔️ | ✔️ | ✔️ | ✔️ |
Microsoft Entra integrated (impersonation)
With this authentication method, the user or application authenticates via Microsoft Entra ID, and the same token is then used to access the SQL Server network endpoint. This method is supported for SQL Server and Cosmos DB.
To use Microsoft Entra integrated authentication (impersonation), add ;Authentication="Active Directory Integrated" to the SQL connection string.
| Example |
|---|
"Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;" |
Managed identity
Your query environment makes requests on behalf of a managed identity and uses its identity to access resources. This method is supported for SQL Server and Cosmos DB.
For a system-assigned managed identity, append ;Authentication="Active Directory Managed Identity" to the connection string. For a user-assigned managed identity, append ;Authentication="Active Directory Managed Identity";User Id={object_id} to the connection string.
| Managed identity type | Example |
|---|---|
| System-assigned | "Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication="Active Directory Managed Identity";Initial Catalog=mydatabase;" |
| User-assigned | "Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication="Active Directory Managed Identity";User Id=00aa00aa-bb11-cc22-dd33-44ee44ee44ee;Initial Catalog=mydatabase;" |
Username and password
To authenticate with username and password, set the keywords User ID and Password in the connection string.
| Example |
|---|
"Server=tcp:myserver.database.chinacloudapi.cn,1433;User Id={myUserId};Password={myPlaceholderPassword};Initial Catalog=mydatabase;" |
Required permissions on the SQL database
For all authentication methods, the principal (or managed identity) must have the necessary permissions on the SQL database to perform the requested operation:
- Read permissions: table SELECT
- Write permissions:
- Existing table: table UPDATE and INSERT
- New table: CREATE, UPDATE, and INSERT