In this post, we’ll look at how PowerShell job steps may have issues connecting to network locations from within SQL Server Agent job steps.
Timeless Backup Job
When working with SQL Server backups, I bet we’ve all seen agent jobs that look like this:
It’s a classic backup job – run the backup using T-SQL and then upon success, purge the backup directory of backups that are older than a given number of days.
My backup job step backs up to a UNC path – \\192.168.2.174\Backups and then the PowerShell step will purge any backups older than 7 days.
The purge step looks like this:
The PowerShell command being:
Get-Childitem "\\192.168.2.174\Backups" | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item
This is a fairly straightforward command – list all the files in the directory older than seven days and pipe them into Remove-Item to delete them.
My backup has just run so after completion of step 1 of the job, the backup directory now looks like this:
At this point, my purge step runs. As it is just after 19/05/2026 00:00, the oldest backup from 12/05/2026 00:00 will be deleted.
Let’s run the purge step and see what happens.
Here’s the job history:
The job displays success, though we can see from the message that there has been an error (the error is non-terminating, hence the success reported by the agent)
The error states:
Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘Get-Childitem “\192.168.2.174\Backups” | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find path ‘\192.168.2.174\Backups’ because it does not exist. ‘. Process Exit Code 0. The step succeeded.
The path does not exist? But it does – our backup in T-SQL has just written there. Could it be permissions? Well, both steps are running as the agent service account, so both will present as the same user account to the remote share.
As a slight aside, we can get SQL Server to return an error on the job step by changing PowerShell’s $ErrorActionPreference variable at the top of the script, which would mean this error becomes terminating:
$ErrorActionPreference = "Stop";
Just to prove the directory indeed exists and we have permissions, I’ve created a test job with a step to read from the directory, and a step to write to the directory.
The read step just lists the files:
EXEC xp_dirtree '\\192.168.2.174\backups',1,1;
The write step writes to a file. I would not normally recommend having xp_cmdshell enabled if at all possible, however, this is a useful case where I enabled it, ran my test and then disabled it again:
EXEC xp_cmdshell 'echo "hello" > "\\192.168.2.174\Backups\echo.txt"';
The steps above will authenticate to the file system as the SQL Server Agent service account – the same account the steps in our backup job run as.
The job succeeded. I enabled “include step output in job history” so we can confirm the output and prove permissions still exist:
Step 1 output:
Step 2 output:
Let’s check whether the test file was created:
It’s there: echo.txt, we therefore know that the directory exists and we have both read and write permissions, so what is going on?
The Cause
The issue is down to PowerShell Providers. SQL Server Agent by default launches PowerShell under the SQLServer provider, whereas the PowerShell prompts we open as users launch under the FileSystem provider.
We can tell this by the current directory shown to us at the prompt.
Below is the default prompt when opening a PowerShell prompt from within the OS. Notice that the current working directory is the logged-in user’s home directory on the C:\ drive – a file system location.
We can verify the provider that PowerShell loads when running under SQL Server Agent by creating another test job. The test job has a PowerShell step that once again includes the step output in the job history so we can see the output of our commands. The PowerShell command we can run to show the current working directory is:
echo $PWD;
If I run the test job and look at the output returned from the command:
We see that this returned SQLSERVER:\ , which is the root path of the PowerShell PSProvider.
In our good old vanilla PowerShell prompt within the OS desktop, if I switch to the SqlServer provider, we can still list directories on the file system. Note that I am using ls, which in PowerShell is an alias for Get-ChildItem.
Import-Module SqlServer
cd SQLSERVER:
ls C:\
However, watch what happens when we try to list the directory for the remote server:
ls \\192.168.2.174\Backups
Very familiar error, right?
The issue is that when working under a provider that is NOT FileSystem, we cannot perform tasks on a UNC path. The workaround is to prefix with the provider name:
ls FileSystem::\\192.168.2.174\Backups
So now we know this, let’s tweak our command in the purge step of our agent job:
Get-Childitem FileSystem::"\\192.168.2.174\Backups" | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item
and then run the job from the purge step again.
The job is successful:
We can also verify the backup file is now gone from the file system:
An alternative approach would be to add a line at the top of the script in the agent job to switch from the SqlServer provider to the FileSystem provider, this would mean the provider prefix is not required:
Set-Location C:
So when working with PowerShell in SQL Server, it is good to be aware that the provider that is loaded by default is SqlServer and not FileSystem as you may naturally assume.