Search code examples
sql-serverpowershellimpersonation

Impersonating sqlcmd.exe from Local service : Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

I'm running an impersonated sqlcmd.exe from a local service running under local system account.

I wrote a small utility to run process as user in order to correctly perform impersonation from this service. So impersonation is performed correctly from this service, when I run whoami.exe /all with my target impersonation account, it reports the correct account information.

Nevertheless, when I run sqlcmd.exe with the same target impersonation account, It fails with the following output:

Running "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe -b -E -S mydbserver.mydomain.net -Q "my sql command"" (working directory: "C:\")
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..

I known the sqlcmd as been correctly impersonated, but I do not understand why the connection is performed with this account instead of impersonated account.

The machines setup is as follow:

  • mydbserver.mydomain.net : running a SQL Server 12.0.2000 instance
  • mybuildserver.mydomain.net : running a powershell script under a local system service account where it impersonate sqlcmd.exe (Microsoft SQL Server Native Client 11.0)

The impersonation is performed using the win API CreateProcessAsUser function.

Solution

  • The issue is that you have a token for the user which is valid on the local machine only. Although you can impersonate the user on that machine you cannot delegate the impersonation to another machine.

    In order to delegate the impersonation to another machine you must either:

    • Have an impersonation token for a login which was created as NetworkCleartext or Interactive
    • Have access to the user's password
    • Have an impersonation token for a Kerberos (Active Directory) login AND your process must be "trusted for impersonation".

    This is for security reasons: In order to impersonate a user on the local machine you only need to be an administrator on the local machine. If you could delegate impersonation to another machine without knowing the password, this would allow any local administrator on any machine to impersonate any user on the network on any machine. They could then grant themselves domain admin permissions and rule the network. Clearly this cannot be allowed.