使用不同的凭据调用 SQLCmd [英] Invoke-SQLCmd with Different Credential

问题描述

背景:我正在本地运行一个脚本，该脚本必须以 SYSTEM 身份运行，让我们不讨论为什么会这样.:)该脚本尝试通过一个简单的查询来检查我的 MSSQL 集群的运行状况.然而，我遇到的问题是本地 SYSTEM 帐户无权访问远程数据库.在这一点上，我已经尝试了很多事情，我稍后会介绍，但老实说，我愿意寻求任何有意义的解决方案.如果这意味着在数据库中创建一个可以回答我的简单查询的本地帐户，那也很好.

Background: I am running a script locally that has to be run as SYSTEM, lets not get into why that is. :) The script attempts to check the health of my MSSQL cluster with a simple query. The problem I am running into however is that the local SYSTEM account doesn't have access to the remote database. At this point I've tried a number of things, which I'll get into in a moment, but I'm honestly up for any solution that makes sense. If it means creating a local account in the database that can answer my simple query that's fine too.

到目前为止我所拥有的:

There is what I have so far:

$Server = 'myserver.domain.tld'
$Database = 'myDatabase'
$Query = 'SELECT DB_NAME() AS DataBaseName'
$Username = 'myDomain\myUsername'
$Password = 'myPasswordWithPlainText'
Invoke-SQLCmd -ServerInstance $Server -Database $Database -ConnectionTimeout 300 -QueryTimeout 600 -Query $Query -Username $Username -Password $Password

结果:Invoke-Sqlcmd : Login failed for user 'myDomain\myUsername'

也许 Invoke-SQL 不采用我认为的 Windows 身份验证，但它不使用 -Credential.于是我尝试使用 Invoke-Command 作为包装器.

Maybe Invoke-SQL doesn't take Windows authentication I thought, but it doesn't use -Credential. So then I tried to use Invoke-Command as a wrapper.

$Server = 'myserver.domain.tld'
$Database = 'myDatabase'
$Query = 'SELECT DB_NAME() AS DataBaseName'
$Username = 'myDomain\myUsername'
$Password = 'myPasswordWithPlainText'
$secpasswd = ConvertTo-SecureString $Password -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($Username, $secpasswd)
Invoke-Command -script {Invoke-SQLCmd -ServerInstance $Server -Database $Database -ConnectionTimeout 300 -QueryTimeout 600 -Query $Query} -Credential $Credential

这让我知道:Invoke-Command:无法使用指定的命名参数解析参数集.

所以……我被卡住了.有什么想法吗?

So.. I'm stuck. Any thoughts?

推荐答案

遗憾的是我尝试了所有(包括 Matt 的建议)，然后尝试了一些，但我无法在以下参数下使其工作.1) 该脚本是从作为 SYSTEM 帐户运行的服务启动的.2)系统与SQL集群位于不同的域/子网/等中.3) 查询必须实时运行并实时返回，因为查询只是较大脚本的一部分.

Sadly I tried it all (including Matt's suggestion) and then some and I just cant get it to work under the following parameters. 1) The script is launched from a service running as the SYSTEM account. 2) The system is in a separate domain/subnet/etc than the SQL cluster. 3) The query has to run real time and return in real time as the query is only part of a larger script.

现在我已经认输并创建了一个本地 SQL 登录来使用，然后将 Invoke-SQL 与 -username 和 -password 选项一起使用.这不是我想如何处理这种情况，但事实就是如此.谢谢大家！

For now I've thrown in the towel and created a local SQL login to use then use Invoke-SQL as is with the -username and -password options. It is not how I wanted to handle the situation BUT it is what it is. Thank you all!

这篇关于使用不同的凭据调用 SQLCmd的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！