DETAILED SCENARIO:
Organization with data housed in secure database (proprietary data store). Certain parts that are not confidential are regularly exported to a XLSX file for loading into a SQL Server 2017 database (I will call this "The Data"). Client is currently using PowerBI for a lot of their work, and for their work they have a PowerBI Gateway established. This is the only APPROVED way to take data that is on prem and make it available outside. Communication to the SQL Server from the outside MUST use the PBI Gateway - no other mechanisms or technologies are allowed.
DESIRED OUTCOME:
Organization currently has several Microsoft 365 Subscriptions. Their desire is to make The Data available to BCS in SharePoint Online. Hybrid SharePoint is not an option.
WHAT I'VE TRIED (I by no means are stuck on this, so if I need to correct it, please let me know :) ):
First, I'm not a developer, but I have done a ton of SharePoint, SQL, and other Azure and Microsoft 365 services.
Thusfar, I've setup an Azure Relay to tie to On-Prem PBI Gateway - and that works. But, I'm at a loss of making the tie in from BCS (which can use a WCF connection - remember, I'm not Dev :) ) to Azure Relay. When setting up the connection from BCS to a data source, I'm prompted for a URL to a tie to that ends with ?WSDL, which I believe means it's a SOAP connection - which I found I can setup using Azure API Connections.
Anywho... I see that there are parts that CAN talk, but I'm not sure HOW to make them talk...
MY NEED
Please help me see how to make BCS communicate to an on-prem SQL Server via a PBI Gateway.
Thanks so much in advance!