Fabric Warehouse

SQL Tran can use Managed Identity and Service Principal to connect to Fabric Warehouse.

Make sure you use regular Fabric workspace and not personal workspace! You can tell by the icon:

FabricDemo is regular workspace. My workspace is personal workspace.

If all you have is "My workspace", you will need to create a new workspace.

Here is how to set up Service Principal connection:

1. Create new enterprise application in Azure portal

   1. You may need to be given additional permissions, such as Application Administrator

   2. Pick the "Register an application to integrate with Microsoft Entra ID (App you're developing)" type

   3. Note its name, application id and object id

   4. Go to App registrations, find your application in All applications, enter

   5. Go to "Certificates & secrets", create a certificate, save it

2. Go to Fabric workspace, in the upper-right corner click Gears icon for settings

   1. Go to Admin portal

   2. In the Tenant settings, find Developer settings

   3. Enable the option "Service principals can use Fabric APIs"

3. Go to Fabric workspace, in the upper-right corner click Manage access

   1. + Add people or groups

   2. Type your app name and wait for the dropdown to display it - check that id matches

   3. Change the access type from Viewer to Contributor

   4. Click Add

   5. Go to your Fabric Warehouse, click ellipsis, Manage permissions. It must list your app as "Workspace Contributor"

   6. Again, click ellipsis, Copy SQL Connection String

4. Go to SQL Tran, create a new project for Fabric Warehouse

   1. Click the hamburger menu in the lower-left corner, click Project settings

   2. Navigate to Database connections

   3. Select Target staging

   4. Paste the Fabric Warehouse connection string into the Server field

   5. For authentication, select Microsoft Entra Service principal, then paste application id into Client ID field, and certificate as Secret

   6. Copy the exact Fabric Warehouse name into Database field

   7. Click "Test connection"

Microsoft Entra Managed Identity is the recommended authentication method for databases hosted in the same Azure tenant as the SQL Tran deployment.

Microsoft Entra Service Principal can be used as an authentication method for databases hosted in the same Azure tenant as the SQL Tran deployment and/or different Azure tenants. The downside of using the Microsoft Entra Service Principal approach is the secret which needs to be manually recreated and pasted into SQL Tran upon expiration of its validity which can be set to up to 2 years.