Install SP/CU on a SQL Server remotely using CredSsp

This topic contains 3 replies, has 2 voices, and was last updated by  Don Jones 3 days, 5 hours ago.

  • Author
    Posts
  • #98919

    Dmitriy
    Participant

    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" 
    
    

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

  • #98922

    Don Jones
    Keymaster

    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.

  • #98923

    Dmitriy
    Participant

    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).

  • #98925

    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.