Getting data via direct access into Power BI using an On-Premise Gateway

[dahmhenderson]

We have created a new Power BI On-Premise gateway server for ClickHouse testing. We installed the ClickHouse ODBC drivers and using PowerBI Desktop on the gateway machine we can get direct query to work after placing the custom connector in the "...Power BI Desktop\Custom Connectors" directory.

We now want to publish the report to Power BI Online but are unable to get the custom connector to work. Following some other instructions from Microsoft we copied the custom connector into the "C:\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors" directory and loaded it onto the gateway but when we try to connect to ClickHouseODBC on the PowerBI gateway server from PowerBI Online we get an error message that we don't have any access? Has anyone else gotten it to work?
In summary we want to run a report in PowerBI Online using a Direct Query connection back to our On-Premise ClickHouse database via an On-Premise PowerBI Gateway. A similar things works with Hadoop ;)

[belyaevvo]

Do you install clickhouse odbc drivers on the gateway server? Please note, you need to restart gateway service after coping connector and odbc installation пт, 10 нояб. 2023 г., 18:10 Stephen Henderson ***@***.***>:

…

We have created a new Power BI On-Premise gateway server for ClickHouse testing. We installed the ClickHouse ODBC drivers and using PowerBI Desktop on the gateway machine we can get direct query to work after placing the custom connector in the "...Power BI Desktop\Custom Connectors" directory. We now want to publish the report to Power BI Online but are unable to get the custom connector to work. Following some other instructions from Microsoft we copied the custom connector into the "C:\Windows\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors" directory and loaded it onto the gateway but when we try to connect to ClickHouseODBC on the PowerBI gateway server from PowerBI Online we get an error message that we don't have any access? Has anyone else gotten it to work? In summary we want to run a report in PowerBI Online using a Direct Query connection back to our On-Premise ClickHouse database via an On-Premise PowerBI Gateway. A similar things works with Hadoop ;) — Reply to this email directly, view it on GitHub <#6>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AD5DYXX63D24TF2NWOO7ENDYDYRSVAVCNFSM6AAAAAA7GE74DSVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZVHAZTMMRQGA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[dahmhenderson]

Hello, thanks for your response. Yes I did install the drivers on the gateway and restart the service.
The issue we have is that to get the Direct Query to work on PowerBI Desktop we need to use the ClickHouseConnectotor (Beta) locally as this is the only one which allows direct access. Even this needs renewed authentication and URL when configuring. The moment we publish the report to Power BI online the connection string cannot be added to the gateway as it says that we have no access to the ClickHouse database.
Simply what we are looking for is a way to get Direct Query to an on-premise ClickHouse database from PowerBI Online using the PowerBI Gateway.

[belyaevvo]

I think this connector supports PowerBI on-premises gateway. As Microsoft's documentation says, the only thing that needs to be implemented compared to PowerBI Desktop is "For custom connectors to work with the on-premises data gateway, they need to implement the TestConnection section in the custom connector code." And I implemented it.

Please note that this document contains some limitations and considerations:
https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors

As I remembered, I'd test connector in Gateway Scenario. So if you have any errors, please send me the error message you received.
I'll try to repeat Gateway test on Personal Gateway.

[dahmhenderson]

Hello, the error message I get is attached but I don't think it is actually telling the whole truth as the message never changes even if I use an incorrect URL or IP address. I have tried to look at the log files on the gateway but have not found anything. According to Google that is not unusual as even with the extended logs not everything is visible.

What I don't understand is why I need to type in the database information, username and password in to the connection config online as what we normally do is to choose anonymous, as the credentials etc. are already typed into the ODBC drive on the gateway?

[dahmhenderson]

Hello, the error message I get is attached but I don't think it is actually telling the whole truth as the message never changes even if I use an incorrect URL or IP address. I have tried to look at the log files on the gateway but have not found anything. According to Google that is not unusual as even with the extended logs not everything is visible. What I don't understand is why I need to type in the database information, username and password in to the connection config online as what we normally do is to choose anonymous, as the credentials etc. are already typed into the ODBC drive on the gateway? I have just reinstalled the Gateway and get the same issue, I can choose ClickHouseODBC as a Connection Type so the injection of the driver gets replicated online but saving gives the error message above.  Any help you can give would be gratefully appreciated? Regards, Stephen Henderson

…

On 24 Nov 2023, at 11:26, Vladislav Belyaev ***@***.***> wrote: I think this connector supports PowerBI on-premises gateway. As Microsoft's documentation says, the only thing that needs to be implemented compared to PowerBI Desktop is "For custom connectors to work with the on-premises data gateway, they need to implement the TestConnection section in the custom connector code." And I implemented it. Please note that this document contains some limitations and considerations: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors As I remembered, I'd test connector in Gateway Scenario. So if you have any errors, please send me the error message you received. I'll try to repeat Gateway test on Personal Gateway. — Reply to this email directly, view it on GitHub <#6 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANEICIB4J3VQXCXG5ZH7WOTYGBY4RAVCNFSM6AAAAAA7GE74DSVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TMNJZGI3DI>. You are receiving this because you authored the thread.