PowerShell activities ("Is Device in Collection", "Add to Device Collection", etc) on SCCM CDS workflow "Deploy SCCM Application" intermittently fail with error "Authentication Failure with the local MID Server service credential".

This happens after additional SMS Provider is installed in the environment.

(The SMS Provider is a Windows Management Instrumentation (WMI) provider that assigns read and write access to the Configuration Manager database at a site)

The issue should be reproducible by running below powershell commands repeatedly on MID Server host, which will fail with authentication error intermittently:

$cred = get-credential;
$computer = "[SCCMServerFQDN]";
$session = New-PSSession -ComputerName $computer -Credential $cred;
Invoke-Command -Session $session -ScriptBlock {Import-Module -Name "$(split-path $Env:SMS_ADMIN_UI_PATH)\ConfigurationManager.psd1";}
remove-pssession $session

The issue is caused by the additional SMS provider added.
When we are running remote powershell command, we use a defined credential object to connect to the SCCM server, then from the SCCM server it's trying to load module and authenticate the stored credential to another SMS server. This new SMS server is considered as a second node, and this is going into a second-hop situation, which is documented in below Microsoft Article:
https://devblogs.microsoft.com/scripting/enable-powershell-second-hop-functionality-with-credssp/

In order to make it work, you would need to run Enable-WSManCredSSP command on MID server host, and Enable-WSMaCredSSP on target SCCM, in order to enable CredSSP.
Please refer to the Microsoft article below which has very detailed solution and a flow chart which makes it easier to understand where and how to apply these commands:

https://devblogs.microsoft.com/scripting/enable-powershell-second-hop-functionality-with-credssp/

Then on your ServiceNow instance, navigate to MID Server > Script Files > SCCM.psm1 > modify below line
from:
$session = New-PSSession -ComputerName $sccmServerName -ConfigurationName Microsoft.PowerShell32 -Credential $credential;
to:
$session = New-PSSession -ComputerName $sccmServerName -ConfigurationName Microsoft.PowerShell32 -Credential $credential -Authentication CredSSP;

Also for another script file: Credentials.psm1 (the one with parent as PowerShell), 

locate function testCredentialSCCM, and modify below line

from:

 $session = CreatePSSessionWithComputerName -Host $computer -ConfigurationName Microsoft.PowerShell32 -Credential $cred;

to:

$session = New-PSSession -ComputerName $computer -ConfigurationName Microsoft.PowerShell32 -Credential $cred -Authentication CredSSP;