locked
Remote PowerShell RRS feed

  • Question

  • Hello Everybody:

    I recently created an SPN for SQL and it had the weirdest effect on my SSIS packages that run PowerShell scripts remotely with an "Invoke-Command -ComputerName" command.  Some of the script blocks are constructed with the executable {python 'path_to_script'}, while some others had the full path to the executable {C:\python27\python 'path_to_script'}.

    The Invoke-Commands with the full path to the executable worked the same with and without the SPN.  The one with just the executable suddenly stopped working with the error that the executable was not valid.  Once I removed the SPN, they started working again.  I tried manually running the PowerShell sessions manually and got the same error, so I'm thinking the SSIS involvement was not relevant to the issue.

    Why would an SPN for MSSQLsvc affect the way the Invoke-Command script block works?  

    Completely Mystified,

    Simon


    Cheers and thanks, Simon

    • Moved by Bill_Stewart Tuesday, April 14, 2020 4:30 PM Off-topic
    Friday, April 12, 2019 5:51 PM

All replies

  • I recommend that this be handled in the SQLServer forum since it is executing via SSIS.


    \_(ツ)_/

    Friday, April 12, 2019 6:07 PM
  • I tried manually running the PowerShell sessions and got the same error, so even when removing SSIS and SQL Server from the process, the error still occurred until I removed the SPN.

    Cheers and thanks, Simon

    Friday, April 12, 2019 6:15 PM
  • What SPN are you referring to? It sounds like the SPN is incorrect.


    \_(ツ)_/

    Friday, April 12, 2019 6:22 PM
  • Are you logging off and then back on to the SQL server after you set the SPN? Rebooting the server after setting the SPN?  Anything unusual in the security eventlog on the target system 

    The SPN must be causing a conflict with Kerberos authentication. Try purging the related tickets with the klist command. 

    https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/klist

    • Proposed as answer by jrv Friday, April 12, 2019 7:49 PM
    Friday, April 12, 2019 7:40 PM
  • Are you logging off and then back on to the SQL server after you set the SPN? Rebooting the server after setting the SPN?  Anything unusual in the security eventlog on the target system 

    The SPN must be causing a conflict with Kerberos authentication. Try purging the related tickets with the klist command. 

    https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/klist

    I did clear the tickets from both the SQL server and the targeted server of the remote powershell, although I didn't reboot.  It was several hours later that the jobs with the PowerShell remote calls began failing - but only those with the executable for python without the full path.  Those calls started working again as soon as I removed the SPN (which was for MSSQLsvc for the credentials running SQL and the PowerShell calls).  I just don't understand why an SPN that was not for http or https could affect remote PowerShell.

    Cheers and thanks,

    Simon


    Cheers and thanks, Simon

    Friday, April 12, 2019 9:37 PM
  • If you're really curious, you could try running these commands remotely with that account. Where.exe will search the system path for the executable, and the PS commands will list off the folders in the path and the number of pyth files in them.  It will at least show you where its finding python. 

    where.exe python 
    $env:path.Split(";") | foreach { "{0} - {1}" -f (Get-ChildItem $_ -Filter 'pyth*').count, $_ }

    Saturday, April 13, 2019 12:11 PM