SQL Server remote insert fails, update works

Question

I have created a stored procedure on ServerA (SQL Server 2019) which inserts a number of rows in a local table.

When I call the stored procedure using OPENQUERY from ServerB (SQL Server 2012), the procedure runs but the rows are not inserted.

Performing the same insert from ServerB with OPENQUERY returns an error 7357 (the actual error message is in French, saying that the OLE DB provider "SQLNCLI11" reports that the object has no columns or the user has no access to the object).

I'm using a linked server on ServerB with SQL authentication. RPC is disabled on ServerA, hence the use of OPENQUERY.

If I use the same statement to insert using OPENQUERY in an SSMS session directly on ServerA using the same credentials as the linked server, it works - I had granted INSERT to this user on the table in question before starting my tests.

I have performed updates on the same table from the linked server for many months without problems.

In fact, an INSERT on ServerB using a 4-part name for the table works.

Is there an extra permission I need to perform inserts remotely?

Answer 1

"RPC is disabled on ServerA".

RPC and RPC OUT are settings on the linked server definition, not on the target server.

So just enable RPC OUT on the linked server definition and exec the stored procedure directly or with

EXEC ('whatever') at ServerA