Connect PBI to on premise sql server

[Hussein_charif]

I have an sql server that i am trying to connect to, the server is on a on premise device and i am using rdp to login to that device. 
i want to get data from that sql server to PBI on my own user, but i get an error when i put the credentials of the sql server. so i read that i need to download a gateway to be able to set things up. i downloaded the on premises data gateway on both my laptop and the server that has the SQL server. what should i do next? if someone can provide in details the steps i should, im new to this whole on premise gateway.

[anmolmalviya05]

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.

[rajendraongole1]

Hi @Hussein_charif -Install the On-Premises Data Gateway only on the machine hosting SQL Server or a machine that has access to the SQL Server.

• Open the On-Premises Data Gateway app on the SQL Server machine.
• Log in with the same Microsoft account that you use for Power BI Service.If this is the first gateway, choose "Register a new gateway" and give it a descriptive name (e.g., "SQLServerGateway").If one already exists, choose "Migrate, restore, or take over an existing gateway".
• Make sure the server allows outbound communication on these ports:
• 443 (HTTPS) for Power BI Service.
• The gateway communicates securely with Power BI Service.

https://app.powerbi.com/

Click the gear icon in the top-right corner of Power BI Service and select Manage Gateways.
Add a New Data Source:

Under the newly registered gateway, click Add Data Source.
Configure the following:
Data Source Type: Choose SQL Server.
Server Name: Enter the SQL Server name as configured (e.g., SERVERNAME\INSTANCE).
Database Name: Specify the database you want to connect to.
Authentication Method: Choose the correct authentication method (e.g., Windows Authentication or SQL Server Authentication).
Use credentials that have access to the SQL Server.
Test the connection.
Name the Data Source:

Give the data source a name that matches its purpose (e.g., "SalesDatabase").

In the dataset's settings in Power BI Service, configure Scheduled Refresh to automatically update your data.

Hope the above in detailed steps helps you to configure the same.

[Hussein_charif]

hello so, i did all that, i can also see my gateway name in the power bi service, but i dont have the option to "Add Data Source".

here is what i have :

as mentioned, the gateway was made successfully, and i logged in with the same account of the gateway on pbi service and the gateway is there and the status says "Online",but i can find the add data source to add my sql server database.

[Hussein_charif]

and also concerning power bi desktop, i got the following error:

Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
i clicked on get data > sql server database > entered the server name and the database name. 

and i am logged in with the same account as the gateway's.

[Anonymous]

Hi @Hussein_charif ,

It typically indicates that the client cannot find the SQL Server instance.It has detailed troubleshooting for this error.You can refer to:
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified | Microsoft Community H...

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.