question

GeneMcKelvey avatar image
0 Votes"
GeneMcKelvey asked CaseyYangMSFT-4714 commented

SPO - BCS to on-premises SQL Server via PBI Gateway

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!

sql-server-generaloffice-sharepoint-online
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CaseyYangMSFT-4714 avatar image
0 Votes"
CaseyYangMSFT-4714 answered

Hi @GeneMcKelvey,

Per my research, You could create an External List by using Business Connectivity Services (BCS) and Secure Store to access data from a Microsoft SQL Azure database. BCS connects SharePoint solutions to external data, and Secure Store enables user authentication for the data.

1.Create a Business Connectivity Service
SharePoint admin center > Active sites page > More features > BCS > Manage connections to online services
2.Create a Secure Store Service ID
SharePoint admin center > Active sites page > More features > Secure store > New
3.Create the External Content Type
SharePoint designer 2013 > External Content Type > Add Connection
4.Adding external list

You could get more details from the following link. It is a article describes how to configure the SharePoint Online Integration with Azure SQL Server step by step. Hope it will help you.
https://social.technet.microsoft.com/wiki/contents/articles/28286.office-365-sharepoint-online-bcs-with-azure-sql-server-database.aspx?PageIndex=2


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeneMcKelvey avatar image
0 Votes"
GeneMcKelvey answered CaseyYangMSFT-4714 commented

Thanks, @CaseyYangMSFT-4714 .

However, the requirement is there for On-Prem SQL Server only, not having the data pushed out to an Azure SQL Database.

Thanks again! :)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for reply, I will continu on this issue, if I have any idea I will let you know.

0 Votes 0 ·