Powershell Invoke-Sqlcmd Login Failed

Question

I am trying to run a sql query from powershell for Windows SQL Server 2008 R2. It looks like the code is failing to pass SQL Server Authentication credentials to run the sqlcmd correctly. I am running this first part directly from powershell, not as a script.

Set-Location SQLServer:\SQL\Server\
Invoke-sqlcmd "SELECT DB_NAME() AS hist;" -username "username" -password "p@ss"

I receive error, Login failed for user 'username'. The ideal end result for the code format would look more like this. Same resulting error.

$path = "sqldbdump.csv"
$server = "serveronlocalhost"
$db = "hist"
$Query = @"
SELECT * From alarms;
"@
Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $username -Password $pw -Query $Query | Export-CSV $path

I have also tried convert-tosecurestring -asplaintext with no success.

$pw = convertto-securestring -AsPlainText -Force -String "p@ss"

• Bruce: If you run Sqlcmd from a command prompt with the -U username -P p@ss parameters can you connect to the SQL Server?
• snoop: Same error, Login failed for user 'username'.
• alroc: Have you verified that you have the username & password correct?
• snoop: Yes, they work when I log in through SQL Server Management Studio
• HAL9256: Do you have any firewalls on? or blocking port 1433? For ex. to confirm, log in through SQL Server Management Studio from your workstation, not from the server.

Answer 1

Never use double quotes for a variable containing a password because special characters are interpreted (the same for username containing special characters). It is not the case with single quoted strings.

For example, this password will cause an authentification error because the character $ is evaluated:

$pw = "p@ss$ord!"

But if you use single quotes, it will work:

$pw = 'p@ss$ord!'

Please note that the same principle applies to command lines, so this wil not work:

Invoke-Sqlcmd ... -Password "p@ss$ord!"

You should write:

Invoke-Sqlcmd ... -Password 'p@ss$ord!'

Answer 2

For everyone that might experience this issue in the future. Run your Invoke-SqlCmd directly through the PowerShell terminal and not through a Text Editor like vsCode. If it works, change the encoding of your script from UTF-8 to UTF-8 with BOM.

Some special characters are getting messed up when used in UTF-8 format, that's why you need to use UTF-8 with BOM.