none
Location of tnsnames.ora file for Oracle connection from Reporting Services 2008 R2 (x64) RRS feed

  • Question

  • After some effort (and with help from forum postings) I've succesfully installed SQL Server 2008 R2 Standard Edition (x64) Reporting Services and a suitable 64 bit Oracle 11g client on a Windows 2008 (64 bit) server.

    I can create a Shared Data Source connecting to Oracle using either type=Oracle or Type=OLE DB and Connection String with hard coded Data Source parameters:

    e.g.

    Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));

    However, I would normally just use Data Source = <database> and allow the Oracle client to find the correct TNS entry in the tnsnames.ora file. The location of this file is set in the TNS_NAMES entry in the registry. Unfortunately I can't get this method to work and the TNS not found error occurs. This applies whether I put the .ora file on the local drive, or on the network share where it normally goes (and set the registry entry)

    Does anyone know why this is happening and how to resolve? I wondered if the SSRS account which was running the reports was unable to see this location? I'm not sure how to find out which account is doing this.

    Any help appreciated

    Eric

     

    P.S. The post at http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f/ was of some help but not for this particular problem.

    Note that a cmd prompt sqlplus connection works succesfully using the tnsnames file

     

     


    Eric
    Tuesday, November 9, 2010 7:35 PM

All replies

  • Hello

    have you made sure your tns connection works outside of SSRS?

    try doing a tnsping and see the location used to resolve the alias

    also I've never had a problem with tnsnames when just using the default ../network/admin location within the Oracle Home directory, so maybe its worth a shot seeing if it works there.

    Tuesday, November 9, 2010 8:34 PM
  • If you look at the last line of my email, you will see I tested this using sqlplus to prove the TNS connection worked correctly. A tnsping also works.

    The reason we use a network version of tnsnames.ora is so that we can change databases and server locations globally across the company. If there is some other way fo solving this then it woudl be helpful to know

    Eric


    Eric
    Tuesday, November 9, 2010 8:49 PM
  • I've had a separate reply from the Oracle Developer Technical User Group. The Data Source name needs to be fully qualified as our tnsnames.ora file holds fully qualified names of the form <database>.domain

    So in the Shared Data Source

    Data Source=<database>.DNS

     

     


    Eric
    Wednesday, November 10, 2010 9:16 AM
  • you can create a dns entry for the oracle datasource. use net configuartion assistant to create new dsn.

    the respected entries must be in the tnsnames.ora file.

     

    Wednesday, November 10, 2010 10:57 AM
  • Hi Eric,

    The tnsnames.ora shoule be located under %ORACLE_HOME%\network\admin.

    %ORACLE_HOME% is like C:\OracleHome\product\11.2.0\client_1. This can be found from the Path System enviroment variable.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, November 11, 2010 2:00 AM
    Moderator
  • Yes, but you can also put it in other places, including network folders as long as you let the client software know you have done this. This is normally achieved using a registry key setting. The problem I have is that it works when tested with teh Oracle tols (for exampel SQL Plus) but not from a Shared Data Source in SSRS. 
    Eric
    Thursday, November 11, 2010 9:03 AM
  • Hi ,

     

    I had the same problem, I solved it assigning the reading permission on the oracle home folder to the reporting services' user.

    I assigned the read and execute permission, after that reporting services can reach the db with no more problem.

    I hope this help.

     

              regards

     

                  Alex

    Monday, September 19, 2011 11:52 AM
  • I was wondering if you ever found a resolution to your proble. I have installed the client Report Builder 3 and am unable to establish any conn to Oracle database running localy even (Express 10G).

     

    Gary


    Tilt User
    Sunday, February 5, 2012 6:01 PM
  • No, never found a solution other than using local copy of the tnsnames.ora file

    Eric

    Wednesday, February 8, 2012 3:19 PM
  • Many times the symptoms you describe are caused by lack of network rights.   That is, the Service is running under a local account  and is not authenticated thru the domain.  Not domain authenticated == no domain rights.  You can't use networked file shares as the account has absolutely no network rights.  It works when you use it via tnsping because your windows user is a domain account and has rights to the network share.   

    You looked like you were on to this with this" I wondered if the SSRS account which was running the reports was unable to see this location? I'm not sure how to find out which account is doing this. "

    You find out the account by looking at services in the "log on as"  column.  Make sure that file permissions are OK too.

    Tuesday, October 16, 2012 11:06 PM
  • Hi ,

     

    I had the same problem, I solved it assigning the reading permission on the oracle home folder to the reporting services' user.

    I assigned the read and execute permission, after that reporting services can reach the db with no more problem.

    I hope this help.

     

              regards

     

                  Alex

    This completely solved my problem that has been stumped for 6 hours. The issue in my case was having multiple client installs (10 and 11)... resetting permissions fixed it. Thanks!

    Friday, June 14, 2013 2:56 PM