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, 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.

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • 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.

  • anmolmalviya05's avatar
    anmolmalviya05
    Icon for Super User rankSuper User
    1 year ago

    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.