使用不同的凭据调用 SQLCmd

发布于 2024-11-30 15:18:27 字数 1340 浏览 0 评论 0原文

背景: 我正在本地运行一个脚本,该脚本必须以系统身份运行,我们不解释为什么会这样。 :) 该脚本尝试通过一个简单的查询来检查我的 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

蓝颜夕 2024-12-07 15:18:27

我使用 Get-Credential,然后使用 Invoke-Command -AsJob 从 .sql 文件运行脚本。例如

$s = 'myserver.domain.tld';
$scriptpath = 'C:\myfile.sql';
$cred = Get-Credential -credential domain\user;
$sess = New-PSSession -ComputerName $s -Credential $cred -Authentication CredSSP -Name 'S1';
$job1 = Invoke-Command -Session $sess -FilePath $scriptpath -AsJob -JobName 'J1';
  # -ArgumentList $args;

Get-Job | Wait-Job;
Get-Job -Name 'J1';

Get-PSSession | Remove-Session;
Get-Job | Remove-Job;

,但请注意,$cred 行将启动一个提示来确认/验证凭据 - 我还在脚本中提供了数据库名称,所以我不确定如何更改此逻辑以将脚本定向到任何数据库(如果它需要根据每个服务器进行自定义)。

I use Get-Credential and then Invoke-Command -AsJob to run a script from a .sql file. e.g.

$s = 'myserver.domain.tld';
$scriptpath = 'C:\myfile.sql';
$cred = Get-Credential -credential domain\user;
$sess = New-PSSession -ComputerName $s -Credential $cred -Authentication CredSSP -Name 'S1';
$job1 = Invoke-Command -Session $sess -FilePath $scriptpath -AsJob -JobName 'J1';
  # -ArgumentList $args;

Get-Job | Wait-Job;
Get-Job -Name 'J1';

Get-PSSession | Remove-Session;
Get-Job | Remove-Job;

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).

夏末染殇 2024-12-07 15:18:27

对于此类事情,我们通过 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

暖阳 2024-12-07 15:18:27

如果您还没有检查用户是否有权访问数据库:-)如果

他这样做了,您可以回避问题并使用 .net 对象 - 不是那么简洁,并且可能有其他问题,但可以工作,因为您可以使用连接字符串。

这是未经测试的:-)

$ConnectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$command = New-Object System.Data.SqlClient.SqlCommand;

$command.Connection = $connection;
$command.CommandType = [System.Data.CommandType]::Text;
$command.CommandText = "SELECT DB_NAME() AS DataBaseName";

$connection.open();

$reader = $command.ExecuteReader()

While($reader.Read()){
    # do something with $reader
}

$connection.Close()
$reader.Close()

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 :-)

$ConnectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
$command = New-Object System.Data.SqlClient.SqlCommand;

$command.Connection = $connection;
$command.CommandType = [System.Data.CommandType]::Text;
$command.CommandText = "SELECT DB_NAME() AS DataBaseName";

$connection.open();

$reader = $command.ExecuteReader()

While($reader.Read()){
    # do something with $reader
}

$connection.Close()
$reader.Close()
佞臣 2024-12-07 15:18:27

遗憾的是,我尝试了所有这些(包括马特的建议),然后尝试了一些,但我无法让它在以下参数下工作。
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!

望喜 2024-12-07 15:18:27

您可以尝试提交作业并传递凭据。

#Submit the job with creds
$job = Start-Job {importsystemmodules; Invoke-Sqlcmd -Query 'select @@version'  -ServerInstance LOCALHOST} -Credential $creds | Get-Job | Wait-Job

#Receive the job
$jobInfo = Receive-Job -Job $job

这对我有用。

You can try submitting a job and passing credentials.

#Submit the job with creds
$job = Start-Job {importsystemmodules; Invoke-Sqlcmd -Query 'select @@version'  -ServerInstance LOCALHOST} -Credential $creds | Get-Job | Wait-Job

#Receive the job
$jobInfo = Receive-Job -Job $job

This worked for me.

浪推晚风 2024-12-07 15:18:27

无需远程登录即可运行 SQL 查询。您可以使用以下函数并根据需要传递变量。无论哪个帐户具有访问权限,您都可以将其作为凭据传递。 (适用于 Windows 和 SQL 身份验证)

        $SQLInstance = "Instance Name"
        $Database = "Database"
        $ID = "User ID" 
        $Password = "Password"



     function Invoke-Sqlcommand 
        { 
            [CmdletBinding()] 
            param( 
            [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
            [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
            [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
            [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
            [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
            [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
            [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
            [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
            [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
            ) 

            if ($InputFile) 
            { 
                $filePath = $(resolve-path $InputFile).path 
                $Query =  [System.IO.File]::ReadAllText("$filePath") 
            } 

            $conn=new-object System.Data.SqlClient.SQLConnection 

            if ($Username) 
            { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
            else 
            { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 

            $conn.ConnectionString=$ConnectionString 

            #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
            if ($PSBoundParameters.Verbose) 
            { 
                $conn.FireInfoMessageEventOnUserErrors=$true 
                $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
                $conn.add_InfoMessage($handler) 
            } 

            $conn.Open() 
            $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
            $cmd.CommandTimeout=$QueryTimeout 
            $ds=New-Object system.Data.DataSet 
            $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
            [void]$da.fill($ds) 
            $conn.Close() 
            switch ($As) 
            { 
                'DataSet'   { Write-Output ($ds) } 
                'DataTable' { Write-Output ($ds.Tables) } 
                'DataRow'   { Write-Output ($ds.Tables[0]) } 
            } 

        } 

Invoke-Sqlcommand -ServerInstance $SQLInstance -Database $Database -Query "Query Goes here" -Username $ID -Password $Password

希望有帮助。

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)

        $SQLInstance = "Instance Name"
        $Database = "Database"
        $ID = "User ID" 
        $Password = "Password"



     function Invoke-Sqlcommand 
        { 
            [CmdletBinding()] 
            param( 
            [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
            [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
            [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
            [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
            [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
            [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
            [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
            [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
            [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
            ) 

            if ($InputFile) 
            { 
                $filePath = $(resolve-path $InputFile).path 
                $Query =  [System.IO.File]::ReadAllText("$filePath") 
            } 

            $conn=new-object System.Data.SqlClient.SQLConnection 

            if ($Username) 
            { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
            else 
            { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 

            $conn.ConnectionString=$ConnectionString 

            #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
            if ($PSBoundParameters.Verbose) 
            { 
                $conn.FireInfoMessageEventOnUserErrors=$true 
                $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
                $conn.add_InfoMessage($handler) 
            } 

            $conn.Open() 
            $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
            $cmd.CommandTimeout=$QueryTimeout 
            $ds=New-Object system.Data.DataSet 
            $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
            [void]$da.fill($ds) 
            $conn.Close() 
            switch ($As) 
            { 
                'DataSet'   { Write-Output ($ds) } 
                'DataTable' { Write-Output ($ds.Tables) } 
                'DataRow'   { Write-Output ($ds.Tables[0]) } 
            } 

        } 

Invoke-Sqlcommand -ServerInstance $SQLInstance -Database $Database -Query "Query Goes here" -Username $ID -Password $Password

Hope it HElps.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文