Connect Oracle with on-premise data gateway

I need to build an app with PowerApps with Oracle data source. Here we can find the official document explain how to manage your connection. As usual, Microsoft document looks like very detail but it always miss something or not up-to-date. Here is the action summary for your reference:

1. Install on-premise data gateway on your server (I call it gateway server). Very important notice: the server hostname should not contain special character like “_”. Because ODAC installation will not work. Refer to last section of this article.
2. Ask your Oracle DBA for DB information including:
   Server name or IP
   Port number
   Username/password
   Encrypt connection (yes/no)
   SID (Service name)
3. Make sure gateway server and Oracle server can communicate each other. They are not blocked by firewall.
4. Install Oracle Data Access Client (ODAC) on “gateway server”. It is the most tricky part. Check out the details in next section.
5. Login to https://make.powerapps.com/ and create a connection to thru your gateway to Oracle

Oracle Data Access Client (ODAC) installation

Microsoft told us download ODAC at Oracle site but didn’t tell you which one is required. Let me tell you ONLY version “64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64” is working. This ODAC come with windows installer. It will register the oracle drivers as well as register a TNSNAMES.ORA file. By default this file will be saved to the install directory’s \product\12.2.0\client_1\Network\Admin\ folder.

The install wizard will help you build TNSNAMES.ORA file according to parameters you input.

In the “Database Service Name” field, input the SID or Service Name provided by Oracle DBA.

Now you are ready to connect your gateway to Oracle database.

P.S. Refer to https://support.oracle.com/knowledge/Oracle%20Database%20Products/1957895_1.html: PRVG-11322: One or more node names “xxxx_powerapp” contains one or more of the following invalid characters “_”.