Solved: Gateway connection to Oracle (TNS:listener error)

ChickenWaffles · ‎11-26-2019

I'm trying to setup a on-premiss Gateway server. I've got a few other database types connected to the Gateway, but I get an error when trying to connect to my Oracle database. I get a error saying:

Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

To try and trouble shoot I installed powerBI desktop on the same machine and was able to connect to the Oracle. So I don't think this is a firewall or conectivity issue.

The only other thing I can think of is my account has an enviromental variable set for (TNS_ADMIN). Looking at the logs the gateway is using the NT SERVICE\PBIEgwService, but I can't see where that account is expecting to find the *tnsnames.ora* file.

1. How do I find where the Gateway is attempting to get the TNS data from?
2. Is there a way to overwrite/place the ora file being refferanced?

I saw some guides recomending changing the user from the Default account to a Domain account (to solve permissions issues) but they also caused other issues.

ChickenWaffles · ‎12-02-2019

@GilbertQ and @v-xicai ,

According to both of the links you have provided I should be selecting a "Direct Connect" checkbox to make sure this is not available. It looks like this is a Power BI Desktop option only, and not available in the gatway configuration.

I still think there is a good posibility that the system user is not looking at the correct tnsnames.ora file, this was something that happened in the past, but I can't confirm this. Is there any way to confirm what file is being used by the gateway?

UPDATE 1:
I changed the service settings on the Gateway to use my Domain account, originaly configured with the default NT SERVICE/ PBIEgwService. This way the gateway app should pick up on the same TNS file I used, now the connection appears to work.

UNDERLINING CAUSE:

My best guess in my case is that because we use an Enviromental variable for TNS_ADMIN to point to a network drive which contains TNS Listener. The default service account either was refferancing a different file, I cound not locate and edit, or permissions to that share was blocked when that account was used. Running the service under a "User" account allowed permissions and variable to be set.

Full error reported after error connecting.

View solution in original post

ChickenWaffles · ‎12-02-2019

@GilbertQ and @v-xicai ,

According to both of the links you have provided I should be selecting a "Direct Connect" checkbox to make sure this is not available. It looks like this is a Power BI Desktop option only, and not available in the gatway configuration.

I still think there is a good posibility that the system user is not looking at the correct tnsnames.ora file, this was something that happened in the past, but I can't confirm this. Is there any way to confirm what file is being used by the gateway?

UPDATE 1:
I changed the service settings on the Gateway to use my Domain account, originaly configured with the default NT SERVICE/ PBIEgwService. This way the gateway app should pick up on the same TNS file I used, now the connection appears to work.

UNDERLINING CAUSE:

My best guess in my case is that because we use an Enviromental variable for TNS_ADMIN to point to a network drive which contains TNS Listener. The default service account either was refferancing a different file, I cound not locate and edit, or permissions to that share was blocked when that account was used. Running the service under a "User" account allowed permissions and variable to be set.

Full error reported after error connecting.