Help or work around with Remote Invoke-SQLCMD Authentication (double hop)

PowerShell Help
1

I’m trying to run a remote script that pulls a dataset from a SQL database, however am getting stuck with the script trying to connect to SQL via ‘NT Authority\Anonymous’ and I’ve tried the following:

  1. Running Invoke-Command {invok-sqlcmd} -Credential $credentials - no good still uses NT Authority
  2. Passing a PSCredential Object to the remote script and then using the -Credential flag on the Invoke-SqlCmD
    1. This one is odd it doesn't work, but it can't find the server / instance even though I can ping the server, and if I run the script locally from the remote box it connects no problem with a sqlconnectionstring integrated security = true
  3. Removing integrated security = true, passing a PSCredential and then breaking into a username / password combo and using a connection string with username id='username'; password='password'. I am 100% certain the correct username / password combo is being used (I wrote the output of the remote script after passing just to check), but the login still fails.
    1. It's my AD creds, and they work when I'm logged into windows and running with integrated login (so this might be a sqlserver limitation of accepting username / password)
My last idea is starting a powershell process as my user remotely, but how do i pass it the file and arguments i want it to run?
2

As for …

...however am getting stuck with the script trying to connect to SQL via 'NT Authority\Anonymous' and I've tried the following:

… you cannot use this account in remote session, period. It is a local machine service account only.
In order to remote connect, you must use and domain account.

This is not a SQL thing, but networking 101. Only domain accounts can traverse the network, and that account must have permissions to access the remote target.

3

Sorry this wasn’t clear, i’m stuck BECAUSE the script tries to connect to SQL with ‘NT Authority \ Anonymous’ even though it’s called with my domain account.

So script 1 (i run from my machine) –> calls script 2 (on remote machine)–>which tries to connect to sql with integrated security=true, but instead of my domain credentials is using NT Authority

4

Unless I miss something the answer is actually in your subject line.
By default kerberos don’t allow double hops.

Good blog post on the subject.

https://blogs.technet.microsoft.com/ashleymcglone/2016/08/30/powershell-remoting-kerberos-double-hop-solved-securely/

5

Hi Frederick,

So by the looks of it, I’ll need to pull the dataset on my machine and pass it as a param?

6

Kind of difficult to say without seeing what you’re trying to accomplish on the first server.
The problem you’re facing is that you can’t get the first server to impersonate your account without additional configuration.

So if you can pull the data from the SQL server directly from your machine, then transfer that data to the other machine then yes that should work.
Since you’re connecting to both machines directly from your machine.

But depending on the size etc. of the dataset that may not be feasible/great either.

7

Hi Frederick,

the DS should be relatively small, at most 128 rows (only pulling 3 columns). I’m now trying to pass the DS as a parameter but it’s not being received like the other parameters. Is there a special call I have to declare on the receiving function?

Here’s what i’m trying to do (already confirmed $ds has a value prior to this call):

Invoke-Command -Computername $server -ScriptBlock {. C:\script\test.ps1 -p1 "$Using:v1" -p2 "$Using:v2" -p3"$Using:v3" -p4 "$Using:v4" -ds $Using:ds} -ArgumentList $v1, $v2, $v3, $v4, $ds

 

and the receiving function:

param(
$p1,
$p2,
$p3,
$p4,
$ds)

Function Test
{

do work with ds

}

When I run it, i get a “cannot index into a null array” , which i Know means there’s no data in the $ds variable.

8

It appears the receiving $ds is a [System.Collection.Arraylist] type, though i’m sending it the output of an Invoke-SQLCMD which i believe is a System.Data.Datarow variable type

9

To avoid any guessing, check the type of the variable is, before sending it to the “receiving” function.
E.g.
[pre]
$ds.GetType()
[/pre]

So lets say that it’s of the type “System.Data.Datarow” and you need to keep that type on the receiving function then make sure that you add the type to the variable.
E.g.
[pre]
param(
$p1,
$p2,
$p3,
$p4,
[System.Data.Datarow]$ds)

Function Test
{

do work with ds

}
[/pre]

10

Hi Frederick,

A working solution that I’m using is I read the dataset on Script 1, then I read the data and add it to an arraylist, send the arraylist as a parameter to script B, which powershell thinks is one long string. I then split the string on “” and have an array of all the values I need. It’s clunky but works and the performance hit isn’t noticeable (not a large dataset)

11

Yeah that seems pretty clunky and shouldn’t be necessary.
But if it works :slight_smile: