I have an SSIS package that inserts some data to Server B Which is in domain B. But my user is in Domain A and I am running the package in Server A which does not see domain B.
I have added a windows credential to server A and I can connect to Server B with windows authentication also I can run the job in the server using visual studio but when I run the package with a job It says my user does not have access to the server B.
my problem is a little different than SQL proxy I think because I tried it and it did not solve my issue. what I did was I created a credential on my user(in server A) and add it as a proxy to the job and I thought it will run it like I am running it with visual studio (while I am remote on the server) but still, I get the error.
I can't add credentials of server B because the domains are not trusted and It can't see the user.
Update:
my friend suggested "cross domain authenticate" which means I create a user with the same name as the Server B Username and also with the same password on Server A and if I run the job with this user it should be able to connect to both servers. It looks promising but did not work on the first try I will investigate it more and update here.
domain authenticate does work very well. all I had to do was add that user to SQL-server and then create a proxy. after that, I set the proxy in the step that calls the package and it works.