Power BI Report Server Not Recognizing TNS Names File

[mrbonvivant]

On the report server I have the 64-bit Oracle database client installed and I have the TNSNames.ora file updated correctly.  I have the SQL Server instance installed on the same machine and can create a linked server on the SQL instane using the alias from the TNS file.  I can also connect using SQL Developer on the host machine utilizing the TNS alias.  When I try to create a data source in the web portal using the TNS alias I get a TNS error indicating it doesn't recognize the TNS name.  I have rebooted the server to no avail.  I can successfully create and connect to an Oracle data source in Power BI Report Server using the full TNS entry.  Honestly, I wouldn't mind using the full TNS entry, but Power BI Desktop only allows 128 characters for the "Server Name" for an Oracle data source and I can't edit the data source in the web portal for a given Power BI report.

My current workaround is to use a SQL Server connection and use OPENQUERY with the linked server.  This does work, but this is somewhat unreasonable.

Help!

[kskarthick]

Same issue i also got, i am not getting any solution.

Guys please can you post the workaround solution step by step, i tried i am not getting steps to solve issue .

If anyone gives perfect solution also good for me.

[TDERBY]

I am having the exact same problem with the Power BI Server. I have tried multiple ODAC versions and all seemd to be working well to connect to Oracle. I can use sqlplus to connect from a command prompt using the tnsnames.ora alias without a problem. I can create an ODBC connection and use that. But I can not create an Power BI Server Data Source object using Oracle Database type and the connection string below.

Data Source="MyOracleDB"

This same technique works fine in my 2016 SSRS service.

I am using Version 1.2.6648.38132 (March 2018) of the Power BI Server and ODAC 12.1.0.2.4. Both are 64 bit versions running on Windows 2012 R2.

I was able to connect using both work arounds in the posts above.

[mrbonvivant]

I figured out a more viable workaround since I couldn't get the TNS alias to work.  I used the format of HOST":"PORT"/"SERVICE_NAME for the Oracle database and it worked!  Luckily this is short enough to use in Power BI Desktop.  Hopefully this helps someone out.

[kskarthick]

Hi mrbonvivant,

I am new to oracle, Could u please explain step by step how you achived this workaround.

i installed oracle client in my system, i tested TNS connection in my system with help of sqlplus, it show connected.

same server is connected in Power BI desktop.

[mrbonvivant]

From your example the connection string should be formatted as: Data Source="192.168.12.44:1521/ISPL11GDB".

[kskarthick]

Hi mrbonvivant,

Thank you, its solved my issue while creating new datasource.

For existing report default it took the Connection string from which i develop in Power BI that i can't change?

But this is workaround you mentioned, because of this we will face any issue(like performance or ect..).

[mrbonvivant]

Correct - you can't change the connection string on the report sever once it has been sent from Power BI Desktop.  You will need to change the connection string in Power BI Desktop then republish the report.

This connection string naming convention is actually a better option than using the full TNS name.  You will acheive the same performance.

[amitsankar]

I am having same issue. I have used the same format what you mentioned earlier. But i am getting same error.

Oracle: ORA-12154: TNS:could not resolve the connect identifier specified