Invoke-Sqlcmd error : Login failed for user xxxx when running powershell to call remote DB server

Question

I getting Invoke-SqlCmd : Login failed for user 'DIR\MISAccount'.when I executed powershell script. The powershell script will read .sql file and executed statement defined in .sql file. I've tested the powershell and .sql script on my local machine (app and db in same box), and the scripts is working as expected.

Now I getting the error when executed the scripts on server, the difference here is the powershell need to call the remote db server. I've checked below

Connect via SSMO from app server - Worked, Connect via CMD SQLCMD from app server - Worked, Run powershell script on DB server - Worked

Based on above test, I believe the problem is the credential from Powershell (running in app server) to remote DB server, but I have no clue to solve it. Any help would highly appreciate.

Here the powershell script

$LogTime = Get-Date -Format "MM-dd-yyyy_hh-mm-ss"
$SqlFile = "E:\User\Script_CreateTable.sql"
$OutputFile = "E:\User\CreateTable.log"
$Path = "E:\User\Password.txt"
$uid = 'sa'
$ConnectionString = "Server=WINPRD212\PROD_INC;Database=AccountMEC;"

$pwd = Get-Content $Path | ConvertTo-SecureString
$pwd.MakeReadOnly()


$creds = New-Object System.Data.SqlClient.SqlCredential($uid,$pwd)

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $ConnectionString
$con.Credential = $creds
$con.Open()


Invoke-SqlCmd -InputFile $SqlFile -verbose

$con.Close()

Answer 1

Problem solved by below method:

$creds = New-Object System.Management.Automation.PSCredential $uid,$pwd
$Password = $creds.GetNetworkCredential().Password

Invoke-SqlCmd -InputFile $SqlFile -Username 'sa' -Password $Password -ServerInstance 'WINPRD212\PROD_INC' -Database 'AccountMEC'