Connect to cloud data sources in the Power BI service

With Power BI, you can share cloud connections for semantic models, paginated reports, datamarts, dataflows, and Power Query Online experiences in Get data. This capability enables you to create multiple connection objects to the same cloud data source. For example, you can create separate connections to the same data source with different credentials or privacy settings, and share the connections with others so they don't need to manage their own separate cloud connections.

Types of data connections

The following table shows how various types of connections map to the two primary connection types: data gateway connections and direct cloud connections. The new capability described in this article is Shareable cloud connections.

Advantages of shareable cloud connections

Connections that use a personal cloud connection come with several limitations. For example, by using a personal cloud connection, you can only create a single personal cloud connection object to a given data source. All semantic models that connect to the data source use the same personal cloud connection object. If you change the credentials of the personal cloud connection, all semantic models using that personal cloud connection are affected. Often, that's not a desired outcome.

Another limitation of a personal cloud connection is that you can't share it with others. Other users can't bind their semantic models and paginated reports to the personal cloud connection you own. Users must maintain their own personal cloud connections.

Shareable connections have no such limitations and provide more streamlined, flexible connection management, including the following advantages:

• Support multiple connections to the same data source - support for multiple connections on the same data source is particularly useful when you want to use different connection settings for different semantic models and other artifacts. It's also useful when you want to assign individual artifacts their own separate connections, to ensure their connection settings are isolated from each other.

• You can share these connections with other users - by using shareable connections, you can assign other users Owner permissions, enabling them to manage all aspects of the connection configuration, including credentials. You can provide other users with Resharing permissions so they can use and reshare the connection with others. You can also provide User permissions, enabling them to use the connection to bind their artifacts to the data source.

• Lower the overhead of maintaining data connections and credentials - when combined with the data source and gateway management experience, you can centralize data source connection management for gateway and cloud connections. Such centralization and management is already common for enterprise and VNET data gateways, for which a gateway administrator creates, shares, and maintains the connections. By using shareable connections, you can now extend such centralized connection management to cloud data sources as well.

Prerequisites

To work with cloud connections in the Power BI service, you need:

• A Power BI Pro or Premium Per User (PPU) license, or a workspace in a Power BI Premium or Fabric capacity.
• A semantic model or paginated report published to a workspace in the Power BI service.
• Appropriate permissions. To create or manage shareable cloud connections, you need at least a Contributor role in the workspace. To use a shareable cloud connection that someone else created, you need User permissions on that connection.

Most of the settings described in this article are found in the semantic model settings in the Power BI service. To get there:

1. Sign in to the Power BI service.
2. Open the workspace that contains your semantic model.
3. Find your semantic model in the workspace content list.
4. Select More options (...) next to the semantic model name, and then select Settings.
5. Expand the Gateway and cloud connections section.

You can also manage all your connections from anywhere in the Power BI service by selecting the Settings gear icon in the upper right corner, and then selecting Manage connections and gateways.

Compare shareable cloud connection to other connections

By default, when you create a Power BI Desktop report that connects to a cloud data source, then upload it into a workspace in the Power BI service, Power BI creates a personal cloud connection and binds it to your semantic model. You must provide credentials for this connection. If an existing personal cloud connection is available, you likely provided the credentials previously.

In contrast, if you have access to at least one shareable cloud connection to the same data source, you can use the shareable cloud connection instead of your personal cloud connection. The owner of the shareable cloud connection already configured it for you.

To use a shareable cloud connection instead of a personal cloud connection:

1. Open the semantic model settings page as described in the Prerequisites section.
2. In the Gateway and cloud connections section, find Cloud connections.
3. Select the Maps to dropdown and choose the shareable cloud connection you want to use.
4. Select Apply.

The following screenshot shows the settings.

Create a new shareable cloud connection

You can create a new shareable cloud connection directly from the Semantic model settings page. Under Gateway connections > Cloud connections, select the Maps to dropdown and then select Create a connection.

A pane called New connection appears and automatically populates the configuration parameters.

Enabling the creation of new connections makes it easy to create separate shareable cloud connections for individual semantic models, if needed. You can also display the connection management page from anywhere in the Power BI service by selecting the Settings gear in the upper right corner of the Power BI service, and then selecting Manage connections and gateways.

Create a shareable cloud connection using workspace identity

You can create a shareable cloud connection by using the Workspace identity authentication method. This method uses the automatically managed service principal associated with the Fabric workspace to connect to data. To use the connection, the model owner must have Contributor (or higher) access to the workspace.

To create a Workspace identity, follow these steps:

1. Configure the workspace to have a Workspace identity. The Workspace identity is an automatically managed service principal associated with the Fabric workspace.
2. Create a shareable cloud connection (SCC) with Workspace identity as the authentication method.
3. Bind the data source to the SCC in the semantic model settings.

Keep the following considerations in mind when creating or using a Workspace identity:

• Workspace identity is supported with Fabric data sources.
• Workspace identity is also supported for Power BI semantic models. Set the Authentication method in the Analysis Services data connection to Workspace identity.
  • Workspace identity authentication for Power BI semantic models requires the tenant setting Service principals can call Fabric public APIs to be enabled. Ensure that the Workspace identity has Contributor (or higher) access to the source workspace.
  • If you restrict the tenant setting Service principals can call Fabric public APIs to specific security groups, ensure that the Workspace identity is a member of at least one of these security groups.
• The connection type you're using must support the Workspace identity authentication type, which includes SQL Server and ADLS connectors. For the connection type you're using, if there's a Workspace identity option under the Authentication setting, then that connector is supported.

Default connection settings

When you connect to a Fabric data source specifically, you use your Entra ID Single Sign-On (SSO) credentials by default.

You can also use a shareable cloud connection instead of the default connection settings to connect a semantic model to a Fabric data source. By using this method, you apply the settings you configured for that shareable cloud connection, such as fixed credentials. You can bind the data source to the shareable cloud connection and override the default SSO connection for that data source.

To select your shareable cloud connection instead of your default SSO settings, select the shareable cloud connection in the Maps to: drop-down for the data source to which you want your semantic model to connect, as shown in the following image:

If you don't have a shareable cloud connection, you can select Create a connection and create a new connection, as described in the previous section of this article.

Using shareable cloud connections with paginated reports

When you share your paginated report in the Power BI service, you can update the cloud connections from within the report itself. To modify the cloud connections for your paginated report, go to your workspace in the Power BI service, select the More button (ellipses), and then select Manage.

When you select Manage, you see a page with several tabs. Select the Reports tab from the top row, and then update the connection from within the Cloud connections area, as shown in the following screenshot.

Limitations and considerations

• Shareable cloud connections also share your credentials - when you allow others to use your shareable cloud connections, you let them connect their own semantic models, paginated reports, and other artifacts to the corresponding data sources by using the connection details and credentials you provided. Make sure you only share connections (and their credentials) that you're authorized to share.

• Every user is limited to 1,000 data source connections in every cloud tenant: If you reach the maximum number of data sources, verify that the number of data sources per user isn't over the limit of 1,000 connections. To resolve any related problems, you can manually remove existing data sources from the admin center or, alternatively, use the following PowerShell script to find and bulk-delete any data sources that exceed that limit.

  ## required module "mcirosoftpowerbimgmt" Install-Module -Name DataGateway and sign in the same user who exceeded the 1000 limit
  Import-Module -name microsoftpowerbimgmt
  
  ## get the gateway information per the sign in person. Choose Environment: Public, USGov, China, USGovHigh, USGovMil
  $environment = "Public"
  Connect-PowerBIServiceAccount -Environment $environment
  
  switch ($environment) {
      "Public" { $baseURL = "https://api.powerbi.com/v2.0/myorg/me/"; Break }
      "USGov" { $baseURL = "https://api.powerbigov.us/v2.0/myorg/me/"; Break }
      "China" { $baseURL = "https://api.powerbi.cn/v2.0/myorg/me/"; Break }
      "USGovHigh" { $baseURL = "https://api.high.powerbigov.us/v2.0/myorg/me/"; Break }
      "USGovMil" { $baseURL = "https://api.mil.powerbigov.us/v2.0/myorg/me/"; Break }
  }   
  
  $getDatasourcesURL = $baseURL + "gatewayClusterDatasources?$expand=users"
  
  $datasources = Invoke-PowerBIRestMethod -Url $getDatasourcesURL -Method GET | ConvertFrom-Json
  
  foreach($dataource in $datasources.value)
  {
      if($datasource.gatewayType -eq "TenantCloud")
      {
          "cloud datasource found with id = {0}, name = {1}" -f $dataource.id, $datasource.datasourceName
          $gatewayId = $datasource.clusterId
          $datasourceId = $dataource.id
  
          ## conditional logic to determine if name matches set
          $deleteDatasourceURL = $baseURL + "gatewayClusters/$gatewayId/datasources/$datasourceId"
          Invoke-PowerBIRestMethod -Url $deleteDatasourceURL -Method DELETE
      }    
  }
  

  If you're an ISV or any other Power BI Embedded app owner with many customers, use service principal profiles for multitenancy apps in Power BI embedded. If you're not an ISV, you might reach this limit because you're creating a new data source for every CSV or Excel file. To solve this problem, you might want to use the upload file box in Power BI Desktop to select multiple Excel files, which creates multiple data source connections. In this scenario, to ensure that only a single data source is selected, select the folder containing those Excel files.

• You can't mix an Excel on-premises data source with an existing Analysis Services DirectQuery data source; you can only include an Excel on-premises data source to your report if it's in a separate query. In such situations, you can map the Excel data source to a gateway, and leave the Analysis Services DirectQuery cloud data source as-is.

• Power BI Dataflow Gen1 and Fabric Dataflow Gen2 don't support shareable cloud connections. Other versions, like Power Apps dataflows, do support shareable cloud connections.

• Group managed service account (gMSA) credentials aren't supported for SQL data connections. A gMSA isn't designed for running the data connection.

Related content

For more information about creating shareable cloud connections, see:

• Create and share cloud data sources in the Power BI service

You can do all sorts of things with the Power BI service and Power BI Desktop. For more information on its capabilities, check out the following resources:

• What is Power BI Desktop?
• Query overview with Power BI Desktop
• Data types in Power BI Desktop
• Shape and combine data with Power BI Desktop
• Common query tasks in Power BI Desktop