I have tried every option but i still cant find out why my sql agent is using a domain user when executing job steps with TSQL and keep getting the following error:
Executed as user: RESOURCE\UXCOLEBSREP$. Login failed for user 'RESOURCE\UXCOLEBSREP$'. [SQLSTATE 28000] (Error 18456). The step failed.
I have made sure that the agent is running using this acocunt:
NT SERVICE\SQLAgent$UXB
I have made sure that no run as being used under any job stpes.
Please advice! I'm using sql 2017
Thx
Best Answer
uxcolebsrep$ is probably the SQL Server name itself.
In this case, as you are using a linked server, that makes sense. Your actual Agent service account is a local account to your sql server. It does not exist on the remote Linked Server so SQL uses the Machine account to try to connect to the remote SQL Server (the one in the linked server).
To get this fix, you have a couple of option:
1 - Change your SQL Agent service acccount for a domain account and make sure it has permission on the remote SQL server (the one in the linked server) - (Recommanded option)
2 - Give access to the SQL Machine (uxcolebsrep$) on your remote SQL Server
3 - Create a proxy to use in your SQL job and make sure that this proxy will use an account with permission on the remote SQL Server.