cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Moving from External Content Types in on prem to Power Apps / SPO

Good morning, I need some assistance. Currently, I have a SharePoint 2013 Server where I have External Content Types using BDC Service App to pull data into a list from SQL View. Though my SQL is in Azure, it was a VM moved up and NOT an Azure SQL instance. So, I can't directly connect. I do have a Gateway set up and working for other Power Apps. 

 

What I am trying to accomplish is to move this over using the power platform. I want it in a list format though. I don't want a little mobile app with cards. I want to be able to view the data in a list in SharePoint. I am still trying to figure out if this is even possible. It must be. I would greatly appreciate any guidance that anyone can provide me.  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

  • If its Microsoft SQL server running on a VM then you would need an On-premises gateway in an Azure VM to be able to use Power Apps or Power Automate.  Neither will operate exactly like an external list.  But that will work for both on-premises and SharePoint online
  • I believe you are correct about external lists in SQL running on a VM in SharePoint online.  It will work with an Azure SQL database, but I've never had any luck getting read/write to work with a server.


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

9 REPLIES 9
Highlighted
Dual Super User III
Dual Super User III

If its a one-time migration you should be able to do it with Power Automate.  But if its an ongoing process then it would be better to stick with an External List and BDC.  Power Apps isn't really designed to show you that data in a list format like SharePoint.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

@Pstork1 , thank you for your quick reply. I can do that using a data gateway and BDC. For some reason I thought I wouldn't be able to do that. Is there an article that i can follow connecting SQL using the data gateway and using BDC?

Highlighted
Dual Super User III
Dual Super User III

Is this for SQL or MySQL?  They are two different products.

For SQL you wouldn't need the data gateway.  You should be able to connect directly to the SQL instance on a custom port through the NSG of a Public IP on the VM.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

This is SQL Server. I thought it wasn't allowed to connect to SharePoint Online to SQL Server unless you used the Gateway for security purposes? Do I have that wrong?

Highlighted
Dual Super User III
Dual Super User III

Sorry, we have some confusion going on here.  In your question you said you had SharePoint 2013.  That would be on-premises, not SharePoint online.  So if its an Azure SQL database and SharePoint Online then yes you can create an External List directly without a gateway.  I did it not too long ago.

 

But can you clarify exactly what you have and are trying to do.  The specifics make a difference.

  • Is it Azure SQL, SQL server running as an Azure VM, or MySQL running on a VM or just an Azure MySQL database?
  • Is it SharePoint 2013 or SharePoint online?
  • Do you need ReadWrite or just Read Only Access
  • Is it a one time migration or is the database live in Azure?

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

Yes, that is clearly my fault. I was not clear. I currently have them working in on premises on SP 2013 but i am trying to shutdown that 2013 server and move everything to SPO. So, I need to figure out how if I can get the SQL Server (on prem) data into a list in SharePoint Online.

Highlighted

But can you clarify exactly what you have and are trying to do.  The specifics make a difference.

  • Is it Azure SQL, SQL server running as an Azure VM, or MySQL running on a VM or just an Azure MySQL database? 
    • SQL Server running as an Azure VM
  • Is it SharePoint 2013 or SharePoint online?
    • Currently in 2013, would like to move to SPO.
  • Do you need ReadWrite or just Read Only Access
    • ReadWrite
  • Is it a one time migration or is the database live in Azure?
    • Live data updating daily.
Highlighted
Dual Super User III
Dual Super User III

  • If its Microsoft SQL server running on a VM then you would need an On-premises gateway in an Azure VM to be able to use Power Apps or Power Automate.  Neither will operate exactly like an external list.  But that will work for both on-premises and SharePoint online
  • I believe you are correct about external lists in SQL running on a VM in SharePoint online.  It will work with an Azure SQL database, but I've never had any luck getting read/write to work with a server.


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted

@Pstork1 , I guess that I will have to get MS involved. I am not sure what to do at this point. 

 

Thanks so much for your thoughts on this.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,708)