Microsoft Fabric Warehouse is a cloud data warehouse in Microsoft Fabric that stores and queries structured data using T-SQL. This connection is for Microsoft Fabric Warehouse, listed under Databases, and is separate from the Microsoft Fabric (REST API) connector.
Before you begin
Make sure the service principal that you use for the connection has these permissions. The same identity reads from the warehouse and writes to your staging location.
| Scope | Requirement |
|---|---|
| Tenant | Turn on the Service principals can use Fabric APIs setting. The connection test checks this setting and fails with a message that names it when it is off. |
| Workspace | Grant the service principal a role in the workspace through Manage access. Use the Contributor role so it can write. |
| Warehouse | Run GRANT ADMINISTER DATABASE BULK OPERATIONS and GRANT INSERT. You need both to run COPY INTO. |
| Sync destination | A data ingestion sync also needs CREATE SCHEMA (not needed for the dbo schema), CREATE TABLE, and ALTER TABLE. The connection test doesn’t check these permissions. |
| Destination table | Grant SELECT and UPDATE on each destination table. A merge reads the table to find matching records and updates them. |
| Staging | With OneLake staging, the workspace role covers staging, so you don’t need an Azure storage role. |
Your warehouse and staging lakehouse must be in the same workspace. The connection has one Workspace ID, and integrator.io looks up both the warehouse and the lakehouse in it.
Set up a connection
After you start the connection, configure it in the Create connection panel and complete all of the required * authentication settings:
| Setting | Instructions |
|---|---|
| Name your connection * |
Enter a clear and distinguishable name. Throughout integrator.io imports and exports, you will have the option to choose this new connection. A unique identifier will prove helpful later when selecting it from a list of the connections in your account. |
| Host * | Enter your Fabric instance host name, for example xxxx.datawarehouse.fabric.microsoft.com. Find it under Settings > SQL endpoint on the warehouse in the Fabric portal. For detailed information, see Connect to Fabric Data Warehouse. |
| Workspace ID * |
Enter your Fabric workspace ID. You can find it in the workspace URL. For example, in integrator.io uses it to register the service principal with Fabric, and as the workspace segment of your staging path if you stage in OneLake. |
| Service principal client * |
Authenticate with Fabric using Microsoft Entra ID service principal credentials. Select an existing service principal client, or select Create service principal client and complete the settings in Create a service principal client. The same identity reads from the warehouse and writes to your staging location, so there is no second credential to configure. |
| Warehouse name * | Enter the name of the Fabric warehouse you’re connecting to. Fabric uses the warehouse item name as the database name. |
| Staging location * |
Select where integrator.io stages files before loading them into your Fabric warehouse. The default is OneLake. Select OneLake to stage files in a lakehouse and use optimized bulk load. Select None to import with SQL queries only. Optimized bulk load isn’t available on a connection with no staging location. |
| Lakehouse * |
Enter the name of the lakehouse in OneLake where integrator.io stages files before loading them into your Fabric warehouse. This setting appears when Staging location is OneLake. Fabric can load staged files from any OneLake item except a warehouse, so staging goes in a lakehouse rather than in the warehouse you’re loading. |
| Staging folder | Enter the folder in your lakehouse where integrator.io stages files before loading them into your Fabric warehouse. The default is celigo-staging, and the name can be up to 512 characters. This setting appears when Staging location is OneLake. |
Create a service principal client
If you don’t have a service principal client, select Create service principal client in the Create connection panel. In the Create iClient panel, complete these settings:
| Setting | Instructions |
|---|---|
| Name * | Enter a unique name for this service principal so you can find it when you set up other connections. |
| Type * | Shows Azure service principal. You can’t change this setting. |
| Auth method * | Select how the service principal authenticates with Microsoft Entra ID: Client secret or Certificate. The settings that follow depend on your choice. |
| Client ID * | Enter the Application (client) ID from your Microsoft Entra ID app registration. For detailed information, see Register an application in Microsoft Entra ID. |
| Client secret * |
Enter your Microsoft Entra ID client secret. For detailed information on retrieving the client secret, see Add credentials to your application. Multiple layers of protection, including AES 256 encryption, are in place to keep your client secret safe. When editing this connection, you must re-enter this value each time; it is stored only when the connection is saved and never displayed as text. This setting appears when Auth method is Client secret. |
| Client certificate * |
Enter the certificate in PEM format, including the private key. For detailed information, see Add credentials to your application. This setting appears when Auth method is Certificate. |
| Client certificate password |
Enter the password for the certificate’s private key. Complete this setting only if the private key is encrypted. This setting appears when Auth method is Certificate. |
| Tenant ID * | Enter the Directory (tenant) ID of your Microsoft Entra ID tenant. |
Test the connection
Select Test connection or Save to verify the connection. integrator.io checks the warehouse and the Service principals can use Fabric APIs tenant setting. When Staging location is OneLake, it also checks the staging location by running a test COPY INTO.
The test rejects two kinds of endpoint, and each message tells you to connect to a Fabric warehouse:
- A lakehouse SQL analytics endpoint — This endpoint is a Lakehouse SQL analytics endpoint, which is read-only. Connect to a Fabric Warehouse to load data.
- A SQL database in Fabric — This connector doesn’t support a SQL database in Fabric. Connect to a Fabric Warehouse to load data.
The test doesn’t check grants on your destination tables. If the connection tests successfully but loads fail, confirm that the warehouse and destination table permissions in Before you begin are in place.