Security

New-sqmAgentProxy

Creates a SQL Server Agent proxy account for a Windows identity. Validates the AD account health (enabled, not locked, not expired) before creating the SMO Credential and ProxyAccount objects. Supports CmdExec, SSIS, and PowerShell subsystems.

Module: sqmSQLTool
Requires: SMO (no dbatools)
ShouldProcess: Yes (Medium Impact)
Output: PSCustomObject

Execution Flow

START -WindowsCredential provided? No Get-Credential dialog Yes Get-sqmADAccountStatus → check Enabled / LockedOut / PasswordExpired / AccountExpired AD account healthy? No → throw account error OK SMO Connect (Microsoft.SqlServer.Smo.Server) ShouldProcess? WhatIf Skip -Force & existing Credential/Proxy? Yes Drop existing Credential + Proxy SMO: Create Credential (Name = ProxyName, Identity = DOMAIN\user) SMO: Create ProxyAccount → link Credential foreach subsystem in -Subsystem (CmdExec=1 / SSIS=11 / PowerShell=12 / All) ProxyAccount.AddSubSystem(subsystemId) ProxyAccount.Alter() → persist DONE
AD account validation is performed via Get-sqmADAccountStatus before any SMO operations. If the account is disabled, locked, or has an expired password or account, the function throws immediately without connecting to SQL Server. Use -Force to replace an existing proxy with the same name.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-ProxyNameStringRequiredName for the new Agent proxy account.
-ProxyDescriptionStringOptionalDescription stored on the proxy object.
-WindowsCredentialPSCredentialOptionalWindows account for the proxy. If omitted, Get-Credential is invoked interactively.
-SubsystemString[]OptionalOne or more of: CmdExec, Ssis, PowerShell, All. Default: CmdExec.
-ForceSwitchOptionalDrop and recreate the proxy if a proxy with the same name already exists.
-EnableExceptionSwitchOptionalThrow exceptions immediately on error.
-WhatIfSwitchOptionalPreview actions without creating any objects.
-ConfirmSwitchOptionalPrompt before creating objects.

Return Value

Returns a PSCustomObject with: SqlInstance, ProxyName, CredentialName, WindowsAccount, Subsystems, Status (Created / Replaced / WhatIfSkipped), Message.

Examples

Example 1 — Create proxy with interactive credential prompt

New-sqmAgentProxy -SqlInstance "SQL01" -ProxyName "DBA_Proxy"

Example 2 — All subsystems with stored credential

$cred = Get-Credential "DOMAIN\svc_sqlagent"
New-sqmAgentProxy -SqlInstance "SQL01" -ProxyName "SSIS_Proxy" `
    -WindowsCredential $cred -Subsystem "All" `
    -ProxyDescription "SSIS and PowerShell proxy for DBA jobs"

Example 3 — Replace existing proxy with -Force

New-sqmAgentProxy -SqlInstance "SQL01" -ProxyName "DBA_Proxy" `
    -WindowsCredential $cred -Subsystem "CmdExec", "PowerShell" -Force