SQL Agent Job fails with Unable to connect to the remote server (Sharepoint)

Question

I have a PS1 script that uses Microsoft.Online.SharePoint.PowerShell and downloads files from specific sharepoint library (Sharepoint Online).
It runs successfully on the SQL server machine thru SQL Server powershell, but fails when executed thru SQL Agent -> Powershell or cmdexec command with error: Exception calling "ExecuteQuery" with "0" argument(s): "Unable to connect to the remote server"

What could cause this error and how to troubleshoot this error?

Here is what PS1 script writes to log:

• Error connecting to library in SharePoint Online
• Exception calling "ExecuteQuery" with "0" argument(s): "Unable to connect to the remote server"

Here is what SQL Agent Job writes to log:
An error occurred while enumerating through a collection: The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.. At E:\ Data ev\ga\ script\SPOnline. ps1:157 char:9 + $folder = $folders | Where {$_.Name -eq $FolderName} +

here is the piece of script (again, it runs fine on SQL Server powershell but fails when ran thru SQL Agent Job):

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)

Try
{
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $Ctx.Credentials = $Credentials

    #Get the Library
    $List = $Ctx.Web.Lists.GetByTitle($LibraryName)
    $Ctx.Load($List)
    $folders = $List.RootFolder.Folders
    $Ctx.Load($folders)
    $Ctx.ExecuteQuery()

    Write-Log "Listed loaded."
}
Catch
{
    Write-Log "Error connecting to library in SharePoint Online `n"
    Write-Log $_.Exception.Message
}

$Query = New-Object Microsoft.SharePoint.Client.CamlQuery

$Query.ViewXml= "@
<View Scope='RecursiveAll'> 
<Query>
   <Where>
      <Eq>
         <FieldRef Name='TaskStatus' />
         <Value Type='Text'>$TaskStatus</Value>
      </Eq>
   </Where>
</Query>
</View>"

$count =0
#Get List Items in Batches
Do
{ 
    # $List.RootFolder.Folders[$FolderName]

    if ($FolderName -ne "")
    {
        $folder = $folders | Where {$_.Name -eq $FolderName}        
        $ctx.load($folder)
        $ctx.ExecuteQuery()
        $Query.FolderServerRelativeUrl = $folder.ServerRelativeUrl
    }

    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()
    $ListItems.Count

    #Update Postion of the ListItemCollectionPosition
    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
    $Query.ListItemCollectionPosition

    If ($ListItems.Count -eq 0) 
    {
    Write-Log "No items in library that meets CAML query condition `n" 
    Break
    }

    $downloadItemCount=1;

    #Extract the each list item from the List Items collection.
    ForEach($Item in $ListItems)
    {                  
            try
            {

                $Ctx.Load($Item.File)
                $Ctx.ExecuteQuery()  

                $SourceFile=$Item.File.ServerRelativeUrl;
                #$Folder_Name1 =  split-path $SourceFile  
                #$Folder_Name1 =  split-path $Folder_Name1 -leaf;
                #$TargetFile=$TargetFolder + "\"+$Folder_Name1+"\" + $Item.File.Name;

                $TargetFile=$TargetFolder + "\" + $Item.File.Name;
                If ($Action -ne "UpdateOnly")
                {
                FileDownLoadFromSPOnlineLibrary -SPOSiteURL $SiteURL -SourceFilePath $SourceFile -TargetFilePath $TargetFile 

                $fileDownloadingMessage="Downloaded: "+$Item.File.Name; 
                #Write-Host $fileDownloadingMessage -BackgroundColor DarkGreen
                Write-Log $fileDownloadingMessage
                }

                $Item["TaskStatus"] = $UpdateStatus;
                $Item[$DescriptionFieldname] = $UpdateDescription;
                $Item.Update();
                $Ctx.ExecuteQuery();

                Write-Log "Set Task Status to Downloaded."

            $downloadItemCount++

            }
            catch
            { 
            $ErrorMessage = $_.Exception.Message +"in: " +$Item.File.Name
            Write-Host $ErrorMessage -BackgroundColor Red
            Write-Log $ErrorMessage 
            }
        }
    Break; #just do 1 item.    

}While ($Query.ListItemCollectionPosition -ne $null)

Answer 1

Hi @Pam ,
The PowerShell step in SQL server executes the command within the context of the SQLPS provider. So when you access the file system it has no clue. There are questions on DBA.SE that show options, but easier method is to just use cmdexec step and call powershell.exe

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello @Pam ,

Thank you for your question.

To configure SQL Server Agent
Select the Start button, and then, on the Start menu, Select Control Panel.
In Control Panel, Select System and Security, Select Administrative Tools, and then select Local Security Policy.
In Local Security Policy, Select the chevron to expand the Local Policies folder, and then Select the User Rights Assignment folder.
Right-click a permission that you want to configure for use with SQL Server and select Properties.
In the permission's properties dialog box, verify that the account under which SQL Server Agent runs is listed. If not, Select Add User or Group, enter the account under which SQL Server Agent runs in the Select Users, Computers, Service Accounts, or Groups dialog box, and then Select OK.
Repeat for each permission that you want to add to run with SQL Server Agent. When finished, Select OK.

For more information please go through this link:
https://learn.microsoft.com/en-us/sql/ssms/agent/configure-sql-server-agent?view=sql-server-ver15

If the reply was helpful, please don't forget to upvote or accept as answer