Job failing because of a strange non-exisiting login

Salam ELIAS 112 Reputation points
2021-11-05T11:55:18.16+00:00

Hi, I have a job that runs several T-SQL, one of them access 2 Linked sql servers, which fails with the following error. The login used is my domain user who has all privileges.
Inside SSMS it executes successfully. I am not able to understand where this The login MYDOMAIN\SQL2K12$ used by NT SERVICE\SQLSERVERAGENT. comes from.

Executed as user: NT SERVICE\SQLSERVERAGENT.
Login failed for user 'MYDOMAIN\SQL2K12$'. [SQLSTATE 28000] (Error 18456)
Starting proc 'Retrieve_LinkedServer_ErrorLog' on server 'SQL2K12' [SQLSTATE 01000] (Error 0)
Starting proc 'Retrieve_LinkedServer_ErrorLog' on server 'SQL2K12\SC2K12' [SQLSTATE 01000] (Error 0). The step failed.

8 months ago, I implemented Log monitoring system, opened a thread in this forum because I was receiving some alerts about this loging at
https://learn.microsoft.com/en-us/answers/questions/315226/strange-login-and-appdomain-xx-created.html#answer-344406
but no solution was found, no right replies. So the BIG QUESTION Why NT SERVICE\SQLSERVERAGENT is using this non-exisiting login?
From sql doc, I understand that a job that uses T-SQL can't be assigned a "Execute AS" in the properties.
Thanks for your help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,671 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-11-05T11:59:32.163+00:00
    0 comments
  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-11-05T22:47:07.533+00:00

    That is not a non-existing login. That's the account for the machine where SQL Server is running.

    You did not say how the linked server was setup, but I assume that it uses self-mapping. If user A logs in the local server and accesses the remote server, the access will be as user A. And fail if user A does not have access to the linker server.

    From a security perspective, this is the best way to set up a linked server. You can also set up a linked server so that a specific login maps to an SQL login on the remote server. This can also be set up to apply to everyone. This is not good security.

    Since this job runs under Agent, access will be by the service account for SQL Server Agent. However, presumably Agent runs under a machine account, for instance NT Service\SQLAgent$SQL12K. This account is not available on the other server, and therefore the access is by the machine account.

    One solution is to grant access to the machine account on the remote server, but it is not a good solution.

    A better solution is to set up a proxy and then run the job as a CmdExec job which runs SQLCMD to run SQL task. I describe how to do this here: https://www.sommarskog.se/perm-hijack.html#agentjobs

  3. Seeya Xi-MSFT 16,436 Reputation points
    2021-11-08T07:51:16.703+00:00

    Hi @Salam Elias ,

    Welcome to Microsoft Q&A!
    Please see this link which is a similar thread:
    https://social.technet.microsoft.com/Forums/en-US/5252ba7c-0626-456b-8a81-472c5d51c6f8/linked-server-setup-and-sql-agent-job-execution?forum=sqlsecurity

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  4. Erland Sommarskog 100.9K Reputation points MVP
    2021-11-10T22:11:30.45+00:00

    You say "That is not a non-existing login...", then why SQL is not finding it as you you can notice in the following alerts I am receiving?

    Sorry, for some reason i read your post as you were thinking that this login does not exist in Windows. Which it does. Except that Windows does not really use the term "login", so that was bad reading on my part.

    It was running under NT SERVICE\SQLAgent$SQL2K12 and not SQL2K12$.

    But that account is a local account, so when connecting to another machine, connection will be by the machine account.

    Also, I visited your thread, in step 4, you indicate "On the first page, set sa or some other non-person who is sysadmin as the job owner...." but this does not exist in the interface

    But the screenshot is for setting up a proxy. The screenshot below shows which box I'm talking about:

    148337-image.png

    However, that box will not work with access to linked server with self-mapping. (Which is what you have.)

    on local and the 2 remote servers, when I execute as CMDexec the job I get

    Message
    Executed as user: salam\ApplJobRunner. Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'SALAM\ApplJobRunner'.. Process Exit Code 1. The step failed.

    Obviously, you need to create a login for that Windows user on the other server.

    I have just noticed that you say in your article "and adds it to the AD group(s) needed for the application in question.....", which AD groups, I have no specific app for this it is just a sql agent job trying to access a table on a remote server

    Well, "the AD group(s) needed for the application in question". That is, I'm assuming that you already have an AD group for users accessing the application. Although what I have in mind here is the local database. The key point is that the login, and thus the job, should run with elevated permission. When it comes to the linked server, I would suggest that the same thing applies, although exactly how you do it, depends on your local situation.

    0 comments