Invoke-SQLCmd with Different Credential

Question

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?

sure you've checked this but the user (mydomain\myusername) does have access to the database? — Matt, Aug 23 '11 at 08:15
You could grant a machine access to a remote SQL Server. This can't be setup via the GUI, but on the remote machine added myDomain\myMachine$ using CREATE LOGIN [myDomain\myMachine$] FROM WINDOWS; Then remove all teh user name, password stuff from your script. — Chad Miller, Aug 23 '11 at 11:45
@Matt yes of course as the account works when run from the Shell. But hey its the little things we overlook sometimes that come back to bite us. — Aaron Wurthmann, Aug 24 '11 at 22:43
@Chad that would have been cool. Except.. the system isn't in the same domain as the SQL server yet. It's going to happen just not today. I'm going to keep this attempt in my back pocket. — Aaron Wurthmann, Aug 24 '11 at 23:20
Are you sure your PowerShell instance has the correct remote permissions? Easy to overlook. It's bitten me in the past... — , Mar 16 '13 at 11:08

score 7 · Answer 1 · answered Aug 22 '11 at 22:46

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

I'll give that a try, it a variation of it sounds promising. Thank you. — Aaron Wurthmann, Aug 23 '11 at 00:46
It won't work right out of the box, but hopefully it gets you off to a start. — Aaron Bertrand, Aug 23 '11 at 00:54

score 0 · Answer 2 · edited Apr 14 '16 at 18:41

0

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.

edited Apr 14 '16 at 18:41

Matt

45,022
8
78
119

answered Apr 24 '14 at 15:31

Gabe

9
1

That worked on a system I already have access on. On different system it just produced an error `[localhost] An error occurred while starting the background process. Error reported: Logon failure: unknown user name or bad password.` – majkinetor Jun 09 '16 at 13:06
Ok. This worked for me, but I couldn't get it to use variables for the parameters inside the scriptblock. Updated with $using: and it worked. Like this: $query = 'select @@version' Start-Job {importsystemmodules; Invoke-Sqlcmd -Query $using:query -ServerInstance LOCALHOST} -Credential $creds | Get-Job | Wait-Job – adamt8 Jun 17 '20 at 03:52

score 0 · Answer 3 · answered Oct 30 '16 at 03:40

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.

This doesn't accomplish what the op is looking for. When supplying username and password in this code, it uses SQL authentication. The "integrated" auth just passes through who the user is signed in as when they run this code. However, this is a useful chunk of code if you don't want to install the SQL Powershell tools in your PS environment and add a dependency, so I'll be using it for a different project! :) — Matt, Mar 27 '18 at 19:30
@Matt . OP have mentioned "If it means creating a local account in the database that can answer my simple query that's fine too." in his question, which means he is fine with creating sql userID. Hence this answer — Venkatakrishnan, Dec 07 '18 at 10:41

score 0 · Answer 4 · edited May 23 '17 at 12:33

0

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

edited May 23 '17 at 12:33

Community

1
1

answered Aug 22 '11 at 22:20

Cade Roux

88,164
40
182
265

I'm sorry I neglected to mention that the script runs as a service so there is no GUI to use. Actually it is "more" correct to say that a service runs the script (Nagios client, NRPE/NSClient), I suppose I could use sanur with runas though. – Aaron Wurthmann Aug 23 '11 at 00:49
@Aaron Wurthmann I did end up building a custom RUNAS which allows CreateProcessWithLogonW with LOGON_NETCREDENTIALS_ONLY without prompting for the credentials. But we never used it in production because of the security considerations of storing the password. – Cade Roux Aug 23 '11 at 02:42
yeah I hear that. In this case the account I'm using for monitoring as very little permissions and the server running the script is on the internal network and domain verses the production network and domain. Maybe I'll come back around to this someday and figure it out from a .NET perspective. – Aaron Wurthmann Aug 25 '11 at 15:55

score 0 · Answer 5 · answered Aug 23 '11 at 08:25

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

score 0 · Accepted Answer · answered Aug 24 '11 at 23:58

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!

Invoke-SQLCmd with Different Credential

6 Answers6

Linked