February 10, 2015 at 1:11 pm
Hello
I'm using SSIS 2012, oracle 12 client on Windows 2012. We created a simple package that only attempts to connect to Oracle. It works for me and anybody else who is local admin on the Windows server. The problem occurs when we execute the package from an AD account coming from a 3rd party scheduling tool on a seperate server. When executing the package throught the scheduling tool SSIS throws the error: "Microsoft OLE DB Service Components" Hresult: 0x80070005 Description: "Access is denied.". Following that error is: Connection may not be configured correctly or you may not have the right permissions on this connection.
At first I thought it had something to do with permissions to the Oracle Home folder on the Windows Server so we gave the scheduling tool AD account modify access to it and subfolders but it still fails.
This package works on 2008 R2 O.S. so not sure why it's having an issue with 2012. It seems pretty obvious that it's a security issue since making the AD account local admin fixes the issue but we can't let that user stay admin. Would like to know what security is missing and the least amount of security required to get this working.
February 10, 2015 at 2:19 pm
First thing I'd check is whether it is using the same runtime (32 vs 64 bit).
A second question would be whether the oracle login being used is authorized only to come from a specific IP.
February 10, 2015 at 8:22 pm
Thanks for responding!
I ended up running process monitor on the server while the package was executed and found that it was in fact a DLL in the oracle folder that was getting the access denied error. It seems that the permissions on the Oracle Home folder were not inherited to the subfolders and files. Once the permissions were inherited the package worked fine. :pinch:
October 27, 2022 at 12:49 pm
I know this is an old post, but this was also my solution for "ORA-12154: TNS: could not resolve the connect identifier specified". This drove me crazy for months. I had SSIS package importing from Oracle in an Always on availability cluster with two nodes, one node worked the other one didn't. The Oracle home folders appeared the same until I drilled into them and looked at inheritance.
I was hoping to save someone else the headache.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply