Powershell script on SQL agent no longer working :: Support Forum

Kai_H
Joined:: 2019-10-05
Posts:: 5
Location:: Canada

Powershell script on SQL agent no longer working

2019-10-05 15:35

Hi,

I created a powershell script that is scheduled in SQL Agent(2012), it is one of many steps in a process to download and process data. Since Oct 2 this script is no longer executed - on this day Security Update for Windows (KB4522007)
- it appears that the library cannot be loaded when executed from SQL agent - I can execute the script as user logged in on the machine via RDP or a scheduled task in the Windows task scheduler. I suspect that this the access to dll files has been restricted to the SQL agent as a measure of increasing security. How else can I refer to the assembly? Can I move the file(s) to another location that SQL agent has access to?

Below the error message:

PowerShell script. The corresponding line is ' Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\WinSCP\WinSCPnet.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. '. Process Exit Code -1. The step failed.\

Thanks for any hint what I could do to fix this.

Kai

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: Powershell script on SQL agent no longer working

2019-10-09

Please read documentation:
https://winscp.net/eng/docs/message_net_runtime_newer_than_currently_loaded

Reply with quote

Kai_H
Joined:: 2019-10-05
Posts:: 5
Location:: Canada

Powershell script on SQL agent no longer working

2019-10-11 21:56

I downloaded .Net framework update package and Powershell install but the error still occurs.
the current Power shell version is 5.1.14409.1005 - below the installed .Net frameworks.

PSChildName                      Version        Release
-----------                      -------        -------
v2.0.50727                       2.0.50727.4927        
v3.0                             3.0.30729.4926        
Windows Communication Foundation 3.0.4506.4926         
Windows Presentation Foundation  3.0.6920.4902         
v3.5                             3.5.30729.4926        
Client                           4.6.01055      394271 
Full                             4.6.01055      394271 
Client                           4.0.0.0

The code below executes from powershell and can be called from Microsoft scheduler, but when executed in SQL server Agent I get the error message. This has been working for years - this error started on Oct 3rd - on this day the security update below was installed, not sure if that caused the error by restricting access to the library.
Here is the code that I have been using - I inserted the set Complus line and referred to the newer .net build that is installed - but this does not make a difference.

set COMPLUS_version=v4.6.01055.394271
 
# Load WinSCP .NET assembly
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"
 
# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::sftp
    HostName = "deleted"
    UserName = "deleted"
    Password = "deleted"
    SshHostKeyFingerprint = "deleted"
    }
 
  $session = New-Object WinSCP.Session
 
 
    # Connect
    $session.Open($sessionOptions)
 
   # set filenames - if executed on a monday subtract three days
 
   if((get-date).dayofweek -match 'Monday')
   {$filedate = (Get-Date).adddays(-3)}     
   else
   {$filedate = (Get-Date).adddays(-1)}
 
   $file1 = 'TORON_RH' + $filedate.ToString('yyyyMMdd') + '.csv'
   $file2 = 'TORON_RC' + $filedate.ToString('yyyyMMdd') + '.csv'
   $file3 = 'TORON_RT' + $filedate.ToString('yyyyMMdd') + '.csv'
 
    # Download files
    $transferOptions = New-Object WinSCP.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
 
    $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file1, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
        $transferResult.Check()
 
   $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file2, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
        $transferResult.Check()
 
   $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file3, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
        $transferResult.Check()
       
 
    # Disconnect, clean up
    $session.Dispose()

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 41,442
Location:: Prague, Czechia

Re: Powershell script on SQL agent no longer working

2019-10-15

What does this print, if you put it to your PowerShell script?

[Environment]::Version

Also the set COMPLUS_version has to be set before you start PowerShell. Not in the PowerShell script itself - that's too late. You can for example wrap your PowerShell execution to a batch file:

set COMPLUS_version=v4.6.01055.394271
powershell -File script.ps1

Reply with quote

Kai_H
Joined:: 2019-10-05
Posts:: 5
Location:: Canada

2019-10-15 13:48

This is the version

Major  Minor  Build  Revision
-----  -----  -----  --------
4      0      30319  42000

Reply with quote

Kai_H
Joined:: 2019-10-05
Posts:: 5
Location:: Canada

2019-10-15 16:24

I have tried running the code below as batch script

set COMPLUS_version=v4.0.0.0
powershell -File C:\Users\sysadmin.kai\Desktop\test.ps1

which gives me error

Version v4.0.30319 of the .NET Framework is not installed and it is required to
run version 3 of Windows PowerShell.

I downloaded .Net 4.5 and tried to install this version and I am getting the message below:

Microsoft .NET Framework 4 is already a part of this operating system. You do not need to install the .NET Framework 4 redistributable. <A HREF="http://go.microsoft.com/fwlink/?LinkId=164207">More information</A>.

I am at a loss what I need to do now...

Reply with quote

martin◆ Site Admin

2019-10-16

You need to specify an exact version of the framework in the COMPLUS_version, not v4.0.0.0.

Reply with quote

Kai_H
Joined:: 2019-10-05
Posts:: 5
Location:: Canada

issue resolved

2019-10-17 17:43

Hi Martin,

I was able to resolve the issue after I added the two keys below forcing to use the latest version

reg add hklm\software\microsoft\.netframework /v OnlyUseLatestCLR /t REG_DWORD /d 1
reg add hklm\software\wow6432node\microsoft\.netframework /v OnlyUseLatestCLR /t REG_DWORD /d 1

found at https://stackoverflow.com/questions/2094694/how-can-i-run-powershell-with-the-net-4-runtime

Thanks for your help.

Reply with quote