[Solved] Powershell Invoke-Sqlcmd Login Failed

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:SQLServer
Invoke-sqlcmd "SELECT DB_NAME() AS hist;" -username "username" -password "[email protected]"

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 "[email protected]"
Enquirer: snoop

||

Solution #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 = "[email protected]$ord!"

But if you use single quotes, it will work:

$pw = '[email protected]$ord!'

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

Invoke-Sqlcmd ... -Password "[email protected]$ord!"

You should write:

Invoke-Sqlcmd ... -Password '[email protected]$ord!'
Respondent: snoop

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.