question

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 asked ErlandSommarskog answered

Job failing because of a strange non-exisiting login

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://docs.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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SalamElias-0832 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered SalamElias-0832 commented

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

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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? My thread 7 months ago was I needed to know who is using this login but nobody was able to tell me how to discover who was trying to use it. If it exists, how can I chek its properties
148153-sqlfailedlogin.jpg


0 Votes 0 ·
sqlfailedlogin.jpg (36.6 KiB)

It was running under NT SERVICE\SQLAgent$SQL2K12 and not SQL2K12$. I was obliged as a fix to run the agent as mydomainuser who has sysadmin on local and remote servers and it works fine but I don't want to keep it like this

0 Votes 0 ·

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
148182-sqlfailedlogin-proxy.jpg


0 Votes 0 ·

Erland, I created domain user then executed your suggestion

 CREATE CREDENTIAL ApplJobRunner WITH IDENTITY = 'DOMAIN\ApplLogin', SECRET='password'

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.



0 Votes 0 ·

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

0 Votes 0 ·

You asked "how the linked server was setup", for the time being it is

"Be made using the login's current security context".

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @SalamElias-0832,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.


image.png (10.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.