start-psjob - user credentials

[Robin Moffatt]

Hi,
Further to my previous question about psjob, I have another.

I have a simple script that connects to a SQL server using integrated
security. If I run the script from the console, it works. If I run it
from start-psjob it fails, even if I pass -credential of my current
user (and that has access to the SQL server in question)

Running Profiler against the SQL server shows the interactive
execution of the script connecting to the server with the correct
domain ID of my current user, but when the job's run as start-psjob
(with or without -credential) it connects to the SQL server as NT
AUTHORITY\ANONYMOUS LOGON

1) To help with debugging, how can I find out what the authenticated
user of the current powershell context is?
2) How can I connect to a SQL server from a Start-PSJob-initiated
script if it won't run under my credential?

My scripts and output are below

Thanks,
moff.

connecttest.ps1:
--------------
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =
"Server=sgb187;Database=buy;Integrated Security=SSPI"
$SqlConnection.open()
$sqlConnection.state
-------------

PSH C:\PS > .\connecttest.ps1
Open
PSH C:\PS > Start-PSJob "C:\ps\connecttest.ps1"

SessionId Name State HasMoreData
Command
--------- ---- ----- -----------
-------
43 Running True C:\ps
\connecttest.ps1

PSH C:\PS > get-psjob|receive-psjob
Receive-PSJob : Exception calling "Open" with "0" argument(s): "Login
failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
At line:1 char:24
+ get-psjob|receive-psjob <<<<
PSH C:\PS > Start-PSJob "C:\ps\connecttest.ps1" -credential (get-
credential)

cmdlet get-credential at command pipeline position 1
Supply values for the following parameters:
Credential

SessionId Name State HasMoreData
Command
--------- ---- ----- -----------
-------
45 Running True C:\ps
\connecttest.ps1

PSH C:\PS > get-psjob|receive-psjob
Receive-PSJob : Exception calling "Open" with "0" argument(s): "Login
failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
At line:1 char:24
+ get-psjob|receive-psjob <<<<

[Robin Moffatt]

Got around it by using
$server = New-Object 'Microsoft.sqlserver.management.smo.server'
$server_path
$server.ConnectionContext.LoginSecure = $false
$server.ConnectionContext.login = $db_user
$server.ConnectionContext.password = $db_pw

I'd still like to understand how authentication under psjobs works
though, if anyone can explain it.

[Marco Shaw [MVP]]

I think the important concept here is that you've gone and created a new
object. The moment you create a object, you need to redefine your
credentials again.

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

[Robin Moffatt]

On Jan 25, 3:24 am, "Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com>
wrote:

> Robin Moffatt wrote:
> > Got around it by using
> > $server = New-Object 'Microsoft.sqlserver.management.smo.server'
> > $server_path
> > $server.ConnectionContext.LoginSecure = $false
> > $server.ConnectionContext.login = $db_user
> > $server.ConnectionContext.password = $db_pw
>
> > I'd still like to understand how authentication under psjobs works
> > though, if anyone can explain it.
>
> I think the important concept here is that you've gone and created a new
> object. The moment you create a object, you need to redefine your
> credentials again.
>
> Marco

Hi Marco,
Thanks for your reply. I'm afraid I don't understand though. If I run
the script from the console, or the commands interactively, I don't
have to authenticate individual objects.
Are you saying that I can't run backgound jobs and use trusted
authentication? That sounds like quite a limitation.
Is there any way I can see what the current credentials of the user
are? Or pick them up from when a script is started as a background
job?

Thanks, Robin.

[Robin Moffatt]

Does anyone have any comments on this, or suggestions where I could be
asking the questions?

- Is it not possible to run backgound jobs and use trusted
authentication?
- Is there any way I can see what the current credentials of the user

are? Or pick them up from when a script is started as a background
job?

thanks.

[Shay Levi]

Robin,

What exactly are you trying to do? Can you define "trusted authentication"?

A sample code would be the best to describe your problem.

I'm not sure what do you mean by "current user credentials", maybe this:

PS > [Security.Principal.WindowsIdentity]::GetCurrent() | gm

TypeName: System.Security.Principal.WindowsIdentity

Name MemberType Definition
---- ---------- ----------
Dispose Method System.Void Dispose()
Equals Method System.Boolean Equals(Object obj)
GetHashCode Method System.Int32 GetHashCode()
GetType Method System.Type GetType()
Impersonate Method System.Security.Principal.WindowsImpersonationContext
Impersonate()
ToString Method System.String ToString()
AuthenticationType Property System.String AuthenticationType {get;}
Groups Property System.Security.Principal.IdentityReferenceCollection
Groups {get;}
ImpersonationLevel Property System.Security.Principal.TokenImpersonationLevel
ImpersonationLevel {get;}
IsAnonymous Property System.Boolean IsAnonymous {get;}
IsAuthenticated Property System.Boolean IsAuthenticated {get;}
IsGuest Property System.Boolean IsGuest {get;}
IsSystem Property System.Boolean IsSystem {get;}
Name Property System.String Name {get;}
Owner Property System.Security.Principal.SecurityIdentifier
Owner {get;}
Token Property System.IntPtr Token {get;}
User Property System.Security.Principal.SecurityIdentifier
User {get;}

-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

[Marco Shaw [MVP]]

Apparently Windows authentication should be the default, but you can
maybe try to force that by adding:
$server=new-object...
$server.ConnectionContext.LoginSecure = $true

Give that a try in your script. If that doesn't work, I'll test and try
to get to the bottom of this.

[Robin Moffatt]

Marco, Shay - thanks for your replies.

Shay, what I'm trying to do, along with code examples and output, is
detailed in my first posting on 24th Jan.
[Security.Principal.WindowsIdentity]::GetCurrent() is interesting,
thanks, I didn't know about it.

It seems the difference between my console and a psjob is the
AuthenticationType changes from Kerbos to NTLM respectively, and I
have one less group.
I can't figure out what that group is though, because I don't know how
to translate the GUIDs that WindowsIdentity returns.
This is the code I'm running, from the console and as a psjob

$id = [Security.Principal.WindowsIdentity]::GetCurrent()
$id
$id.groups.count
Add-PSSnapin Quest.ActiveRoles.ADManagement
get-qaduser -identity $id.name
(Get-QADUser -identity
([Security.Principal.WindowsIdentity]::GetCurrent().name)).memberof

from the console it works but as a psjob I get the error Access is
denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) when it
tries to use the QAD cmdlet

Marco, I tried setting $server.ConnectionContext.LoginSecure = $true
but it didn't help.
Interestingly, it *does* work (leaving it at default, i.e. $true) for
a local instance of SQL Server, just not any remote instances.

Thanks.

[Shay Levi]

Sorry, for some reason I can only see one thread of this post, all chained
threads are lost :)

-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

> Marco, Shay - thanks for your replies.

[Robin Moffatt]

On Jan 29, 11:28 am, Shay Levi <n...@addre.ss> wrote:
> Sorry, for some reason I can only see one thread of this post, all chained
> threads are lost :)
>

here it is :)

[Marco Shaw [MVP]]

Robin Moffatt wrote:
> Hi,
> Further to my previous question about psjob, I have another.
>
> I have a simple script that connects to a SQL server using integrated
> security. If I run the script from the console, it works. If I run it
> from start-psjob it fails, even if I pass -credential of my current
> user (and that has access to the SQL server in question)
>
> Running Profiler against the SQL server shows the interactive
> execution of the script connecting to the server with the correct
> domain ID of my current user, but when the job's run as start-psjob
> (with or without -credential) it connects to the SQL server as NT
> AUTHORITY\ANONYMOUS LOGON

I was looking around for something related to credentials in general,
and came across this:
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

It seemed relevant to what was going on here. There is a "common issues
and workaround" section on the above page regarding anonymous login
failure messages.

[Oisin (x0n) Grehan [MVP]]

On Apr 2, 10:23 am, "Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com>
wrote:

> Robin Moffatt wrote:
> > Hi,
> > Further to my previous question about psjob, I have another.
>
> > I have a simple script that connects to a SQL server using integrated
> > security. If I run the script from the console, it works. If I run it
> > from start-psjob it fails, even if I pass -credential of my current
> > user (and that has access to the SQL server in question)
>
> > Running Profiler against the SQL server shows the interactive
> > execution of the script connecting to the server with the correct
> > domain ID of my current user, but when the job's run as start-psjob
> > (with or without -credential) it connects to the SQL server as NT
> > AUTHORITY\ANONYMOUS LOGON
>
> I was looking around for something related to credentials in general,

> and came across this:http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-...

>
> It seemed relevant to what was going on here.  There is a "common issues
> and workaround" section on the above page regarding anonymous login
> failure messages.
>
> Marco
>
> --
> Microsoft MVP - Windows PowerShellhttp://www.microsoft.com/mvp
>
> PowerGadgets MVPhttp://www.powergadgets.com/mvp
>
> Blog:http://marcoshaw.blogspot.com

What's actually happening here is that when you start the job via
Start-PSJob, you are submitting the work via WinRM. If you remember,
for the first powershell CTP even local jobs go via WinRM and as such
through a web service. The web service listener is what ends up
running your code. Even though you can run the job locally (as a
trusted connection uses the interactive user's credentials, e.g.
yours), this will not work via the webservice as your credentials
cannot be delegated beyond the WinRM we bservice because of what's
known as the "double-hop" issue. The double-hop refers to the problem
whereby your credentials are being passed more than one hop away from
the origin:

* start-psjob - does not work

localhost (posh) -> localhost (winrm) -> sqlserver

* local script - works

localhost (posh) -> sqlserver

The only way you can get this to work is if you enable kerberos
authentication instead of ntlm. Normally, both of these options are
enabled in IIS, but kerberos is first by default, and only works when
your machine is connected to a domain. Additionally, SPNs (service
principal names) need to be created for each unique server / port
combination of your machine in order for your machine to be
authenticated by the remote server. Unfortunately this is beyond the
scope of a usenet post, but perhaps if people are interested I could
put together a blog post on how to do this. Again, this will not work
on your home machine no matter how hard you try if it is not connected
to a domain. On top of that, I'm not even sure if kerberos
authentication will work on the loopback interface (localhost) - in
fact, thinking about it now, it probably won't. In future builds of
the CTP, start-psjob will not submit local jobs via the WinRM
webservice for this very reason AFAIK.

* Some more information here under the "double hop" section:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329986

Hope this helps,

- Oisin

PowerShell MVP
http://www.nivot.org/

[Marco Shaw [MVP]]

> What's actually happening here is that when you start the job via
> Start-PSJob, you are submitting the work via WinRM. If you remember,
> for the first powershell CTP even local jobs go via WinRM and as such
> through a web service. The web service listener is what ends up
> running your code. Even though you can run the job locally (as a
> trusted connection uses the interactive user's credentials, e.g.
> yours), this will not work via the webservice as your credentials
> cannot be delegated beyond the WinRM we bservice because of what's
> known as the "double-hop" issue. The double-hop refers to the problem
> whereby your credentials are being passed more than one hop away from
> the origin:

Thanks.

The reason I got confused, and wasn't sure what was going on was that I
could not reproduce the problem.

I'll test it some more and reread your post while paying more attention.

Marco