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() 2 Answers
I was getting same error because my password had some special characters. I used single quotes around it and it worked.
Invalid : Invoke-Sqlcmd -Database test -ServerInstance sql-test.database.windows.net -Username testadmuser -Password d0F0$yhi]L<beB=y -InputFile C:\Users\abc.sql
Valid: Invoke-Sqlcmd -Database test -ServerInstance sql-test.database.windows.net -Username testadmuser -Password 'd0F0$yhi]L<beB=y' -InputFile C:\Users\abc.sql
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' 
