Integrate SSIS with SQL database in Microsoft Fabric

This tutorial shows how to connect an SSIS package to SQL database in Microsoft Fabric using Microsoft Entra service principal authentication with the Microsoft OLE DB Driver for SQL Server.

Authentication

Use Microsoft Entra service principal authentication for SSIS packages because they typically run non-interactively under agents. This approach provides secure app-only access without user prompts or multifactor authentication (MFA). It lets you apply least-privilege access through Fabric workspace and item permissions.

Service principal authentication aligns with Authentication=ActiveDirectoryServicePrincipal support in Microsoft OLE DB Driver for SQL Server version 18.5.0 and later versions, and improves auditability and secret hygiene when you store client secrets in SSIS Catalog environments or Azure Key Vault.

Prerequisites

• A Fabric workspace with a SQL database.
• Register a service principal (app registration).
• Enable service principal access to Fabric workspace.
• Microsoft OLE DB Driver for SQL Server version 18.5.0 or later versions, including MSOLEDBSQL19.
• Outbound network access to Fabric SQL Database (Default connection policy).

Configure SSIS OLE DB Connection Manager

Use the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) and configure:

• Authentication: ActiveDirectoryServicePrincipal

• User name (User ID): Application (client) ID of your service principal

• Password: Client secret associated with the app registration

• Initial Catalog: Fabric SQL Database name (from Settings → Connection strings)

• Server name (Data Source): Fabric SQL host (for example, <server-unique-identifer>.database.fabric.microsoft.com)

  

  

References

• Authentication in SQL database in Microsoft Fabric
• Use Microsoft Entra ID
• Connect to your SQL database in Microsoft Fabric
• Download Microsoft OLE DB Driver for SQL Server