error with SQL connection during installing MSI package with PowerShell script

Question

I am trying to write a script in powershell that will install 10 servers on virual machines. It's just simple script where I'm specifying list of arguments, path of MSI package and run passive installation. If I do this manually logged as the same user that is running powershell script with the same arguments everything is fine and application is installed successfully but if I'm running the script all the time I'm getting SQL error with connecting to database. Log pasted below.

PowerShell script:

$installerPath = "$destinationPath\$application`_$versionToInstall.msi"

$sqlAuthType="SQL_AUTH"

$arguments = SQL_AUTH_TYPE = $sqlAuthType

            $exitCode = Invoke-Command -ComputerName $serverToInstall -ScriptBlock {(Start-Process -FilePath "msiexec.exe" -ArgumentList $Using:internalArgs -Wait -Passthru).ExitCode}



                if(($exitCode -eq 0) -or ($exitCode -eq 3010)){
                    WriteLog("$application is installed succesfully")

MSI logs from %temp%

PROPERTY CHANGE: Modifying LOG property. Its current value is 'RUNTIME AUTH TYPE USED TO DATABASE LOGIN: 'WINDOWS_AUTH'. 

'CONNECTION STRING 'Data Source=sqlserver\SQL1;Initial Catalog=master;Integrated Security=True;Connect Timeout=10'.

and after this log I'm getting this error:

PROPERTY CHANGE: Adding SQL_ERROR_DETAILS property. Its value is 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'.
CustomAction SilentVerifySqlConnection returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)

and just to test it if I run simple powershell script like this (I'm copying connection string created during my installation and trying to connect with it via PowerShell and its working):

$connString = "Data Source=sqlserver\SQL1;Initial Catalog=master;Integrated Security=True;Connect Timeout=10"
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connString

try {
    $conn.Open()
    Write-Host "success"
    $conn.Close()
} catch {
    Write-Host "Connection failed: $($_.Exception.Message)"
}

do you have any ides why it is happening? I checked a lot of topics in stackoverflow but nothing can help in this case.

Answer 1

What you are running into is what we call the Double Hop Issue.

When you log into another machine using Invoke-Command you are not truly logging in as yourself but as NT AUTHORITY\ANONYMOUS LOGON

When you are calling the SQL you are using your windows login as a Integrated Security=True

There are a few ways around this. This is not all the ways just the ones i prefer

Creating a PSSessionConfiguration on the remote computers. (I prefer this one) You can setup Configurations that will allow you to login as a user of your choosing. I usally setup a GMSA account for SREs to access Servers and shares.

You can get a configuration template from powershell using the Command New-PsSessionConfigurationFile

Register your file Register-PSSessionConfiguration

Then you can connect to the remote machine

Invoke-Command -ComputerName Test -ConfigurationName MyConfigNameHere -ScriptBlock { "Hey" }

Another way would be to use CredSSP (I personally haven't used this option)