Question

You maintain a SQL Server 2022 instance on an Azure VM. A SQL Server Agent job contains a step of type 'PowerShell' that imports the SqlServer module and runs a maintenance cmdlet. The step succeeds when you run the same script manually in a 64-bit PowerShell console on the server, but the job step fails with an error indicating the module cannot be loaded. What is the most likely cause?

Accepted Answer

The SQL Server Agent PowerShell subsystem runs the script under the Agent service account, which does not have the SqlServer module installed in a path it can access.

Answer

The SQL Server Agent service account lacks the sysadmin fixed server role, which is required to run any PowerShell job step.

Answer

PowerShell job steps must be configured with a proxy account mapped to the Windows Task Scheduler credential.

Answer

The job step must be changed to an 'Operating system (CmdExec)' type because the PowerShell subsystem cannot import external modules.