Dynamically update Term store values based on SQL Server database updates

[mdcastorena]

Hi, I have a SQL Server database living on a virtual machine hosted in Azure and am trying to connect SharePoint to it so I can dynamically update the term store based on records added to a certain column in the database.

I figured I'd first try creating a PowerApp that can connect to the database and go from there, but I receive the below error when attempting to do so:

"ERROR -
We weren't able to add this connection. Please try again"

Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
clientRequestId: 95d81cbc-65c2-4d44-b3f0-02d36fe8224d

Session ID: 1a14ded6-57d1-41a4-8031-7531ec5f79d4"

Should I be logged directly into the VM to do this?? I've scoured Google/YouTube for help but feel I'm getting down a rabbit hole. What's your best suggestion at accomplishing this? I am sure there are several ways to do it.

Thank you in advance,
Morghan C.

[#2]

From what I recall when trying to do this, it wasn’t really possible. No connector etc. for powerapps to update the term store.

As for your error it’s most likely a firewall issue. Would have to allow office services to your azure sql server before powerapps is able to talk to it.

[#3]

@mdcastorena I was looking at how to do this recently and had a different approach that might be worth considering. Rather than using a PowerApp, use a script to read data from SQL and then update the Term Store using either the Graph API or PnP PowerShell

TermSets can be updated using Graph API Create term - Microsoft Graph v1.0 | Microsoft Docs

You can also use PowerShell to update the Term Store Set-PnPTerm | PnP PowerShell

[#4]

Nice. Guess you could technically build an app to do this in conjunction with some flows. Or create a custom connector as well.

Wish I had stumbled across this previously. I may have to go back and add something to my term store ;).

[#5]

Thank you, @Chris Webb (et all) -

"Would have to allow office services to your azure sql server before powerapps is able to talk to it."

How can I accomplish this? We'll want to wire up MS to this SQL DB wherever possible. 

Thank you,

Morghan

[#6]

@mdcastorena should have this in the sql server firewall settings

[#7]

It being an sql server and not azure sql database, it might also not have a public endpoint / name configured. I can't recall but it might require an agent just like on-prem etc. if you don't see anything obvious in the firewall settings etc.

[#8]

@Chris Webb I am missing this "Set server firewall" setting here when I am in the portal... Why would that be? The second screenshot is one where it does exist in a sample screenshot I found.... Is it because mine is a VM and not a SQL database within Azure?

My screen: 

Sample screenshot from the web:

Thank you again for your help!

Morghan

[#9]

Basically. You should have a set of azure network functions connecting you're VM's together etc. There should be something in those to allow Azure services in.