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

      Member

      December 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

      Member

      December 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

      Member

      December 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

      Member

      December 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

      Member

      December 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

      Member

      December 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
      ——————————
      ——————————————-

    • Shawn Bucher

      Member

      January 28, 2020 at 12:30 PM

      Same issue for me. I see my gateway in Power BI, but not in Power Apps or Power Automate.

      ——————————
      Shawn
      ——————————
      ——————————————-

    • Frederick Luk

      Member

      January 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.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for peopleĀ  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!