PowerShell:使用 Get-Credential 调用 sqlcmd 不起作用 [英] PowerShell: invoke-sqlcmd with Get-Credential doesn't work

问题描述

我从未见过如此简单的脚本如此失败:

I've never seen a so easy script failing so royally:

$SQLServer = "localhost"
$cred = Get-Credential
invoke-sqlcmd -ServerInstance $SQLServer -Credential $cred -Query "select @@version"

这句话说 -Credentials 不被识别:

the phrase says -Credentials is not recognized:

Invoke-Sqlcmd : A parameter cannot be found that matches parameter name 'Credential'.

那么拥有 Get-Credential 有什么意义?我在网上看到很多例子，他们都是这样使用的.

So what is the point to have Get-Credential? I see a lot of examples on internet and they all use it this way.

编辑示例:为什么此代码与 -Credential 一起使用?因为 -Credential 是在函数内部?

EDIT EXAMPLE: why this code is working with -Credential? Because -Credential is inside a function?

function Pause ($Message="Press any key to continue..."){ 
    "" 
    Write-Host $Message 
    $null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") 
} 

function GetCompName{ 
    $SQLServer = Read-Host "Please enter a computer name or IP" 
    CheckHost 
} 

function CheckHost{ 
    $ping = gwmi Win32_PingStatus -filter "Address='$SQLServer'" 
    if($ping.StatusCode -eq 0){$pcip=$ping.ProtocolAddress; GetCollation} 
    else{Pause "Host $SQLServer down...Press any key to continue"; GetCompName} 
} 


function GetCollation {
    #Provide Database Name 
    $DatabaseName ="master"
    #Prompt for user credentials 
    $credential = Get-Credential

    $Query = "SELECT name, collation_name FROM sys.databases;  " 

    invoke-sqlcmd -ServerInstance $SQLServer -Database $DatabaseName -Credential $credential  -Query $Query | Format-Table

}
#---------Start Main-------------- 
$SQLServer = $args[0] 
if($SQLServer){CheckHost} 
else{GetCompName}

推荐答案

该问题可能是由于 Microsoft 有两个版本的 Invoke-Sqlcmd:

The issue could be resulting from the fact that Microsoft has two versions of Invoke-Sqlcmd:

1. 数据库引擎 - no -Credentials 参数.
2. SqlServer 模块 - -Credentials 参数 is 可用.

1. The Database Engine - no -Credentials parameter.
2. The SqlServer module - -Credentials parameter is available.

查看了您最近的几个 SO 问题 - 看起来您有 数据库引擎 版本.SqlServer 模块是默认情况下未安装，所以你必须这样做手动.(上一个超链接中有一个注意"部分，解释了这个问题背后的一些历史)

Looked at a couple of your recent SO questions - looks like you have the Database Engine version of the cmdlet. The the SqlServer module is not installed by default, so you have to do it manually. (there's a 'Note' section in previous hyperlink that explains some of the history behind this issue)

简而言之，运行以下命令以获取 SqlServer 模块:

In a nutshell, run the following command to get the the SqlServer module:

Install-Module -Name SqlServer -AllowClobber

确保包含 -AllowClobber 开关.这是一个愚蠢的安装程序，如果您关闭开关，它将下载约 24MB 的包，然后失败，因为它会覆盖数据库引擎版本.

Make sure to include the -AllowClobber switch. It's a dumb-installer, and if you leave off the switch it will download the ~24MB package and then fail because it's overwriting the database engine version.

这篇关于PowerShell:使用 Get-Credential 调用 sqlcmd 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！