Install SP/CU on a SQL Server remotely using CredSsp

PowerShell Help
1

Dear All,

I will really appreciate if someone could take a look and point me in the right direction for the resolution of described below issue.
I’m trying to install service packs and cumulative updates on multiple SQL servers via PowerShell using existing script which I’ve found in this forum post.
Author’s gitHub

Issue:

PS C:\Users\username\Desktop\SQL Patching> C:\Users\username\Desktop\SQL Patching\SqlUpdater.ps1
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
VERBOSE: Installing SP on [TargetComputerName]
VERBOSE: Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT SystemType FROM Win32_ComputerSystem,'queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: Operation 'Query CimInstances' complete.
VERBOSE: The server [TargetComputerName's'] service pack [] is older than [2]
VERBOSE: Using filter [SQLServer2014-SP2-(.+)?x64\.exe$]...
VERBOSE: Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT BuildNumber,CSName FROM Win32_OperatingSystem,'queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: Operation 'Query CimInstances' complete.
VERBOSE: Acceptable success return codes are [0,3010]
VERBOSE: Running command line [\\ServerWithPatches\repository\MDT_Repository\Patches\Microsoft\SQL\2014\Updates\SQLServer2014-SP2-x64.exe /extract:"C:\Windows\Temp\SQLSP" /quiet] on TargetComputerName
Invoke-Program : The WinRM client cannot process the request. Requests must include user name and password when CredSSP authentication mechanism is used. Add the user name and password or change the authentication mechanism and try the request again.  
At C:\Users\username\Desktop\SQL Patching\SqlUpdater.ps1:242 char:21
+                     Invoke-Program @connParams -FilePath $installer.FullName -Ar ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-Program
 
VERBOSE: Acceptable success return codes are [0,3010]
VERBOSE: Running command line [C:\Windows\Temp\SQLSP\setup.exe /q /allinstances] on TargetComputerName
Invoke-Program : The WinRM client cannot process the request. Requests must include user name and password when CredSSP authentication mechanism is used. Add the user name and password or change the authentication mechanism and try the request again.  
At C:\Users\username\Desktop\SQL Patching\SqlUpdater.ps1:245 char:21
+                     Invoke-Program @connParams -FilePath "$spExtractPath\setup.e ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-Program
 
VERBOSE: Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT BuildNumber,CSName FROM Win32_OperatingSystem,'queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: Operation 'Query CimInstances' complete.
VERBOSE: Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT SystemType FROM Win32_ComputerSystem,'queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: Operation 'Query CimInstances' complete.
Install-SqlServerCumulativeUpdate : Cannot validate argument on parameter 'ServicePackNumber'. The 0 argument is less than the minimum allowed range of 1. Supply an argument that is greater than or equal to 1 and then try the command again.
At C:\Users\username\Desktop\SQL Patching\SqlUpdater.ps1:934 char:13
+             Install-SqlServerCumulativeUpdate @cuParams
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SqlServerCumulativeUpdate

What I’ve already done:
Enabled CredSSP on the client and server via the following commands:

#on the machine where I run the script
#Enable-WSManCredSSP -Role Client -DelegateComputer " TargetComputerName.domain.com" -Force

#on the target machine
#Enable-WSManCredSSP -Role Server –Force

Test works just fine

Enter-PSSession -ComputerName " TargetComputerName " -Authentication Credssp -Credential (Get-Credential)
Test-Path -Path "\\aShare"

Imgur

Thanks in advance for every input that will bring us close to resolving the issue and making this beautiful script work as intended.

2

Yeah, but in your test, you’re providing -Credential. I’m suspecting the DSC resource either isn’t, or it’s got a null credential it’s attempting to use.

3

Dear Don,

Thank you for you reply, but the script isn’t using DSC at all… It’s attempting to install remotely located executable by accessing it via UNC path(reason it needs CredSSP).

4

Sorry, it’s using Remoting. It may be copying a file by UNC path (which would be SMB, not CredSSP), but it’s quite definitely using Remoting to execute it, as of course are you with Enter-PSSession.

The error’s pretty clear - Remoting isn’t being given a credential.