Sql-server – SSIS proxy/credentials not working from within SQL Agent job step

PROXYsql-server-2012ssis

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.

Best Answer

I appreciate the feedback on this, and I hope this helps people in the future. I finally narrowed down the true root cause of the problem. I had not provided enough detail around the fact that an Excel file was being used. After disabling everything in the package, If I only enabled the Data flow task using the Excel source, I would get the failure, but only when using the SSIS proxy (which launched the job using the Domain1\NewUser account). If I set the job step to use the SQL Server Agent account, everything would work fine.

After some time away from the problem, I tried logging into the server with the NewUser1 credentials, and ran Excel for the first time. It prompted me for initials and then I closed the program. I had also added the account to the server's local Administrators group so I could RDP.

Then I ran the job using the SSIS proxy and everything worked fine. When I removed the account from the local Administrators group it would fail again, but I determined it was the local policy "logon as a batch job" that was being granted in that membership.

Here is what I have learned from this experience:

  1. SQL Credentials can only impersonate a user account, NOT a group. SSIS proxy is a valid solution for granting necessary privileges for a batch job. Excel (and possibly other applications) may need to be launched once using the related credentials, just to complete application settings in the user's profile on the server. Logon as a batch job is required for a proxy that runs DTExec to launch a package from the file system. Excel sources are problematic, and the OLE DB driver may report this problem when it isn't a layout/format issue:

Hresult: 0x80004005 Description: "External table is not in the expected format."

Related Question