Cannot find path Powershell script on SQL Server Agent

Question

I've been searching all the internet and stackOF to and resolve this issue. I am trying to automate a db restore using SQL Server Agent. The sql server agent job comprises of four steps 3 of which are tsql and one which is a powershell script.

I have created a proxy with admin credentials so that the script can be run as admin.

    cd c:;
$backuppath="Microsoft.PowerShell.Core\FileSystem::\\sharedcomputer\backup";
$destpath="c:\tmp\";
get-childitem -path $backuppath | where-object { -not $_.PSIsContainer } |

    sort-object -Property $_.CreationTime |

    select-object -last 1 | copy-item -Destination (join-path $destpath "byte.BAK");

It copies the .bak file from the source shared folder and places it in to tmp folder on the target. Whenever I run this through regular Powershell it works fine. Whenever I try to run this from SQL server agent I get an error stating that it cannot find path.

I tried to even use net use to pass credentials for the shared folder. I am thinking it has to do with the fact that the folder has requirement for credentials.

I have turned of password file sharing as well on the source server but for some reason when i use windows explorer to locate the shared file it still asks for credentials initially. Once its saved and cached I can then use powershell to cd in to that folder. But none of this works when its executed from sql server agent

Answer 1

I was able to finally figure this out with a little help from a Windows Server guy... Going back to answering the question. When I created a proxy agent I used the credentials that were associated with the current Domain Account i.e Domain\Administrator.

In order for the proxy to connect to the remote server it needs to have credentials on that domain. So what I did was create another domain account on my target and source servers using the same name and password and gave it permissions to the folders I needed

That account was used in the proxy and the credential was set up as .\AccountName, so because the wildcard was in place the proxy was able to jump back and fort between the two servers and successfully transfer the files....

Hope this helps