I have migrated several SQL Remote remote databases to 220.127.116.118 and 18.104.22.1681. They run fine but I have noticed that they don't call hook procedures while older remotes do.
I have confirmed that the hook procedures are there (it's a sp_hook_dbremote_receive_end() hook in particular) and that they do the desired work when called explicitly.
But why are they ignored by DBREMOTE?
That's what a v16 remote log reveals (no hint to a hook procedure):
I. SQL Remote Message Agent Version 22.214.171.1248 I. I. Copyright © 2015 SAP SE or an SAP affiliate company. I. All rights reserved. I. Use of this software is governed by the Sybase License Agreement. I. Refer to http://www.sybase.com/softwarelicenses. I. I. Received message from "Zentrale" (20-02145857622-02145857622-0)
In contrast, a v12 remote log lists the defined hooks:
I. SQL Remote Message Agent Version 126.96.36.19901 I. I. Copyright © 2001-2015, iAnywhere Solutions, Inc. I. Portions copyright © 1988-2015, Sybase, Inc. All rights reserved. I. Use of this software is governed by the Sybase License Agreement. I. Refer to http://www.sybase.com/softwarelicenses. I. I. Found hook procedures: I. sp_hook_dbremote_receive_end I. Received message from "Zentrale" (3-02132130761-02138560117-0)
Stored procedure owned by dbo were not being recognized as valid hook procedures in v16 and up, even though they satisfied all the requirements.
This will be fixed in v16.0.0 build 2245 and v17.0.0 build 1414.
EDIT: I turned that comment into an answer (though it is none yet) in order to unnest the thread comments.
Reg had previously suggested:
Well, as stated, I'm not really familiar with that RBAC stuff (and for smaller companies like ours, it just feels like admin overkill) - but I had been quite sure the compatibility mode would work well enough for me (you know, that Golden Watcom rule).
Here are the few statements from the v12 script w.r.t. the user with REMOTE DBA authority (note, "4711_SB1" is the particular publisher of that remote, whereas the "RemUser" is a group and used to run DBREMOTE):
GRANT CONNECT,GROUP,REMOTE DBA TO "RemUser" AT 102 IDENTIFIED BY ENCRYPTED '...'; GRANT MEMBERSHIP IN GROUP "RemUser" TO "4711_SB1"; ... SET OPTION "RemUser"."wait_for_commit"='ON';
In constrast, that's what a v16/v17 unload will create:
GRANT CONNECT TO "RemUser" AT 102 IDENTIFIED BY ENCRYPTED '...'; CREATE ROLE FOR USER "RemUser"; GRANT ROLE "RemUser" TO "4711_SB1"; CREATE OR REPLACE ROLE FOR USER "RemUser" WITH ADMIN ONLY "SYS_MANAGE_ROLES_ROLE"; GRANT ROLE "SYS_RUN_REPLICATION_ROLE" TO "RemUser" WITH NO SYSTEM PRIVILEGE INHERITANCE; ... SET OPTION "RemUser"."wait_for_commit"='ON';
According to the v16 doc, the deprecated REMOTE DBA statement is replaced by granting the SYS_RUN_REPLICATION_ROLE role, and apparently that has been done here.