Forum Discussion

roedeske's avatar
roedeske
Icon for Advocate I rankAdvocate I
10 years ago
Solved

How to use Enterprise Gateway (SQL) Datasource within Designer?

Hi there,

 

I installed the latest Enterprise Gateway for SQL on-prem, I create a Gateway and setup a Datasource. Copnnection is telling status ok, and now the big question, how do I connect to that data source from within the Designer or also the PowerBI Web portal, I have no clue and did not find anything in the documentations. Can anyone help?

 

Regards,

Michael

Need Help
  • roedeske's avatar
    roedeske
    10 years ago

    Hi together,

     

    I tried as you were telling and had success. It's just so simple.

     

    1. Install Enterprise Gateway

    2. Setup Gateway

    3. Connect within local Designer to local SQL Server source

    4. Choose DirectConnect

    5. Create report

    6. Publish report

    7. Open Report in PowerBI ... the Gateway just got used, no configuration needed, it seems that just all gets routed by teh gateway.

     

     

18 Replies

  • dhrot's avatar
    dhrot
    New Member
    10 years ago

    Hi

    just wondering on the naming requirements for the gateway, datasource etc.   What are the hooks for getting this working.   Do we need to name the datasource the same as the powerbi datasource, how does it find the gateway?

     

    I would have thought i would just select the gateway as a datasource?

     

    Thanks in advance.

    Dave

     

     

    • dhrot's avatar
      dhrot
      New Member
      10 years ago

      In reply to my own query, it seems you need to have the PowerBI Desktop installed on the server and then publish from where the gateway is also installed and it just seems to pickup on the datasource being able to live query.

       

      Thanks, look forward to being able to select gateway datasources in PowerBI and Excel directly.

       

       

    • Dodge1350's avatar
      Dodge1350
      Regular Visitor
      10 years ago

      Same here Dave, folks are stating that they can connect by give no examples of the details they are entering in for credentials to gain access... I don't want to see the exact credentials, but if there is something that has to be added to the server name or anything weird that isn't straightforward, I'd want to see the explicit example.  I would have thought that you simply connected to the gateway and selected the data source from there, strange it works otherwise.  How does it know how to transfer the call, or even intercept the call for the sql server instance?  Anyone got a clue?  Still haven't been able to use a SQL Server DirectQuery datasource through the Gateway even though it is configured and the data source is showing that it is ok in the configuration.  Any ideas?

  • Bjoern's avatar
    Bjoern
    Icon for Continued Contributor rankContinued Contributor
    10 years ago

    For PBI WebService:

    Get data -> SQL Server database -> it will search within your organization -> connect should do the trick (I have not tested it my self, but it works exactly like this for the AS Tabular Connector).

     

    For PBI Designer:

    Get data -> enter server name (please make sure that you are in the same domain etc., no firewall restrictions etc.).

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Bjoern When I go to Get Data from the Web Service, I don't see SQL Server database at all, just Azure SQL + SSAS on prem via the tabular connector.

       

      In Desktop, I created a Get Data > SQL Server, connected in fine, built a simple report and published out to my PowerBI.com account.

       

      When I go to PowerBI.com and try to schedule refresh, it says I need to install the Personal Gateway. So it's like it's ignoring that I have an Enterprise Gateway installed and configured with that particular data source.

       

      So there's some disconnect here between creating a gateway data source and having PowerBI desktop "know" that the connection in it is configured in that data source so it can use that gateway to handle the refresh.

       

      Anyone who can point out how to connect the Enterprise Gateway data source with an actual data source in a PBIX file?

       

      • ashishrj's avatar
        ashishrj
        Icon for Power Participant rankPower Participant
        10 years ago

        Anonymous Bjoern I have tested Enterprise gateway and works fine. Please note currently Enterprise Gateway only supports live connections to on-premises SQL Server databases using DirectQuery.

    • roedeske's avatar
      roedeske
      Icon for Advocate I rankAdvocate I
      10 years ago

      Dear Bjoern,

       

      the SQL Server connected to the gateway is not within the Power BI Account Domain, so name resolution within network will never work. But in my understandig this is why there is this kind of gateway.

       

      Regards,

      Michael

      • Bjoern's avatar
        Bjoern
        Icon for Continued Contributor rankContinued Contributor
        10 years ago

        roedeske Erm, please correct me, if I am wrong. But if you would be able to share the source outside your organization, this would be a serious security threat. In my opinion the gateway is used with a Power BI account to publish the data (same like the SSAS connector) into the web-service, where it can be used by PBI accounts of your own organization.