DO I need Visual studio and Power Query SDK to connect Power BI report to On-Premise SQL server???

[Anonymous]

I have published a report on PowerBI server, however it needs to connect to an on premise SQL server database. I read from documentation that I need to create a PowerQuery connector.  DO I need to have visual studio and Power Query SDK to have this connectivity ?? Are there any alternative methods to connect ? 

I feel like it is an overkill to Get a VIsual Studio License and install it, and Power Query SDK, just to connect a report to a SQL server. Ttere has to be a better way..

Any suggestions ? thank you.

[v-jingzhang]

Hi @Anonymous 

Have you solved this problem? If so, kindly Accept an appropriate reply as Solution. If not, please provide more details about the problem. Thanks.

Regards,
Community Support Team _ Jing

[Anonymous]

Hello @v-jingzhang

PREVIEW

Thank you for your answers on this. This has not worked for me yet. (the error messages from PowerBI are not very clear). Currently I am working with firewall team, powerbi admins and SQL admins in our Org to rule out any communication restrictions, and if there are restrictions then open up the communications between PowerBI VMs and SQL servers, and then try again. Once that is done, I will update the results here. 

[Anonymous]

I have read through the documents, and this is what I gather from documentation and watching some videos:

1. You have to download install the gateway. (which I already have done- On-premises data gateway)
2. Gateway is just a program, which uses the "connectors" folder, and looks for connectors in this folder. (gateway by itself will not connect).

3. In order to create a connector, you need to have "PowerQuery SDK", which is an add-on to the Visual Studio

4. You also have to use "M Language" to create a custom connector. The process involves creating the connector and doing a "Build", and once you build, you put those files in the "connectors" folder, which will be used by the gateway.

Let me know if I have got it wrong.

[v-jingzhang]

Hi @Anonymous 

It seems you referred to documents for custom connectors instead of documents for SQL Server Database connector. Power BI has a built-in connector for SQL Server, so you can connect to SQL Server databases easily.

You just need to install the on-premises data gateway. Then in Power BI Service, select Settings > Manage gateways, select your gateway and select Add data source. Then add the on-premises SQL Server DB as a data source. For detailed steps, you can read this article: Add or remove a gateway data source - Power BI | Microsoft Docs.

After you have added this data source on the gateway, go to Dataset Settings page and select your dataset. Expand Gateway connection section, turn on Use an On-premises or VNet data gateway option. Then select your gateway and map data sources in the dataset to corresponding data sources on that gateway. Apply the change. 

Now you should be able to refresh data from the on-premises SQL Server database. 

Please refer to below tutorials for SQL Server data sources. 

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs

Manage your data source - SQL - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KNP]

Unless I'm misunderstanding something, you don't need to create a connector, the gateway should do everything you need.

Once you publish the report to the service you need to go to the Dataset settings and map the dataset to the SQL environment.

You're looking for the Gateway connection settings...

[Anonymous]

My UI looks slightly different from the above. (I think it is able to identify the gateway running on my laptop, But I don't see the space for entering SQL server details, and I only see a delete icon under actions).

When I click on "Apply"  button, it gives me an error as below.  Do you think there may be firewall issues?

Something went wrong
Failed to use the selected gateway. Please make sure the gateway is up and running and try again.
Please try again later or contact support. If you contact support, please provide these details.

Activity ID: XXXXXXX-XXXX-XXXX-XXXX-xxxxxxxxxx
Request ID: XXXXXXX-XXXX-XXXX-XXXX-xxxxxxxxxx
Correlation ID: XXXXXXX-XXXX-XXXX-XXXX-xxxxxxxxxx
Status code: 400
Time: Thu Jan 20 2022 09:02:17 GMT-0600 (Central Standard Time)
Service version: 13.0.17535.35
Client version: 2201.3.09479-train
Cluster URI: https://wabi-us-north-central-h-primary-redirect.analysis.windows.net/

-----------------------------------------------------

Additionally, when I go to "Manage Gateways", I get the below response (even though my gateway is running):

[KNP]

You appear to have it installed in personal mode. That may be causing you the issue.

If you have the option to install it in standard mode, try that and see if you have better results.

https://powerbi.microsoft.com/en-us/gateway/

[Anonymous]

hello @KNP 

Thank you for your answers and suggestions on this issue. I am still working on this. I do not have permissions in my org to install Standard Mode, so  Currently I am working with powerbi admins. They have installed "Standard Mode" gateway on a virtual server. What I notice is that when I login with my "Pro" license, I see different set of options (I am not able to see or edit the connection string), and the PowerBI admins are able to see and edit the connection string).  Currently we are working to rule out firewall issues. Once that is done, I will retry and update the results here. 

[KNP]

I may be misunderstanding but I think all you need is to setup a Power BI Gateway.

See docs here as a starting point...

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem