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\path\01222020"
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\path\01222020'.  '.  Process Exit Code -1.  The step failed.

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

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