Detailed Guide on Enabling On-Premises Data Gateway for Microsoft Fabric and Power BI

Microsoft Fabric’s Data Factory is a robust cloud-based data integration service that enables you to create, schedule, and manage workflows across various data sources. When dealing with data sources that reside on-premises, Microsoft offers the On-Premises Data Gateway, which provides a secure connection between your on-premises environment and the cloud.

For instance, if you have an on-premises SQL Server (such as SQL Server 2022), you can use the gateway to seamlessly integrate this data into your Power BI reports or other Microsoft Fabric components. The gateway is highly versatile, supporting numerous services like Azure Analysis Services, Power Apps, Power Automate, and Azure Logic Apps, making it suitable for a wide range of data integration scenarios.

In this post, we will walk step by step on how to download, install, configure and On-Premises Data Gateway and get your data from on-prem to the cloud.

1. Create an on-premises data gateway

a. An on-premises data gateway is a software application designed to be installed within a local network environment. It provides a means to directly install the gateway onto your local machine. To download gateway, refer to this Microsoft Documentation.

b. Sign in with your user account to access the on-premises data gateway, which will then be ready for use. Be sure to store your recovery key in a secure location.

2. Create Fabric connection for your on-premises data source

Go to the admin portal and click the settings icon (a gear-shaped icon) located at the top right corner of the page. From the dropdown menu, select Manage connections and gateways.

On the New connection dialog that appears, select On-premises and then provide your gateway cluster, along with the associated resource type and relevant information.

Available connection types supported for on-premises connections include:

  • Entra ID
  • Adobe Analytics
  • Analysis Services
  • Azure Blob Storage
  • Azure Data Lake Storage Gen2
  • Azure Table Storage
  • Essbase
  • File
  • Folder
  • Google Analytics
  • IBM DB2
  • IBM Informix Database
  • MySQL
  • OData
  • ODBC
  • OLE DB
  • Oracle
  • PostgreSQL
  • Salesforce
  • SAP Business Warehouse Message Server
  • SAP Business Warehouse Server
  • SAP HANA
  • SharePoint
  • SQL Server
  • Sybase
  • Teradata
  • Web

3. Connect using Dataflow Gen2

a. Go to your workspace and create a Dataflow Gen2.

b. Add a new source to the dataflow and select the connection established in the previous step.

c. You can use the Dataflow Gen2 to perform any necessary data transformations based on your requirements.

d. Use the Add data destination button on the Home tab of the Power Query editor to add a destination for your data from the on-premises source.

e. Publish the Dataflow Gen2.

Now you’ve created a Dataflow Gen2 to load data from an on-premises data source into a cloud destination.

4. Using on-premises data in a pipeline

1. Go to your workspace and create a data pipeline.

2. From the Home tab of the pipeline editor, select Copy data and then Use copy assistant. Add a new source to the activity in the assistant’s Choose data source page, then select the connection established in the previous step.

3. Select a destination for your data from the on-premises data source.

4. Run the pipeline.

Now you’ve created and ran a pipeline to load data from an on-premises data source into a cloud destination.