Scenario
Its pretty involved to explain but I have a procedure that updates data in two databases on instance1 and as part of the process (its a cursor for relevant reasons) it needs to make updates to a database on instance2 where certain criteria are met. If the line of code that references instance2 is altered to be the local instance the procedure runs in less than a minute. If it is set to refer to the correct location on instance 2 the procedure takes 30-40 seconds per record (we have never let it complete).
History (this morning)
Reviewing the estimated execution plan, the code that makes the single record update on instance 2 is using a remote scan on a table that is 100k records deep.
I changed this from
Update C set col1 = @val1, col2 = @val2 where col2 = @ID
to
Execute ('Update C set col1 = @val1, col2 = @val2 where col2 = @ID') as user1 at Instance2
where Instance2 is a linked server and user1 is a sql login that has impersonate enable on the linked server. This was so that the update process can make use of the clustered index on col2 and therefore avoid the table scan.
Issue
We are now getting security/authentication errors and the script is failing with
Msg 15274, Level 16, State 1, Procedure "procname", Line 263
Access to the remote server is denied because the current security context is not trusted.
Can anyone advise me what I need to configure to allow this update to execute please? Or, is there a better way that I can get the update to use the index on the table on Instance2? From my knowledge table hints are not allowed on remote queries...?
Many thanks
Jonathan