I'm at a loss here ...
I can use an OPENDATASOURCE query with integrated security from my local instance with my SQL Service running either as Local System, my credentials, or my SQL Service Account ... all of them work fine.
Yet when I run my query from my control SQL Server, I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The server and agent are both running under my domain sql server user, which is a local admin on the machine.
The crazy part is that if I put my query into an agent job and run it, works perfect. The jobs are owned by sa, meaning it would be using the account running SQL ... which is the same account that is running SQL and the Agent on this server.
That and If I run SSMS as the service account in question and attempt to run my query, same result ... failed.