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 \\<path to the network share> 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.
External table is not in the expected formatdoesn't smell like a permissions issue which I was expecting given the title. You say it runs fine from BIDS, but that's for you in an interactive mode. Given the breadth of the potential problem domain, I'd start by using RunAs to run the SSIS package on your machine (or the server, whatever) to identify is this a SQL Agent/Proxy issue or a user issue. Command approximately
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx