It is indeed as you say.

When running the same SELECT linked-server query while RDP'd directly on the source server (the server instance to which the linked server is added) it works! The error occurs when running the query while connected remotely to the server (through SSMS).

So it looks like it is indeed a double-hop integrated authentication issue.

We are running this query from a SQL job so we just need to give the SQL Agent account sufficient privileges on the target instance for it to be able to view the data.