Forum Discussion
Dynamically update Term store values based on SQL Server database updates
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.
8 Replies
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
- 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 ;).
- 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.Thank you, ChrisWebbTech (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
- 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.
