Super User Asked by Peter Sun on February 17, 2021
I am trying to run a SQL Server job nightly, but I am getting an error.
executed as user: NT SERVICESQLSERVERAGENT. Login failed for user 'SERVERINSTANCE$'. [SQLSTATE 28000](Error 18456)
The job is set up to run a stored procedure that references data from another server. Apparently, the login is failing, but I don’t understand, because both servers have the user, NT SERVERSQLSERVERAGENT, login setup on both servers. Why is the server trying to use the user ‘SERVERINSTANCE$‘ (the real error has the name server/instance of the stored procedure is on)? I thought SQL Server Jobs use the Job Agent login to run stuff on other servers. The version of SQL Server is SQL Server 2012.
What are some potential fixes for this?
"NT SERVERSQLSERVERAGENT" is a LOCAL account. Since you a connecting to a remote machine, the account is different. The SQL Server Agent, when run under a LOCAL account, will have no access to a remote database server.
The recommended way to fix this is to set up a service account in AD and set the service to run as that AD account. AD accounts are shared across servers, so if you set "DomainSQLAgent" (for example) to have permission on Server A and server B, it will work.
Any "NT SERVER*" accounts will not work for you as they are local to the one machine even if you create an identical one on a second machine.
Answered by Brian Gale on February 17, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP