Tuesday, November 09, 2010 7:35 PM
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:
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
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
Tuesday, November 09, 2010 8:34 PM
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 09, 2010 8:49 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
Wednesday, November 10, 2010 9:16 AM
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
Wednesday, November 10, 2010 10:57 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.
Thursday, November 11, 2010 2:00 AMModerator
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.
Jin Chen - MSFT
Thursday, November 11, 2010 9:03 AMYes, 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.
Monday, September 19, 2011 11:52 AM
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.
Sunday, February 05, 2012 6:01 PM
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).
Wednesday, February 08, 2012 3:19 PMNo, never found a solution other than using local copy of the tnsnames.ora file
Tuesday, October 16, 2012 11:06 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.