I have two SQL 2016 servers - A and B. On Server A I have a SQL agent job with a step that executes an SSIS package that resides on server B. The package on server B adds a row to a table on server B and tries to write a row to a table on server A.
When the agent job on server A is run, the package succeeds in writing the row on server B (where the SSIS package is stored and is executing) but errors out when writing the row on server A (where the agent job is being run from).
Here's the error message I'm getting from the package:
SSISTest:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
So the problem is when the package is trying to write the row to server A from server B.
How do I change the SSIS package on server B and provide credentials to write to server A? Or, have the agent job and package execute under the same domain login?
I've tried adding the SQL service accounts for both servers as sysadmins on each server. I've also created a credential and proxy account on server A using my domain login which is a sysadmin on both servers.