Muokkaa

Jaa

Azure SQL external table connection strings

  • Artikkeli

Applies to: ✅ Microsoft Fabric ✅ 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) ✔️ ❌ ❌ ❌
Username and Password ✔️ ✔️ ✔️ ✔️
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.windows.net,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.windows.net,1433;Authentication="Active Directory Managed Identity";Initial Catalog=mydatabase;"
User-assigned "Server=tcp:myserver.database.windows.net,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.windows.net,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

Related content