How to connect to on premises SQL server via data gateway ?
-
How to connect to on premises SQL server via data gateway ?
Posted by DSC Communities on December 13, 2019 at 4:28 pm-
Frederick Luk
MemberDecember 13, 2019 at 4:28 PM
Hi all,I have a database on my on-premises SQL Server. I wanted to use the MS
data gateway to retrieve and manipulate data via a PowerApps app (or
Power BI).I followed instructions in the following link and installed the gateway
and got to the screen as shown in the attached screenshot.https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install
But when I tried to get to the gateway in make.powerapps.com, it says
there is no gateway installed and asked me to install one. When I
downloaded the installer and try to install, it says I already have
gateway in my system and wanted me to uninstall the previous one if I
want to continue to install.So, how does this gateway work ? Right now I cannot get to the gateway
in my PowerApps. Even if I can run the gateway, how do I connect to the
SQL Server database via the gateway ?I did some searching but came up empty in terms of how to actually use
the gateway to connect to SQL server and other data sources.Please help.
Thanks a million,
Fred
-
Ali Hoolash
MemberDecember 16, 2019 at 1:49 AM
You can set up a Power BI gateway connection in Personal mode or Enterprise mode.These 2 videos might help.
Personal mode: How to Set Up Power BI Gateway to Connect to On Premise Data in Personal Mode
Enterprise Mode: How to Set Up Power BI Gateway to Connect to On-Premise Data in Enterprise Mode
——————————
Ali Hoolash
1111111111111
——————————
——————————————- -
Frederick Luk
MemberDecember 17, 2019 at 4:16 AM
Hi Ali,Thank you for sharing the video. It was very helpful. I was able to add my on premises SQL server database toĀ the gateway in Power BI service.
However, in PowerApps, it still is not working. I am able to see the gateway in the Admin center in admin.powerplatform.microsoft.com. But I don’t see the gateway in PowerApps studio. I suppose I don’t have to add my database in the on-prem SQL server again. How can I make my database available as a data source in PowerApps ?
Thanks a million,
Fred——————————
Frederick Luk
President
Santa Clara CA
4088001224
——————————
——————————————- -
Sarvesh Shinde
MemberDecember 17, 2019 at 2:26 AM
Are using same account to login at PowerApps, which you have used while installing the gateway?
Cross check this one more time, If not then install the gateway with same email which you are using for PowerApps.——————————
Sarvesh Shinde
Senior SharePoint Consultant
——————————
——————————————- -
Frederick Luk
MemberDecember 17, 2019 at 1:53 PM
HiĀ Sarvesh,ĀI am using the same account to log in to PowerApps, Power BI and install the gateway. I was able to see the gateway in Power BI and added the SQL Server database. I was able to see the gateway in PowerApps’ Admin center, but I couldn’t see the gateway and database in PowerApps studio and web.powerapps.com.
In PowerApps Admin center, there are only two things icons/buttons available, i.e. “open gateway cluster details” and “Manage gateway users”. There is nothing that tells me how to make the gateway available in PowerApps studio.Please advise.
Thanks in advance,
Fred——————————
Frederick Luk
President
Santa Clara CA
4088001224
——————————
——————————————- -
Frederick Luk
MemberDecember 24, 2019 at 3:36 PM
FYI, I contacted Microsoft support regarding this issue last week. We had a session via Teams. So far, they can only tell me that they are still investigating this issue and no explanation or solution yet. I was wondering if other users have encountered the same problem.Sincerely,
Fred——————————
Frederick Luk
President
Santa Clara CA
4088001224
——————————
——————————————- -
Same issue for me. I see my gateway in Power BI, but not in Power Apps or Power Automate.
——————————
Shawn
——————————
——————————————- -
Frederick Luk
MemberJanuary 28, 2020 at 1:53 PM
It turns out that you have to create a separate gateway in the East region for PowerApps and Power Automate. In other words, Power BI and PowerApps/PowerAutomate do not share the same gateway and they run in different regions.Hope this helps.
Cheers,
Fred——————————
Frederick Luk
President
Santa Clara CA
4088001224
——————————
——————————————-
DSC Communities replied 5 years, 8 months ago 1 Member · 0 Replies -
-
0 Replies
Sorry, there were no replies found.
The discussion ‘How to connect to on premises SQL server via data gateway ?’ is closed to new replies.