DirectQuery Oracle database connection issues

[andershh]

Hi everyone,

I have some issues with using DirectQuery for some reports I have uploaded to the PowerBI service, that I hope some of you out there can help me with. Thanks for reading this!

So my problem is this: I have made a report in PowerBI desktop that uses DirectQuery to access data from an Oracle database. In PowerBI desktop I have no issues with connecting to the database and having my visuals being updated live. However, as soon as I upload the report to the PowerBI website/service and open the report there it can't display the visuals. I am getting this error message:

"We reached the data gateway, but the gateway can't access the on-premises data source. Please try again later or contact support. If you contact support, please provide these details.

Error Code DM_GWPipeline_Gateway_DataSourceAccessError

Activity ID 5c70c151-aa06-7896-7974-4d80b51ce054

Correlation ID 1d7e09bd-7f06-e8b2-dded-9663a457b6de

Request ID 2db9f207-f9bf-c5cd-7160-716767db0f01

Time Fri Aug 26 2016 10:14:03 GMT+0200 (Rom, sommertid)

Version 13.0.1605.376

Cluster URI https://wabi-north-europe-redirect.analysis.windows.net"

The report is configured to use an On-premises data gateway to access the database. The weird thing though is that I am using the same gateway for other reports, where viewing them online at the PowerBI website works fine. The only difference being that the reports that work are configured to Import data instead of using DirectQuery. The table I am accessing using DirectQuery is very large, thus switching to Importing the data is not a viable solution. 

I have tried looking at the On-premises data gateway documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/ and https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-manage-oracle/), but haven't found a solution there. 

Does any of you out there know how to fix this? Or have any suggestions as to what the issue might be caused by?

Many thanks beforehand!

[ankitpatira]

@andershh check out this blog post and scroll to the end where there is a solution provided to the error message that you're getting. It talks about ssas but maybe it also works in your case of Oracle.

[andershh]

@ankitpatira - After taking a look at the troubleshooting guide you linked to, I am almost certain that the issue is due to my firewall blocking some of the ports that the on-premises gateway needs in order to communicate with the PowerBI service. Will mark your post as a solution if testing connection with new firewall settings shows that it works. Thanks for sharing the troubleshooting guide!

@dimazaid I have tried looking at the log area for the gateway (as specified in the troubleshooting), but there are no logs there. Thanks for the suggestion!

[andershh]

After opening the ports specified in the troubleshooting guide linked to by @ankitpatira the problem still persists.

However, @dimazaid I was able to find some log files here: C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log 

Looking at the latest log file it appears this is the main error causing the issue:

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: ArgumentException:<pi>System.ArgumentException: 'integrated security' is an invalid connection string attribute
ved Oracle.DataAccess.Client.OracleConnection.ParseConnectionString()
ved Oracle.DataAccess.Client.OracleConnection.set_ConnectionString(String value)
ved Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.OracleConnectionProvider.<OpenConnectionAsync>d__0.MoveNext()</pi>

@dimazaid or @ankitpatira - do you know what might be cause this issue?

[dimazaid]

@andershh,

Thanks for checking the logs. 
Will take a look and get back to you.
Thanks!

[dimazaid]

The log files will contain more info, can you take a look and see what the error is?