Sql-server – PowerShell command ‘New-Item’ fails when invoked through SQL Server Agent job

powershellsql serversql-server-2016windows-server

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.

Best Answer

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

Related Question