SSIS vs. SQL: Impersonation — How an Agent Job Gets to the Resources It Needs

Published Aug 17, 2018 · Updated Jun 2, 2026
by

A SQL Server Agent job that needs to read from a file share fails with Login failed — the agent’s service account has no permission there. The solution is impersonation at runtime, configured in SQL Server Agent through a proxy user. One dead end remains: plain Transact-SQL Script steps don’t support impersonation — the workaround is to wrap the T-SQL in an SSIS package.

What you’ll take away:

  • Why every job step runs by default in the security context of the agent’s service account — and why that’s rarely the right fit.
  • How to configure the chain Credential → Proxy → Job step, side-by-side in the SSMS GUI and in T-SQL.
  • Why plain Transact-SQL Script steps don’t support the Run As property, which step types do — and how to close the gap with either an SSIS wrapper or a CmdExec step running sqlcmd.exe.
  • The 2026 bridge: Group Managed Service Accounts (gMSA), Azure SQL Managed Instance, Azure Data Factory with managed identity, SQL Server on Linux, container/pod identity.

Prerequisites: SQL Server 2017+ on Windows, SSMS for the GUI configuration, a login with permission to create credentials (sysadmin or the explicit ALTER ANY CREDENTIAL permission) and proxies (msdb role SQLAgentOperatorRole plus the owner role).

Contents

Overview

SQL Server Integration Services (SSIS) is a powerful tool set for building ETL pipelines. There are plenty of good reasons to use SSIS, and plenty against. Within the Microsoft stack, the alternative for complex ETL pipelines is (essentially) Transact-SQL (T-SQL).

This article belongs to a series of articles on the important decision criteria for choosing the right technology — SSIS and/or T-SQL.

When a SQL Server Agent job is started, every step runs by default in the security context of the service account of the SQL Server Agent. That service account is set during setup — either as an explicit domain or machine account, or as the local system account NT AUTHORITY\System.

Whichever account is chosen, it ends up with a fixed set of permissions that rarely fit every job. The local system account NT AUTHORITY\System has extensive rights on local resources and is a member of the Windows group Administrators — but it typically lacks permission on file shares, on data sources hosted on other machines, or on cross-domain resources.

Following the need-to-know principle — every account gets exactly the rights it needs for its task, nothing more and nothing less — a job step should run under a dedicated account whose permissions match the actual task. Since a typical SQL Server Agent hosts dozens of jobs with very different requirements, a single service account can never satisfy all of them at once.

Microsoft’s answer is impersonation (in German Identitätswechsel) at runtime. Concretely: a single job step can run under a different login than the service account by assigning a proxy user.

The service-account problem

The friction becomes tangible as soon as a job step needs to access resources outside the SQL Server instance:

  • A file share holding CSV or Excel source files, where the service account has no permission.
  • Data sources on other SQL Server instances where the service account has no login.
  • Domain resources (LDAP queries, Active Directory lookups) that only a dedicated service account is authorized for.
  • Cloud endpoints (storage accounts, external APIs) that demand a different auth token than the one the service account can provide.

The naive fix would be to give the service account more permissions. That doesn’t scale: every additional grant enlarges the attack surface and mixes the rights of different job worlds. As soon as one job is allowed to read only and another is supposed to write and delete, the requirements collide.

The solution: credential + proxy user

Microsoft splits the identity configuration into two objects:

  • Credential — an instance-level object (in the SSMS path Security → Credentials) that encapsulates an external login (a domain account, a gMSA, a local account) together with its secret. The credential lives on the server, not on the agent.
  • Proxy — an agent-level object (in the SSMS path SQL Server Agent → Proxies → <subsystem>, for example SSIS Package Execution) that links a credential to a specific subsystem and exposes that link to job steps.

In the Run As field of the job step, the proxy replaces the service account. As soon as the step starts, the agent switches internally to the identity stored in the credential and runs the step in that identity’s security context.

Step by step: configuration

The configuration takes three steps, shown below in both variants — through the SSMS GUI and through T-SQL. Either path leads to the same final state; which variant fits better depends on whether the setup is a one-off manual operation or has to live reproducibly in a script.

Step 1 — Create the credential

In the SSMS Object Explorer, navigate to Security → Credentials and pick New Credential… from the right-click menu. The dialog asks for a credential name, an identity (typically a domain account in the form DOMAIN\account) and the matching password.

SSMS Object Explorer at Security → Credentials, with the newly created credential cred_etl_proxy visible as a list entry.

Programmatically:

  1: USE [master];
  2: GO
  3: 
  4: CREATE CREDENTIAL [cred_etl_proxy]
  5: WITH
  6:    IDENTITY = N'DOMAIN\svc_etl_proxy',
  7:    SECRET   = N'<password>';
  8: GO

The <password> is just a placeholder — a hard-coded clear-text password in the script is a clear anti-pattern in production. In a real setup the secret is loaded from a safer source: Azure Key Vault, a gMSA identity that has no static password at all, or an encrypted configuration file. The file holding the clear-text never ends up in the repository.

Step 2 — Create the proxy user

The corresponding GUI path in SSMS goes through SQL Server Agent → Proxies → SSIS Package Execution and a right-click on New Proxy…. The dialog takes the proxy name, the associated credential and the subsystem (SSIS Package Execution). Under “Principals” it additionally captures which logins are allowed to pick the proxy when configuring a job step in the first place.

[SSMS Object Explorer at SQL Server Agent → Proxies → SSIS Package Execution, with the newly created proxy proxy_etl_fileshare visible as a list entry.

Programmatically:

  1: USE [msdb];
  2: GO
  3: 
  4: <em>-- Proxy anlegen</em>
  5: EXEC dbo.sp_add_proxy
  6:    @proxy_name      = N'proxy_etl_fileshare',
  7:    @credential_name = N'cred_etl_proxy',
  8:    @enabled         = 1;
  9: GO
 10: 
 11: <em>-- Proxy auf das SSIS-Subsystem (ID 11) berechtigen</em>
 12: EXEC dbo.sp_grant_proxy_to_subsystem
 13:    @proxy_name   = N'proxy_etl_fileshare',
 14:    @subsystem_id = 11;
 15: GO
 16: 
 17: <em>-- Login berechtigen, den Proxy bei Job-Step-Konfiguration zu verwenden</em>
 18: EXEC dbo.sp_grant_login_to_proxy
 19:    @proxy_name = N'proxy_etl_fileshare',
 20:    @login_name = N'DOMAIN\bi_developer';
 21: GO

A proxy should always be granted only to the subsystems it actually needs. A blanket grant across all subsystems enlarges the attack surface unnecessarily and contradicts the need-to-know principle.

Step 3 — Point the job step at the proxy

In the job step editor (reachable through SQL Server Agent → Jobs → <job_name> → Steps → <step_name> → Edit…) the Run As dropdown is changed from the default entry SQL Server Agent Service Account to the newly created proxy.

Job step properties dialog showing the activated Run As dropdown, proxy_etl_fileshare selected, step type SQL Server Integration Services Package.

Programmatically:

  1: USE [msdb];
  2: GO
  3: 
  4: EXEC dbo.sp_update_jobstep
  5:    @job_name   = N'job_etl_load_daily',
  6:    @step_id    = 1,
  7:    @proxy_name = N'proxy_etl_fileshare';
  8: GO

On the next job run the agent executes this step no longer under the service account but in the security context of the identity stored in the credential, domain\marcu.

Dead end: Transact-SQL Script step has no Run As

The Run As property is not available for every step type, however. Which subsystems allow the configuration and which don’t is summarised in the table below:

Step type (subsystem name)Run As (proxy) supported?
SQL Server Integration Services Package (SSIS)✓
PowerShell (PowerShell)✓
Operating System / CmdExec (CmdExec)✓
Analysis Services Command/Query (ANALYSISCOMMAND / ANALYSISQUERY)✓
Replication subsystems (Distribution, Merge, QueueReader, Snapshot, LogReader)✓
Transact-SQL Script (TSQL)✗

The string names are the robust API anchor — the numeric subsystem_id values in msdb.dbo.syssubsystems are not part of the official Microsoft documentation contract and can vary between versions. The local values can be read straight from the instance: SELECT subsystem_id, subsystem FROM msdb.dbo.syssubsystems;.

For Transact-SQL Script steps the SQL therefore always runs in the security context of the service account — regardless of which proxy is configured on the step. A pure T-SQL pipeline built from stored procedures and scripts cannot be impersonated this way.

In practice, two workarounds have established themselves:

Workaround 1: SSIS package as a wrapper. The actual T-SQL stays in stored procedures, and the SSIS package only contains one or a few Execute SQL Task containers that call those stored procedures. The job step is back to type SSIS Package Execution and supports Run As. The reasoning and the diff pragmatics behind this approach are deepened in the companion article SSIS vs. SQL: Readability and Maintainability.

Workaround 2: CmdExec step with sqlcmd.exe. Here the step is of type Operating System (CmdExec) (subsystem 3) and therefore also supports Run As. What gets executed is sqlcmd.exe with the T-SQL script as a parameter:

sqlcmd.exe -E -S server\instance -d database -i path\to\script.sql

Caveats:

  • -E requires Windows authentication against the SQL Server, using the identity of the proxy credential. That works when the credential wraps a Windows account.
  • -U <login> -P <password> is the SQL-authentication variant — a clear-text password in the step command line is a production anti-pattern.
  • Logging is reduced to the step output (stdout/stderr); the rich SSIS-style ETL logging trails (execution log tables, SSISDB reports) are gone.

Whoever doesn’t want to give up the comfort of the SSIS logging world is much better off with workaround 1; sqlcmd is the right choice when the setup is supposed to run without an SSIS stack anyway.

Impersonation beyond the on-prem agent

The proxy concept dates back to the SQL Server 2008 world and fits the classic on-prem stack with a Windows domain. In a 2026 setup the auth landscape looks rather different:

  • Group Managed Service Accounts (gMSA). Since Windows Server 2012 Microsoft recommends gMSAs as the modern replacement for statically configured service accounts: automatic password rotation, no clear-text storage, bound to computer or service accounts. The credential can point at a gMSA identity, which makes the clear-text password problem from step 1 disappear. Windows Server 2025 adds delegated Managed Service Accounts (dMSA) to the picture — a hardened variant for individual services bound to a specific machine, additionally protected against pass-the-hash and pass-the-ticket attacks. dMSA isn’t a gMSA replacement but a second tool in the same toolbox.
  • Azure SQL Managed Instance. Ships a SQL Server Agent with a reduced feature set — proxies are not supported in MI. Identity bridges for SSIS packages or external resource access run instead through the managed identity of the MI itself or through an Azure Data Factory integration with its own managed identity. PowerShell and CmdExec steps remain available; Analysis Services, Merge Replication and Queue Reader are missing.
  • Azure Data Factory. Replaces classic agent jobs with triggers and activities. Instead of a proxy user with a credential, the factory is assigned a system-assigned or user-assigned managed identity, which is then authorised in target systems (storage, SQL, Key Vault) as an identity. No more static secret, no separate proxy configuration. Microsoft positions Data Factory in Microsoft Fabric as the successor platform; Azure Synapse Pipelines remain technically available but are strategically being folded into Fabric.
  • SQL Server on Linux. Supports SQL Server Agent, but the proxy concept for Windows authentication doesn’t carry over one-to-one — Linux auth against file shares typically runs through the OS mount (CIFS, NFS) rather than through the agent proxy.
  • Containerized SQL Server (Docker, Kubernetes). The service-account model shifts onto pod identity / workload identity. The classic proxy stack loses ground there.

The same point runs through all variants: anyone setting up a new system today reaches for managed identity or gMSA rather than for a static domain account with a password. Existing on-prem setups keep living with proxy and credential — but at the next service-account refresh it’s worth asking whether gMSA would simplify the setup noticeably.

Take-away

  • Impersonation is mandatory as soon as a job step needs to access resources outside the service-account context. The default — everything runs under the service account — neither scales nor is it secure.
  • Credential + Proxy is the classic on-prem pattern, applied in three steps: first the credential in the Security tree, then the proxy in the SQL Server Agent tree per subsystem in use, and finally the job step that’s switched over to that proxy.
  • Transact-SQL Script steps remain the gap, because they don’t support Run As. Whoever needs impersonation there falls back either onto an SSIS package as a wrapper or onto a CmdExec step with sqlcmd.exe — the former for the clean logging world, the latter for a minimal setup with no SSIS stack at all.
  • In the 2026 world managed identity (in Azure) and gMSA (on-prem) increasingly take over the role of the classic service account. New setups start straight there; existing systems can prepare the move step by step.

FAQ

Why doesn’t a Transact-SQL Script step support Run As?

The TSQL subsystem runs the script directly through the internal SQL engine rather than through a separate worker process the way SSIS, PowerShell or CmdExec do. Microsoft never retrofitted the Run As property for this subsystem — presumably because the use case “SQL script runs as a different SQL login” can also be solved without job-step impersonation, for example via EXECUTE AS LOGIN = '…' directly inside the SQL script.

Can I run an SSIS package in the agent under the service account without a proxy?

Yes — that’s even the default. With Run As set to SQL Server Agent Service Account, the step starts without an identity switch and runs in the service-account context. That works fine as long as the service account holds all the permissions involved. As soon as it doesn’t — typically for file-share access or cross-instance sources — the proxy enters the picture.

How many proxy users does an agent instance need?

As many as necessary, as few as possible — typically one per job world with its own resource profile. A common split is: one proxy for ETL loads with file-share access, one for cross-instance sources, and a third for cloud storage. Each proxy is granted only to the subsystems it actually uses — collector proxies covering every subsystem run against the need-to-know principle.

What’s the difference between a credential and a proxy?

The credential lives at instance level and encapsulates an external identity (a domain account, a gMSA, a local account) together with its secret — it’s the “identity source.” The proxy lives at agent level and links exactly one credential to one or more subsystems (SSIS, PowerShell, CmdExec, …); it additionally controls which SQL logins are allowed to pick the proxy when configuring a job step. A single credential can be reused across multiple proxies — for example one proxy for SSIS and another for PowerShell, both backed by the same identity.

Does impersonation still work in Azure SQL Managed Instance?

Unlike on-prem: the agent in Managed Instance doesn’t support proxies. Anyone needing job steps to run under a different identity in MI goes through the managed identity of the MI itself or through an Azure Data Factory integration with its own managed identity — the target system in question (storage, SQL, Key Vault) is then authorised against that identity. The classic Credential + Proxy configuration described in this article doesn’t apply there. For pure Azure-native pipelines, Azure Data Factory is the strategic choice anyway, because managed identity hangs directly on the triggers/activities model there.

Why not a CmdExec step with sqlcmd.exe instead of an SSIS wrapper?

The CmdExec workaround is legitimate and sometimes exactly the right call — especially when the stack is supposed to run without SSIS anyway. Three reasons usually argue for the SSIS wrapper, though: first, the logging is richer (SSISDB reports and execution log tables instead of just stdout/stderr); second, the auth bridge runs cleanly through Windows auth (-E) against the proxy credential, whereas the SQL-auth variant (-U and -P) brings the clear-text password problem back; and third, an SSIS wrapper can be extended later with pre-/post-tasks or conditional branching, without having to reshape the step configuration inside the agent.

What are the security caveats of a proxy setup?

Three spots where the configuration has to stay particularly clean: first, the credential secret should not be maintained as a clear-text password — Azure Key Vault, gMSA identities with no static password at all, or an encrypted configuration file are the alternatives; the code/001.sql snippet in the repository only carries the clear-text as a placeholder. Second, subsystem permissions are granted granularly — a proxy is only opened to the subsystems it actually needs; a blanket grant across all subsystems enlarges the attack surface unnecessarily. And third, a dedicated audit trail pays off: changes to credentials and proxies do show up in the SQL Server default trace, but for regulatory requirements a separate SERVER AUDIT SPECIFICATION (using groups such as SERVER_PRINCIPAL_CHANGE_GROUP for credentials and DATABASE_PERMISSION_CHANGE_GROUP for proxy subsystem grants) is the more robust solution.

SSIS-vs.-SQL cluster:

ETL context: