使用不同的凭据调用 SQLCmd
背景: 我正在本地运行一个脚本,该脚本必须以系统身份运行,我们不解释为什么会这样。 :) 该脚本尝试通过一个简单的查询来检查我的 MSSQL 集群的运行状况。然而,我遇到的问题是本地系统帐户无权访问远程数据库。目前,我已经尝试了很多方法,稍后我将介绍这些方法,但老实说,我愿意寻求任何有意义的解决方案。如果这意味着在数据库中创建一个可以回答我的简单查询的本地帐户,那也很好。
到目前为止,我得到的是:
$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 身份验证,我想,但它不使用-凭据。所以然后我尝试使用 Invoke-Command 作为包装器。
$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:无法使用指定的命名参数解析参数集。
所以..我陷入困境。有什么想法吗?
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
The result: Invoke-Sqlcmd : Login failed for user 'myDomain\myUsername'
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
Which got me: Invoke-Command : Parameter set cannot be resolved using the specified named parameters.
So.. I'm stuck. Any thoughts?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
我使用 Get-Credential,然后使用 Invoke-Command -AsJob 从 .sql 文件运行脚本。例如
,但请注意,$cred 行将启动一个提示来确认/验证凭据 - 我还在脚本中提供了数据库名称,所以我不确定如何更改此逻辑以将脚本定向到任何数据库(如果它需要根据每个服务器进行自定义)。
I use Get-Credential and then Invoke-Command -AsJob to run a script from a .sql file. e.g.
Note however that the $cred line will launch a prompt to confirm/authenticate the credential - I also had the database name in the script, so I'm not sure how you would change this logic to direct your script at any database (if it needed to be custom per server).
对于此类事情,我们通过 RUNAS /NETONLY
不确定 Powershell 是否有等效的东西(也许只是使用 RUNAS /NETONLY 运行 Powershell)。如果您要将其构建到应用程序中,我们将使用 Windows API 重新启动来执行此操作:如何将 RUNAS /NETONLY 功能构建到 (C#/.NET/WinForms) 程序中?
另外:http://www.theinfraguy.com/2011/08/三种用途-for-runas-netonly.html
For this kind of thing we had good success with RUNAS /NETONLY
Not sure if Powershell has something equivalent (maybe just run the Powershell with RUNAS /NETONLY). If you are building this into an app, we relaunched using the Windows API to do this: How to build RUNAS /NETONLY functionality into a (C#/.NET/WinForms) program?
Also: http://www.theinfraguy.com/2011/08/three-uses-for-runas-netonly.html
如果您还没有检查用户是否有权访问数据库:-)如果
他这样做了,您可以回避问题并使用 .net 对象 - 不是那么简洁,并且可能有其他问题,但可以工作,因为您可以使用连接字符串。
这是未经测试的:-)
if you haven't already check the user has access to the DB :-)
If he does you could side step the issue and use .net objects - not as succinct and may have other issues but could work as you can use a connection string.
This is untested :-)
遗憾的是,我尝试了所有这些(包括马特的建议),然后尝试了一些,但我无法让它在以下参数下工作。
1) 该脚本是从以 SYSTEM 帐户运行的服务启动的。 2) 系统位于与 SQL 集群不同的域/子网/等中。 3) 查询必须实时运行并实时返回,因为查询只是较大脚本的一部分。
现在我已经认输了,创建了一个本地 SQL 登录来使用,然后按原样使用 Invoke-SQL 以及 -username 和 -password 选项。这不是我想要处理这种情况的方式,但事实就是如此。谢谢大家!
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.
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!
您可以尝试提交作业并传递凭据。
这对我有用。
You can try submitting a job and passing credentials.
This worked for me.
无需远程登录即可运行 SQL 查询。您可以使用以下函数并根据需要传递变量。无论哪个帐户具有访问权限,您都可以将其作为凭据传递。 (适用于 Windows 和 SQL 身份验证)
希望有帮助。
There is no neeed to login remotely to run an SQL query. You can use the below function and pass the variables as required. Whichever account have access you can pass as credentials. (Works for both Windows and SQL Authentication)
Hope it HElps.