Sample Header Ad - 728x90

SSIS proxy/credentials not working from within SQL Agent job step

7 votes
2 answers
8627 views
I have been looking around and found similar questions but nothing specific to what I am having trouble with. **Problem:** I cannot get the SSIS proxy and SQL credentials configured correctly to impersonate a restricted-access service account for a SQL Agent job step that runs an SSIS package. The restricted-access service account has all the needed privileges and the package is designed correctly. I have read much material on this subject and would like some help in case I missed something. **Background:** *(SQL 2012 SP3 server running on Windows Server 2012 R2. SQL engine runs under Domain1\Admin1 and SQL Agent runs under Domain1\Admin2. both also are in the SYSADMIN server role.)* We have an SSIS package that works fine when run interactively by a SYSADMIN, and also runs fine when run within a job step as "SQL Server Agent Service Account". However our Security group wants us to run using credentials limited to what the job needs to do, and I understand this as a best practice anyway. Everything I have read indicates that an SSIS proxy and credential will help solve for this requirement; however I can't get the configuration to work so I must be doing something wrong. The package is run via SSIS package job step. It is a local file and is not deployed to MSDB or SSISDB. The package connects to a network share, loads into a database that is local to the SQL server, truncates a table, runs a few stored procedures, then deletes the XLS file. The job step is set to use the 32-bit runtime. Security created an account (Domain1\NewUser) that will have Modify rights to the network share. I also asked they add Domain1\Admin2 as Modify to the same share. For setup, I created a SQL login for Domain1\NewUser using only the Public role, and added it as db_owner for the database in question because of the range of actions the package needs to perform. I created a credential (BatchLoad-credential) using as its identity the Domain1\NewUser account and the exact working password of the user account. I then created an SSIS proxy (BatchLoad-Proxy) using the BatchLoad-credential credential, active within the SSIS package subsystem, and added the SQL login for Domain1\NewUser as a proxy account principal. Then I changed the SQL Agent job step running the SSIS package to run as the BatchLoad-Proxy, and changed the owner of the job from Domain1\Admin2 to the Domain1\NewUser account. When the job is run, we get this error (some masking on the log has been done): > Executed as user: Domain1\NewUser. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 12:46:17 PM > > Error: 2016-06-24 12:46:28.56 Code: 0xC0202009 Source: xxxxxxx_xxxx Connection manager "yyyyyyyyyyy" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.". End Error Again, the package works fine when a SYSADMIN runs it interactively within BIDS so we know the file is in the correct format and the package is designed ok. I do not have permissions to the network share. When I try to connect manually I get a similar error: Windows cannot access \\ Error code: 0x80004005 Unspecified error Connecting to the network share using the Domain1\NewUser account and password, I can view the folder ok so I have verified the proper user account has access.
Asked by GregDBA (303 rep)
Jun 29, 2016, 02:37 PM
Last activity: Oct 11, 2016, 04:37 AM