Trying to setup log shipping between two SQL Server 2016 servers using the built-in wizard (right click database > Tasks > Ship Transaction Logs). Wizard succeeds, backup job runs fine, copy/restore jobs fail.
- Configured primary db to backup at a network location. gave SQL Server Agent service full control on that folder. SQL Server service is running under a domain account which also has full control on that folder.
- Added secondary server, configured copy job to copy to a local folder on that server. Gave service accounts full control on that local folder.
- Configured restore job with no recovery mode
Wizard completes with no issues and creates jobs on both servers. On the secondary server, when I expand the databases, the database appears in "Restoring" mode. This mode does not go away. When I check the error logs I see the message
Error: 18456, Severity: 14, State: 38.
Login failed for user 'EAGLE\my_username'. Reason: Failed to open the explicitly specified database 'My_Db'. [CLIENT: ]
my_username is the username I used to log into the primary database and run the wizard and this user is a sysadmin on both servers. Why and where is this account even coming in picture? Why not the SQL Job agent service account?
Anyway if I try to manually run the copy job on secondary server. It fails and gives no specific error message except that this step failed. Event viewer or DB error log have no entries for this fail.
How can I make this work?