Powershell Invoke-Sqlcmd 登录失败 [英] Powershell Invoke-Sqlcmd Login Failed

问题描述

我正在尝试从 Windows SQL Server 2008 R2 的 powershell 运行 sql 查询.代码似乎无法通过 SQL Server 身份验证凭据以正确运行 sqlcmd.我直接从 powershell 运行第一部分，而不是作为脚本.

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

我也试过 convert-tosecurestring -asplaintext 没有成功.

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

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

推荐答案

切勿对包含密码的变量使用双引号，因为会解释特殊字符(对于包含特殊字符的用户名也是如此).单引号字符串不是这种情况.

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

你应该写:

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

这篇关于Powershell Invoke-Sqlcmd 登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！