On premise Data Gateway to access SQL data on a separate server

Hi Anonymous 

No, you don't need custom connectors.

You install the Gateway on Server2 then from Power BI service go to "Settings" then "Manage Gateways". There you can select your Gateway and add a datasource to it. The datasource type will be SQL Server. The datasource entry in the Gateway on Server2 will hold the Server1 name, Database and authentication (login) details. That provides a path through the Gateway on Server2 to the actual Database on Server1.

The issue is that if you use Power BI Desktop (which you need to to build a Report based on the SQL database). You can't connect to a Gateway in Power BI Desktop, you can only connect to datasources you have access to on Desktop and you can't access Server1, so you can't get at the data.  

The way around is to use Dataflows (In the Power BI Cloud) which DO allow access through a Gateway. So you create Dataflows in the Power BI cloud from data on Server1. That will effectively copy data from the Database on Server1 into the Cloud. Then Power BI Desktop can access the Dataflows created, and build Report visualisations based on them.

See:

On-premises data gateway - Power BI | Microsoft Docs

Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

Hope this helps

Stuart