Gateway (timeout) issues when adding Oracle Datasource

[Mamuka]

Hi,

On-Premise Gateway won't work for Oracle Database. I get the following error:

Unable to connect: We encountered an error while trying to connect to CIFSFPRD. Details: "We could not register this data source for all gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details
Activity ID: b1c1dc71-b304-4b52-a650-1853be22620f
Request ID: 82392d09-52f6-c4aa-3907-e070ced326b8
Cluster URI: https://wabi-europe-north-b-redirect.analysis.windows.net
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Wed Dec 27 2017 23:40:05 GMT+0400 (Georgian Standard Time)
Version: 13.0.3495.209
PeopleSoft: Cannot connect to the mashup data source. See error details for more information.
Underlying error code: -2147467259
Underlying error message: Timeout expired. The timeout period elapsed prior to completion of the operation.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.HostingError.Reason: Timeout

Logs say the following: Microsoft.Data.Mashup.MashupHostingException (0x80004005). The log extracts are here:

Good news is that if I try to enter incorrect authentication details on the data source, then I get Invalid Credential Error and that tells me that the gateway talks to the database.

Can anyone help me to troubleshoot the issue and/or increase the timeout settings?

[v-qiuyu-msft]

Hi @Mamuka,

It seems error occurred when you created a data source under on-premise data gateway, right? Did you get data use Oracle data source or via ODBC driver? 

From the error log, I find error message: 

Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessHostingException: A problem hosting the mashup engine was detected. Reason: Timeout. Error code: -2147467259

I would suggest you contact your Oracle admin to increase query timeout value on Oracle side.

Also please check if the machine hosts the on-premise data gateway can access Oracle server. If there is a Oracle client installed in that machine, please test in Oracle client to get data from the same server to see if the issue occurs. 

If you have on-premise data gateway( personal mode), please configure the dataset use personal mode gateway then check if you can enter credential for data source successfully. 

Best Regards,
Qiuyun Yu 

[Mamuka]

@v-qiuyu-msft

Tanks for reply but:

1. I don't think the issue is at Oracle side as there's no such thing as query timout settings on Oracle database that I'm aware of. In addition, I've had queries run on that database (using SQL Developer) that takes few ours to complete without any issues;
2. I'm using Oracle Client not ODBC driver; I've followed the steps described on how to install necessary ODAC 64 bit;
3. Machine that the on-premise is installed can access the Oracle DB using the same TNS setting. In addition, as I mentioned in my original post, if I provide wrong user/password in on-premise data source setup than it gives me correct error (i.e. wrong credentials) which can only be if it can reach the database;
4. I don't use personal mode as I plan to share the gateway with users in my organization;
5. I modified <setting name="MashupDSRTestConnectionTimeout" serializeAs="String">
           <value>00:00:50</value> to 10:50 hoping that it will not timeout, but it had given me different error saying that connection was interrupted by user or timed out.

[v-qiuyu-msft]

Hi @Mamuka,

Would you please find on-premise data gateway log files around the issue occurs and share them with us? You can find log files follow this: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#tools-for-troubleshooting. You can upload log files to your Onedrive and send the link to me in private message. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi, 

Is there any update on the issue? I encounterd the same problem.

[mda]

I also have the same problem. But previously I successfully defined the Oracle data source, but to database on a different server. When I use the desktop on the machine where Gateway is installed it is connecting to the database.

And the same with invalid credentials - the error is showing immediately.

[mda]

I think that casue of the problem can be limited priviliges granted to the user which is used to log in to the database. Does Power BI executing any queries after connecting to the database?

[alessio_missio]

Hi all I can confirm that the issue is related to a query that Power BI executes.

On a Oracle database Power BI execute a query to gather all the info from Oracle catalog.

This is the query:

select OWNER, TABLE_NAME, TABLE_TYPE as TYPE
from all_catalog
where OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS')
and TABLE_TYPE != 'SYNONYM';

This query takes more then one minute to answer and on the gateway it goes in timeout.

Anybody has an idea on how to resolve this ?

Best regards

A.