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"

If you run Sqlcmd from a command prompt with the -U username -P p@ss parameters can you connect to the SQL Server? — Bruce
– Bruce, Commented Mar 11, 2014 at 20:32
Have you verified that you have the username & password correct? — alroc
– alroc, Commented Mar 11, 2014 at 21:11
Yes, they work when I log in through SQL Server Management Studio — snoop
– snoop, Commented Mar 11, 2014 at 21:21
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. — HAL9256
– HAL9256, Commented Mar 11, 2014 at 22:02

Benjamin BALET · Accepted Answer · 2016-08-25 11:37:27Z

10

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!'

answered Aug 25, 2016 at 11:37

Benjamin BALET

9691 gold badge11 silver badges36 bronze badges

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

Comments

r14 · Accepted Answer · 2023-03-20 14:35:31Z

0

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.

answered Mar 20, 2023 at 14:35

r14

11 bronze badge

Collectives™ on Stack Overflow

Powershell Invoke-Sqlcmd Login Failed

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related