0

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

Improve this question
2
  • Is your job step a "Transact-SQL Script" ? If yes, what do you have in the "Run as" drop down ? And could you give us an idea of the query you run in that job step ? Your domain is "Ressource" ? and what is UXCOLEBSREP$ ? is it your SQL Server ? – Dominique Boucher Jan 28 '20 at 15:18
  • Hi @DominiqueBoucher and thanks for getting back, our domain is resource but im really not sure what the resource\uxcolebsrep$ is. Im really confused, It is not a domain user for sure because it has the $ sign and i checked in the AD and it doesnt exist. The Job steps are all TSQL, nothing under the Run as because i dont have any proxies, The step is calling a stored procedure which retrieves some data from a linked server. Do you think its a security issue? but im still not sure why the agent is using such a weird user – Kaawach Said Jan 28 '20 at 15:24
3

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.

Improve this answer
5
  • Will get back to you Shortley – Kaawach Said Jan 28 '20 at 15:33
  • Hi @Dominique Boucher, How do you achieve point no. 2? Im sure im facing a security issue. Thx – Kaawach Said Jan 28 '20 at 16:48
  • What's the SQL instance in your Linked Server ? You need to connect to this instance and create a login named "RESOURCE\UXCOLEBSREP$". You then need to assign the wanted permission (probably db_datareader) to the database that your job is hitting through the Linked Server. – Dominique Boucher Jan 28 '20 at 19:10
  • Thank you @Dominique, It was purely Linked server security issue. The resource\uxcolebsrep$ represents my domain and the servername and it is the user used by the Agent, im not sure but this seems how the agent appears on SQL 2017. Im sorry i dont have enough reputation to vote for your answer – Kaawach Said Jan 29 '20 at 17:17
  • 1
    No worries, I'm happy I could helped you with your issue. – Dominique Boucher Jan 30 '20 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.