Login failed for [SQLSTATE 28000] (Error 18456) the step failed
When you query the data from another SQL server (Linked server) and SQL server & agent service is run as a domain user. You may get the error following error message in your job history.
Error:
Executed as user: Domain\XXX.Service. Login failed for user ‘ SSBF\SQL.Service ‘. [SQLSTATE 28000] (Error 18456). The step failed.
Solution:
Step 1: Check the source SQL server agent logon service account name
All programs –> SQL server 2008 –> Configuration Tools –> SQL server configuration manager. Go to the SQL server agent –> properties –> Check the logon tab and find the account name.
Step 2: Check the same is available in the target SQL server.
Under the security tab –>logins. If it’s not available add the domain user with necessary permission on your target machine.
I hope this will help someone.
3 Comments
nasha
hello
tank u for useful post
i have another problem .i cant connect with sql user when i write it in instaletiob time. i chose mix mode .and i recive this error
login failed for user msn\sqldb . microsoft sql server ,error 18456.state1
in my sql server configuration in logon tab i see sqldb user . but in sql -> database-> security -> user
i dont see sqlbd
what can i do??
please help me
Joco
Sir we have the same problem and i followed your instruction and it works but only for single domain user. how can I add a multiple domain user on SQL Server agent?
Spark Limo
I found the post to be exceptionally informative, and I greatly appreciate the shared insights.