Forum Discussion

Hussein_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago

Connect PBI to on premise sql server

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, ...
Need Help
Show and Tell
Tips and Tricks
rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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's avatar
    Hussein_charif
    Icon for Helper V rankHelper V
    1 year ago

    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's avatar
      Hussein_charif
      Icon for Helper V rankHelper V
      1 year ago

      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.