3

I am attempting to run a powershell script utilizing Invoke-SqlCmd2. For various reasons, I need to execute the sql as a specific Windows account which I have the credentials for.

The initial process that executes is not running as the domain account, so I use the credentials I have to create a new Credential object, and then use that to create a New-PSSession :

$password = ConvertTo-SecureString $credentialPass -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($credentialUser, $password)

$sess = New-PSSession -credential $cred

Next, I Invoke-Command to start up a new powershell session as my supplied user.

Invoke-Command -Session $sess -ScriptBlock $sb

Within that (scriptblock, file, whatever) I then try to execute Invoke-SqlCmd2 as a Trusted Connection

$resultListTable = Invoke-Sqlcmd2 -query $query -ServerInstance $ServerInstance  -Database $db -As "DataTable" -Debug

The connectionString generated is : Data Source=ZZZZ;Initial Catalog=XXXX;Integrated Security=True;Connect Timeout=15 but the end result is the error:

Exception calling "Open" with "0" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

This result was surprising to me, as I had fully expected the New-PSSession to solve this problem for me. To that end, I've tried to debug things a bit more, and have added the following code to explain to me what user the script thinks it is running as.

$s = @"
  ** From .Net Environment        : [$([Environment]::UserDomainName)\$([Environment]::UserName)]
  ** From windows access token    : [$([Security.Principal.WindowsIdentity]::GetCurrent().Name)]
  ** From ps environment variables: [$($env:userdomain)\$($env:username)]
  ** From whoami                  : [$(whoami)]
  ** From WMI object              : [$(Get-WMIObject -class Win32_ComputerSystem | select username).username]
  ** From ps environment variables: [$($env:userdomain)\$($env:username)]
"@ 
Write-Host $s

Which produces the following output (just before telling me that 'NT AUTHORITY\ANONYMOUS LOGON' can't login)

** From .Net Environment : [DOMAIN\user]

** From windows access token : [DOMAIN\user]

** From ps environment variables: [DOMAIN\user]

** From whoami : [DOMAIN\user]

** From WMI object : [@{username=DOMAIN\brian}.username]

** From ps environment variables: [DOMAIN\user]

On the surface, there seems to be no possible way for the anonymous login to be utilized. The script is obviously running as my specified user (though the WMI object does still refer to me).

But, even more obviously, this isn't what is true. There must be another layer active here that I just can't see, some misunderstanding in how powershell actually works.

So, the core, underlying question... How do I get a powershell session set up so that when the Invoke-SqlCmd2 cmdlet fires, it is running as the DOMAIN\user ?

Notes

I don't think that remoting is involved here, as I'm always on the same machine. But maybe that doesn't matter...

Community
  • 1
  • 1
reidLinden
  • 4,020
  • 4
  • 31
  • 46

0 Answers0