Powershell Script failure on Remote Server

Question

I have a PowerShell script with logic to transfer data from 1 SQL Server to another using the Bulk Copy command.

In Powershell Version 5, I have installed the SQLServer module to do the above activity.

When I execute the Powershell script from my local machine via the PowerShell tool it is successful. Similarly, when I executed the same command from the remote server (Windows 2012 R2) again via the PowerShell tool where my account has admin access it was successful.

But when I am calling the same command from the Jitterbit (ETL) tool pointing to the above Remote Server which uses Service Account it's failing with the below error

 Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies

I did check a couple of sites for the above error and almost everywhere it's suggested to install SharedManagementObjects.msi and SQLSysClrTypes.msi so I did install it on Remote Server with a version specific to my MSSQL 2016 but still the same issue.

Also checked directory (C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18230) there also I can see Microsoft.SqlServer.BatchParser.dll file exists.

So now I am not sure why this issue exists, it seems like permission issues for Service Account, So based on my limited knowledge of PowerShell I have set the below permissions but still no use, Can someone please help me with this issue.

UPDATE

The above error is resolved, but the script is not executing when executed from Jitterbit, no errors but it's just not executing, however, it is executing if I log in to the remote server with my login and execute it from the PowerShell tool itself. my guess is still the same it has to do something with permissions for the service account which is blocking the script to execute.

Answer 1

Two suggestions to try: load in the dependency as the first step of the script or, if it is truly a permission issue like you think, check your service account's permissions.

First, you can load any assembly using Add-Type. This can be useful if you want to use .NET classes in a C#-styled manner or for the rare issue that it complains about not finding a dependency (especially if it is not located in any directory on your $ENV:Path or in the current working directory).

Second, you can check your permissions. This is different based on how you are connecting. If it was a standalone script, I would expect you to be using Invoke-Command whereby you should check what Authentication method you are using. PowerShell comes with CredSSP by default but it has to be turned on (there are many places to find help including Microsoft's documentation). Other supported to note are Kerberos and ssh.

I am not familiar with Jitterbit so it would be a good idea to check their documentation to find out how they are remoting. I expect it likely remotes in, and then executes the PowerShell so you can check to ensure its permissions by running a simple script

Write-Host "Environment Path:" -ForegroundColor Green
Write-Host $ENV:Path
Write-Host "Current Working Directory Contents:" -ForegroundColor Green
Get-ChildItem | Write-Host

You may also want to investigate where the script executing lives, whether on the remote machine or on the executing machine and marshalled over the network.