[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]"
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!'