22046 "Impersonation error" running SQL Server Agent job

Question

(New to SSIS. None of the similar questions mention this particular impersonation error.)

I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from.

I checked the agent log file and it's this:

[298] SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000]

I checked the Windows security log in Event Viewer:

Failure Information:
Failure Reason:     Unknown user name or bad password.
Status:         0xC000006D
Sub Status:     0xC000006A

I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works.

We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped.

This worked right out of the box in our QA domain.

Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user?

Windows 2016 + SQL Server 2016

Answer 1

Check the account that SQL Agent itself is running as.

See this article on the Redgate "SimpleTalk" website about setting up SQL Agent correctly (section under "What service account to use") for more information.

Key part is this:

The core of the SQL Server Agent infrastructure is the SQL Agent Service. This is a windows service that is responsible for executing the different types of job steps supported by SQL Server Agent. The service account defines the Microsoft Windows account used to execute the SQL Agent service.

You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose from the following options:

Local System– This is the NT AUTHORITY\System account on the local machine. It is a member of the Windows Administrators group on the local machine, and therefore, is a member of the SQL Server sysadmin fixed server role. Since the account is a Windows administrator, it has permissions beyond what is required for running SQL Server Agent, and it is therefore not recommended to be used as a service account.

Local Service– not recommended. Has minimum privileges on local machine.

Network Service – not recommended. Has minimum privileges on local machine.

Windows Domain Account – You can also choose a windows domain account as the service account for SQL Server Agent. Using a windows domain account and granting it the necessary permissions is the best practice when choosing a service account. It is also recommended that this account not be a member of the Windows Administrators group. Further, the service account is required to be a member of the SQL Server sysadmin fixed server role on the SQL Server instance.

In our case, our Sql Agent was running as Local Service. Changing it to run as Local System immediately fixed the issue.