Connect PBI to on premise sql server

Hi Hussein_charif , Hope you are doing good!

Setting up an on-premises data gateway to connect your SQL Server to Power BI involves several steps. Here’s a detailed guide to help you:

Step 1: Understand the Gateway

The on-premises data gateway acts as a bridge between your on-premises data sources (like your SQL Server) and Power BI Service (cloud). You only need to install and configure the gateway on the on-premises server with access to the SQL Server, not on your laptop.

Step 2: Install the Gateway on the Server

Download and Install the Gateway:

Download the on-premises data gateway installer from Microsoft's official site.

Install it only on the server where the SQL Server resides.

Choose Installation Mode:

During installation, choose Standard Mode (not personal mode) to enable multi-user access.

Sign in with Power BI Account:

Use your Power BI Service account (the one you use to log into Power BI) to sign in during the gateway setup.

Complete the Installation:

Once the gateway is installed, it will open the gateway configuration tool.

Step 3: Configure the Gateway

Register the Gateway:

If it's your first time, register the gateway with a unique name. You’ll need this name later when configuring the connection in Power BI Service.

Check Network Connectivity:

Ensure the gateway can communicate with the Power BI Service. You may need to open certain ports or check with your IT team if firewalls are in place.

Add an Admin:

Add yourself or any other administrator as a user for managing the gateway.

Step 4: Configure Data Source in Gateway

Go to Power BI Service:

Open Power BI Service in your browser.

Navigate to Settings > Manage Gateways.

Add Data Source:

Under the installed gateway, click Add Data Source and provide the following details:

Data Source Name: A name for this data source (e.g., "On-Premises SQL Server").

Data Source Type: Select SQL Server.

Server and Database: Enter the SQL Server name and database you want to connect to.

Authentication Method: Use the authentication type for your SQL Server (e.g., Windows Authentication or SQL Authentication).

Credentials: Enter the credentials for accessing the SQL Server.

Test Connection:

Test the connection to ensure the gateway can communicate with the SQL Server.

Step 5: Connect Power BI Desktop to the Data Source

Open Power BI Desktop:

Click on Get Data > SQL Server.

Enter SQL Server Details:

Provide the SQL Server name and database.

Authentication Method:

Choose the same authentication method as configured in the gateway.

Publish the Report:

Once you have created your report in Power BI Desktop, publish it to Power BI Service.