0

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.

enter image description here

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.

Vikas J
  • 795
  • 3
  • 14
  • 31
  • Did you install the 64-bit versions of SharedManagementObjects.msi and SQLSysClrTypes.msi? (The `C:\Program Files\ ` path suggests "yes".) Are you running in a 64-bit PowerShell process? What does `[Environment]::Is64BitProcess` output in PowerShell? – AlwaysLearning Jan 06 '21 at 14:06
  • Hello @AlwaysLearning, yes I have installed the 64-bit version of SharedManagementObjects.msi and SQLSysClrTypes.msi and Powershell command which you shared [Environment]::Is64BitProcess returned True – Vikas J Jan 06 '21 at 14:34
  • What Service Account is your ETL application using? It sounds like this account is not provisioned appropriately on the remote server – Martin Cairney Jan 12 '21 at 07:34
  • @MartinCairney Sorry but I Didn't get your question completely. Yes, there is a service account that etl application uses and it has admin rights on the remote server. But even I am getting the feeling that somehow that service account is not able to communicate with a PowerShell script. So is there a way in Powershell itself wherein at least for testing I can give access to all users to access the script. – Vikas J Jan 12 '21 at 08:31
  • Does the ETL use a domain account as its Service Account? Does this domain account have permissions on the remote server? If you have the password, can you login to your own machine with the Service Account and test the remote execution of the script? If it is a Virtual Account then it will not have access on the remote machine and so you won't be able to execute the PowerShell commands. This is a first step - once you know it has access then it needs to have access to the modules – Martin Cairney Jan 12 '21 at 11:07

1 Answers1

0

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.

carrvo
  • 511
  • 5
  • 11