Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

Using Oracle 19c ODAC for On-Premise Gateway

I have seen lots of links about how to get oracle data source working and everything points back to using the 12.1c installer as it has the option to default a machine wide config for .NET. This is great unless you want to use the newer ODAC installers for 12.2 and above where oracle has removed the option from the installer to do machine wide configuration.


So here is what is needed after an install of the newer ODAC x64 clients:


Create a config.bat file under the following path <oracleroot>\product\19.0.0\<oraclehome>\\bin\4

add the following to that bat file


.\OraProvCfg /action:gac /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:config /product:odp /component:oraclepermission /frameworkversion:v4.0.30319 /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:register /product:odp /component:perfcounter /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\\bin\4\Oracle.DataAccess.dll"



find and replace your Oracle Root directory for <oracleroot> and Oracle Home folder for <oraclehome> in that config.bat


Run the config.bat as administrator

The script will register the DataAccess dll in the GAC and add entries to the machine.config file for datafactory and providers


Next we need to make sure that the On-Premise Gateway has rights to read the tnsnames.ora file

We will add the Service Account that your data gateway is configured with to the ORA_<oraclehome>_SVCSIDS local windows group


By default the account you add would be "NT Service\PBIEgwService" from the local computer. If you are using a custom domain account you would use that instead.


Once this is completed and you have put a tnsnames.ora file in the "<oracleroot>\product\19.0.0\<oraclehome>\Network\Admin" you can now restart the On-Premise DataGateway Service through powershell / services.msc / sc.exe / Gateway UI


Restart-Service -Name PBIEgwService



After all this the data gateway should be able to use the installed ODAC driver.

Rinse and Repeat if you have a cluster of Data Gateways.


Hope this is helpful for others running into the same issues as I did and finding the documentation lacking by MS and Oracle.

Regular Visitor

One option is to use Environment variable of "TNS_ADMIN" and point it to a UNC path to which you host / update the TNSNAMES.ora file. I don't know what would happen though if there were any network disruptions and the gateway servers couldn't access the remote location. I would guess you would get intermittant failures which may not be acceptible. 

@cgroeneveld   I think if the gateway cluster members cannot see the local network then they cannot see the on premise Oracle databases either. So this would be an acceptable limitation. Thank you for the idea.

Super User
Super User

Interesting.  Our 12.2 installer still allows for machine wide .Net configuration. We always checked that thoughtlessly, I didn't know it would actually make a difference.


Having to maintain a TNSNAMES.ORA file across dozens of gateway cluster members is a royal pain though.  Any thoughts on how to improve that?

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors