Forum Discussion

mrbonvivant's avatar
mrbonvivant
Advocate I
8 years ago

Power BI Report Server Not Recognizing TNS Names File

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!

Report Server

8 Replies

  • mrbonvivant's avatar
    mrbonvivant
    Advocate I
    8 years ago

    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's avatar
      kskarthick
      Advocate I
      8 years ago

      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's avatar
        mrbonvivant
        Advocate I
        8 years ago

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

  • TDERBY's avatar
    TDERBY
    Frequent Visitor
    8 years ago

    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.

  • kskarthick's avatar
    kskarthick
    Advocate I
    8 years ago

    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.