Login failed for [SQLSTATE 28000] (Error 18456) the step failed SQL Server

Details of Error:

Executed as user: Domain\LoginName. Login failed for user 'Domain\LoginName'. [SQLSTATE 28000] (Error 18456). The step failed.

or below ODBC Data Source SQL Server Connection – Login Failed For User

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'UserName'.

Cause of Error:

This error is encountered when SQL Agent Service does not have permissions required to run the job. Or When we try ODBC Data Source SQL Server Connection Login Failed message when Login used for connection does not have permission needed.

Resolution:

First if this message is caused by SQL Agent Job connection then we will need to check the source SQL server agent service account name from Services MMC or Server manager or SQL Server Cofiguration Manager in Services-> Go to the SQL Server Agent Service –> properties –> Check the logon tab where we can find the account name.

Now check and add the login we found above or if the its caused by ODBC Data Source SQL Server Connection then add the Login used in ODBC to the SQL server instance by checking in Security tab –>logins with necessary permission on your target machine where its trying to connect or run the job on. This can be same instance or different instance on same or some other server.