February 23, 2016 at 7:50 am
Hi everyone,
I have problem JOb with SSIS step which failed when proxy user isn't in windows Administrators group.
Bellow you have my configuration:
- SQL 2008R2
- SSIS package connect to Oracle database on other server (we have instalied both 32 and 64 bit Oracle Client)
- SSIS package is stored on SQL Server with "do not save sensitive data" protection Level. Passwords are sotred in text configfile.
- JOB step runned as proxy account
- SQL Agent service is runned by LocalServer
- Proxy account got sysadmin role on SQL Server
- Proxy Account is set as "log on as a batch job" (Local security Policy)
I have few scenarios. in some cases JOB finished with sucess and some times it failure with bellow error (oracle errors descriptions bellow was traneleted by me from polish language):
Executed as user: DOMAIN\PROXY_USER. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:19:02 Error: 2016-02-23 11:19:04.53 Code: 0xC0202009 Source: SSIS_PACKAGE_NAME Connection manager "ORACLE_TNS_ALIAS.ORACLE_USER" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-00604: error occurred at recursive SQL level 1 ORA-20500: access denied on PROXY_USER@ in applicaiton ORACLE_APPLICATION on database ORACLE_DATABASE. ORA-06512: on line 100". End Error Error: 2016-02-23 11:19:04.53 Code: 0xC020801C Source: SSIS_CONNECTION_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ORACLE_TNS_ALIAS.ORACLE_USER" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2016-02-23 11:19:04.53 Code: 0xC0047017 Source: SSIS_CONNECTION_1 SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2016-02-23 11:19:04.53 Code: 0xC004700C Source: SSIS_CONNECTION_1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-02-23 11:19:04.53 Code: 0xC0024107 Source: SSIS_CONNECTION_1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:19:02 Finished: 11:19:04 Elapsed: 1.56 seconds. Process Exit Code 1. The step failed.
Scenario 1. -- JOB FAILED
- SQL Agent service is runned by LocalServer
- JOB step runned as proxy_user
- Proxy account NOT in Windows Administrators Group
Scenario 2. -- JOB SUCCEEDED
- SQL Agent service is runned by LocalServer
- JOB step runned as proxy_user
- Proxy account in Windows Administrators Group
Scenario 3. -- JOB SUCCEEDED
- SQL Agent service is runned by Proxy_user (the same as proxy in previous secnarios)
- JOB step runned as SQL Agent Service Account (without proxy)
- Proxy_user NOT in Windows Administrators Group
Scenario 4. -- JOB SUCCEEDED
- SQL Agent service is runned by Proxy_user (the same as proxy in previous secnarios)
- JOB step runned as SQL Agent Service Account (without proxy)
- Proxy_user in Windows Administrators Group
Scenario 5. -- JOB SUCCEEDED
- SQL Agent service is runned by Proxy_user (the same as proxy in previous secnarios)
- JOB step runned as proxy_user
- Proxy_user in Windows Administrators Group
Scenario 6. -- JOB FAILED
- SQL Agent service is runned by Proxy_user (the same as proxy in previous secnarios)
- JOB step runned as proxy_user
- Proxy_user NOT in Windows Administrators Group
I have SSIS package without ORACLE connection only exel file loading data to SQL Server and the same scenarios failed in the same configuration. (log receive information that Proxy_user do not have permissions to xls file or file is opened. proxy_user got permissions set: FullControl to xls file).
Do you have any idea how to solve that problem?
It is not applicable to set Proxy_user on production server in Administrators group.
Thanks in advance.
Tom
February 24, 2016 at 2:44 am
Does the proxy user has access to the tnsnames/oracle client?
February 24, 2016 at 4:06 am
Yes, tnsnames are configured based on environment Variables and Proxy_user got acces to that folder (full control).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply