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

Chinmay Biswal · Accepted Answer · 2021-07-05 13:44:46Z

4

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

answered Jul 5, 2021 at 13:44

Chinmay Biswal

4236 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whytheq · Accepted Answer · 2021-07-12 17:22:41Z

3

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'

edited Jul 12, 2021 at 17:22

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

answered May 4, 2016 at 5:45

user664481

2,3219 gold badges26 silver badges32 bronze badges

1 Comment

Sellorio Over a year ago

Please elaborate, what is going on here?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related