Forum Discussion
DO I need Visual studio and Power Query SDK to connect Power BI report to On-Premise SQL server???
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.
- KNP
Super User
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...
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/
v-jingzhangCommunity Support
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.
