Editar

Compartir a través de

Service principal in Fabric Data Warehouse

  • Artículo

Applies to: ✅ Warehouse in Microsoft Fabric

An Azure service principal (SPN) is a security identity used by applications or automation tools to access specific Azure resources. Unlike user identities, service principals are non-interactive, application-based identities that can be assigned precise permissions, making them perfect for automated processes or background services. By using service principals, you can connect to your data sources securely while minimizing the risks of human error and identity-based vulnerabilities. To learn more about service principals, see Application and service principal objects in Microsoft Entra ID.

Prerequisites

  1. Create a service principal, assign roles, and create secret using Azure.

  2. Ensure the tenant admin can enable Service principals can use Fabric APIs in Fabric Admin portal.

  3. Ensure a user with Administrator workspace role can grant access for an SPN through Manage access in the Workspace.

    Screenshot from the Fabric portal of the manage access popup window.

Create and access warehouses through REST APIs using SPN

Users with administrator, member, or contributor workspace role can use service principals for authentication to create, update, read, and delete Warehouse items via Fabric REST APIs. This allows you to automate repetitive tasks such as provisioning or managing warehouses without relying on user credentials.

If you use a delegated account or fixed identity (owner's identity) to create the warehouse, the warehouse will use that credential while accessing OneLake. This creates a problem when the owner leaves the organization, because the warehouse will stop working. To avoid this, create warehouses using an SPN.

Fabric also requires the user to sign in every 30 days to ensure a valid token is provided for security reasons. For a data warehouse, the owner needs to sign in to Fabric every 30 days. This can be automated using an SPN with the List API.

Screenshot of a Fabric API POST call using an SPN.

Warehouses created by an SPN using REST APIs will be displayed in the Workspace list view in the Fabric portal, with the Owner name as the SPN. In the following image, a screenshot from the workspace in the Fabric portal, "Fabric Public API test app" is the SPN that created the Contoso Marketing Warehouse.

Screenshot from the Fabric portal of the workspace item list. A warehouse is shown. Its owner isn't a personal account but an SPN.

Connect to client applications using SPN

You can connect to Fabric warehouses by using service principals with tools like SQL Server Management Studio (SSMS) 19 or higher versions.

  • Authentication: Microsoft Entra Service Principal
  • User name: Client ID of SPN (created through Azure in Prerequisite section)
  • Password: Secret (created through Azure in Prerequisite section)

Screenshot of signing into Fabric with an SPN in SQL Server Management Studio (SSMS).

Control plane permissions

SPNs can be granted access to warehouses using workspace roles through Manage access in the workspace. In addition, warehouses can be shared with an SPN through the Fabric portal via Item Permissions.

Data plane permissions

Once warehouses are provided control plane permissions to an SPN through workspace roles or Item permissions, administrators can use T-SQL commands like GRANT to assign specific data plane permissions to service principals, to control precisely which metadata/data and operations an SPN has access to. This is recommended to follow the principle of least privilege.

For example:

GRANT SELECT ON <table name> TO <service principal name>;

Once permissions are granted, SPNs can connect to client application tools like SSMS, thereby providing secure access for developers to run COPY INTO (with and without firewall enabled storage), and also to run any T-SQL query programmatically on a schedule with Data Factory pipelines.

Screenshot of a query and result in SQL Server Management Studio (SSMS), where the user has accessed an Azure Storage object using the SPN.

Monitor

When an SPN runs queries in the warehouse, there are various monitoring tools that provide visibility into the user or SPN that ran the query. You can find the user for query activity the following ways:

For more information, see Monitor Fabric Data warehouse.

Takeover API

Ownership of warehouses can be changed from an SPN to user, and from a user to an SPN.

  • Takeover from SPN or user to user: See Change ownership of Fabric Warehouse.

  • Takeover from SPN or user to SPN: Use a POST call on REST API.

    POST <PowerBI Global Service FQDN>/v1.0/myorg/groups/{workspaceid}/datawarehouses/{warehouseid}/takeover

Limitations

Limitations of service principals with Microsoft Fabric Data Warehouse:

  • Default semantic models are not supported for SPN created warehouses and as a result, features such as listing tables in dataset view, creating report from the default dataset will not work.
  • Service principal for SQL analytics endpoints is not currently supported.
  • Service principal or Entra ID credentials are currently not supported for COPY INTO error files.
  • Service principals are not supported for GIT APIs. SPN support exists only for Deployment pipeline APIs.

Related content