We have implemented a new application "A" that connects to a Database in instance "A" on server "A". The OS of database server "A" is Windows 2008 R2 using SQL 2008 R2.
We have another application "B" that connects to a Database in instance "B" on server "B". The OS of database server "B" is Windows 2003 R2 using SQL 2005.
Some data needs to be transferred from Database "B" to Database "A" in a weekly basis and for this we have setup a store procedure on Database "B" which calls a job which maps and allocate the data on a specific table on database "A".
When I try to run this store procedure from my computer (connecting to the Instance and Database B using SQL Management Studio) the following error message appears: Msg 18456, Level 14, State 1, Line 1, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
Now if I RDP directly into the instance B server (with the same user as before) and run the store procedure from there, it runs successfully.
In order to run the store procedure I do not want to have to log into the server itself every time, as a matter of fact I am thinking about delegating this task.
It looks like the SQL permissions have been set correctly on both databases, but most probably there is something that I am missing in order to make it work from a third application or computer.
Any help will be greatly appreciated.