PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

Question

I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing.

The problem is the script works fine when I manually execute. It also executes fine when I execute it as SQL Server Agent service account. But the same script fails when I try to execute as part of SQL Server Agent job. Am I missing something here?

$path = "\\server\shared\path01222020円"
New-Item -path $path -ItemType Directory -Force
Exception:
Date 1/22/2020 5:25:10 PM
Log Job History (test)
Step ID 1
Server serverA
Job Name Test
Step Name Test
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed 
Operator Net sent 
Operator Paged 
Retries Attempted 0
Message
Executed as user: domain\agent_service_account. A job step received an error at 
line 9 in a PowerShell script. The corresponding line is 'New-Item -path $path 
-ItemType Directory '. Correct the script and reschedule the job. The error 
information returned by PowerShell is: 'Invalid Path: 
'\\server\shared\path01222020円'. '. Process Exit Code -1. The step failed.

• alroc: Not enough for an answer, but check out dbatools.io/agent for guidance on how best to execute PowerShell in Agent jobs

Answer 1

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.

Alternatively, you can change directory beforehand to a local drive (cd C:) and it should then work without prefixing the provider name, but you may want to stay in the default SQLSERVER:\SQL\SERVERNAME\INSTANCENAME path depending on what your script requires.

More info:

https://dbatools.io/agent/

https://social.technet.microsoft.com/Forums/windowsserver/en-US/ec7f3ae8-d196-459e-b9dc-e6ed0df93004/running-powershell-from-sql-server-using-unc-paths

• S.D.: perfect, both solutions worked for me & that you for the reference links.

Answer 2

sqlcheckpoint,

Please take a look at the link below, which describes which Powershell modules are used by SQL Agent. Its either SQLPS or SqlServer depending upon your version of SQL Server.

https://learn.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver15

It quite likely the New-Item cmdlet is not found in the powershell module used by SQLAgent.

You could create a SQLAgent job with a CmdExec step, which in turn calls powershell.exe.

powershell.exe -ExecutionPolicy ByPass -NoProfile -File PATH_TO_YOUR_SCRIPT

See the link below for all the options/parameters for powershell.exe

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_powershell_exe?view=powershell-5.1

• Tony Hinkle: Agree that this is probably on the right track. In general, I now just create a .bat file to run PowerShell scripts, and then have the Agent step execute the .bat file.
• Peter Vandivier: New-Item being not found as a cmdlet would return a much different error message. Try it yourself by attempting to execute New-Foo