Invoke-SQLCmd with Different Credential

Background: I am running a script locally that ha开发者_C百科s 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?

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

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

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

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.

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

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.