SQL Remote hook procedures do not get called after migrating to v16/v17

[VolkerBarth]

I have migrated several SQL Remote remote databases to 16.0.0.2158 and 17.0.0.1211. 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 16.0.0.2158
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 12.0.1.4301
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)

[regdomaratzki]

Can you describe how you migrated the databases?

dbupgrade, dbunload -a?, ...

Reg

[VolkerBarth]

Neither nor... The databases were unloaded "manually" to a reload.sql file which was then modified (to add some newer features not available in the old schema) and then that file was run against a freshly created database. For v16 and v17, I used dbinit without the new -pd option, in case that matters.

I have not migrated the user roles to the new RBAC model, so I'm relying on running DBREMOTE with the old REMOTE DBA privilege.

However, it seems that particular user has both the SYS_RUN_REPLICATION_ROLE and SYS_REPLICATION_ADMIN_ROLE roles, as far as I am able to understand the result of the sp_displayroles output:

call sp_displayroles('RemUser', 'expand_up') does list both roles.

(I even added the latter role explicitly but SQL Remote does still not call the hook.)

---

FWIW, I do not call the DBREMOTE utility but use the DBTools API but I guess that should not matter either.

[regdomaratzki]

I don't see the problem when I use dbunload -ar. Here's the v12 dbremote output :

I. 2015-09-18 10:46:36. SQL Remote Message Agent Version 12.0.1.4222
I. 2015-09-18 10:46:36.
I. 2015-09-18 10:46:36. 1: -c
I. 2015-09-18 10:46:36. 2: "uid=cons;pwd=sql;eng=cons;dbf=rem2.db"
I. 2015-09-18 10:46:36. 3: -qc
I. 2015-09-18 10:46:36. 4: -v
I. 2015-09-18 10:46:36. 5: -o
I. 2015-09-18 10:46:36. 6: rem2.txt
I. 2015-09-18 10:46:36. Found hook procedures:
I. 2015-09-18 10:46:36. sp_hook_dbremote_receive_end

I then run v16 dbunload -ar to rebuild the database :

dbunload -ar -c dbf=rem2.db;uid=dba;pwd=sql;eng=v16

And then run v16 dbremote against the newly upgraded db :

I. 2015-09-18 10:47:31. SQL Remote Message Agent Version 16.0.0.2172
I. 2015-09-18 10:47:31. 1: -c "uid=cons;pwd=sql;eng=cons;dbf=rem2.db"
I. 2015-09-18 10:47:31. 3: -v
I. 2015-09-18 10:47:31. 4: -o
I. 2015-09-18 10:47:31. 5: rem2_v16.txt
I. 2015-09-18 10:47:32. Found hook procedures:
I. 2015-09-18 10:47:32. sp_hook_dbremote_receive_end

And it finds the procedure (and executes it).

Are you using the same user in v12 and then new version when running dbremote?

Reg

[VolkerBarth]

FWIW, what does that statement from the v16 doc topic on SQL Remote hooks mean?

The stored procedures must have the SYS_REPLICATION_ADMIN_ROLE system role.

Do STPs have roles in the brave new RBAC world? (I thought users had roles...)

[VolkerBarth]

Are you using the same user in v12 and then new version when running dbremote?

Yes, I do.

Basically, the reload.sql file (because it is used for v12, as well) still does use the classic GRANT CONNECT, GRANT GROUP and GRANT REMOTE DBA statements for that user (and grant access to a few tables and views which that user has to access outside the DBREMOTE session). The script does not GRANT EXECUTE on the hook procedure but AFAIK, that's not required.

[regdomaratzki]

I'm not sure I like the idea of the same reload.sql being used for a v12 and v16/v17 database because of all the changes around permissions.

I'd suggest running the v16/v17 dbunload on the database and comparing how your reload.sql is defining permissions versus the way they are done in the reload.sql generated by dbunload.

[regdomaratzki]

It means you've found a poorly worded statement in the documentation. 🙂

It should say something along the lines of :

1) The user that executes this stored procedure must have the SYS_REPLICATION_ADMIN_ROLE system role.

Making sure this is true depends on whether the stored procedure is defined with the SQL SECURITY INVOKER or SQL SECURITY DEFINER (the default).

[VolkerBarth]

The hook is defined without a SQL SECURITY clause so it should be the default DEFINER (your 3rd option, methinks:)). The procedure is also listed as one of those which require particular privileges.

FWIW, when the remote dba user connects normally, she can not execute the hook procedure because of missing execute permissions. However, that should not matter for a DBREMOTE session.

[VolkerBarth]

FWIW, when I use a full DBA account to run DBREMOTE, it still does not call the hook procedure under v16/v17, just like the REMOTE DBA account. I'm puzzled.

And if I add another simple hook like that, it also does not get called - and it does not matter whether it's declared with SQL SECURITY DEFINER or INVOKER and whether it's owned by dbo or the remote dba user:

create procedure dbo.sp_hook_dbremote_begin() sql security invoker
begin
   message 'dbo.sp_hook_dbremote_begin() hook called' to log;
end;

If I add that simple hook procedure on v12, it surely gets called.

[Breck_Carter]

Here's a DAQ... there wouldn't be a problem with ownership, would there? I believe multiple hook procedures with identical names can coexist, and be called by different instances of dbmlsync started with different user ids matching the different hook owners. No, I have not done that myself but I'm helping a development process where I think it's happening (so far I've just closed my eyes and hoped it goes away 🙂

[VolkerBarth]

No, ownership does not seem to be the problem here, although I had considered that idea, too: I had created two hook procedures (one owned by dbo, one by the remote dba user named "RemUser") but neither one is called by DBREMOTE (not dbmlsync:)). (Of course it does matter when running the hook procedure interactively because then RemUser is not allowed to execute the dbo hook procedure without explicitly granted permission, as expected.)

AFAIK, the docs are rather quiet when it comes to hook procedures and ownership... But we've always used dbo ownership and never run into issues with that, both with SQL Remote and MobiLink.

BTW: What's a DAQ?

[Breck_Carter]

DAQ is dumb-a**-question, and you'll let me know if it was a DAQ or not 🙂

FWIW the symptom "does not execute a hook" sure feels like "does not see a hook" rather than "not authorized to execute a hook".

[VolkerBarth]

Hm, so now I have to consider whether my question on the meaning of DAQ was a DAQ itself? 🙂

FYI: Reg has got the according database, so I'm sure he will discover the cause of that symptom.

[regdomaratzki]

I'd buried a reply in the answer indicating that the source of the problem is that the stored procedure is owned by dbo. I'm currently checking with Those Who Know More Than Me About This Type Of Thing and will get back to you shortly. It might be next week though because The Person That Knows The Most is away today.

[VolkerBarth]

See my comment above (yes, there are too many here to not oversee some of them - I just noticed your reply from yesterday...):

I had created two hook procedures (one owned by dbo, one by the remote dba user named "RemUser") but neither one is called by DBREMOTE...

That made me think it's not about ownership...

[regdomaratzki]

I would not expect an sp_hook stored procedure owned by RemUser to be found or executed. The v17 docs state that :

Hook procedures can be created by any user with the MANAGE REPLICATION system privilege.

http://dcx.sap.com/index.html#sqla170/en/html/81afb4036ce210149cc3dbcb39a8a4d0.html

The v12 docs state that :

Hook procedures must be owned by a user with DBA authority.

http://dcx.sap.com/index.html#1201/en/mlclient/using-procs-customizing-ml-asa.html

It doesn't say that we look for procedures owned by users who at the time that dbremote is running have DBA authority (or the MANAGE REPLICATION system privilege).

I think you'll find that in v12, an sp_hook_dbremote stored procedure owned by RemUser will also not be found.

[VolkerBarth]

Just as a hint: The v17 link relates to MobiLink hook procedures, though those for SQL Remote are described similarily (except the poorly worded description, as we have discussed further up nested comment thread...:

http://dcx.sap.com/index.html#sqla170/en/html/95f074cf6ea11014bef4cc31fceaaf9a.html

So you basically say dbo must have the MANAGE REPLICATION privilege in v16/v17? (I have not yet checked whether this has been granted.)

---

EDIT: For v12, dbo has been granted DBA authority, just as the DBA user. So I would think that should mean when migrated to a compatibility role, dbo should be granted "SYS_AUTH_DBA_ROLE" which in turn has the mentioned "MANAGE REPLICATION" role and therefore should be able to specify hook procedures...

---

EDIT2: Now I have checked again: For v16, both dbo and DBA (the v12 users with DBA authority) are listed in v16 as users with the according privileges:

select * from (SELECT name from sysusers where name in ('dbo', 'dba')) SU cross apply sp_displayroles(SU.name, 'expand_down')
where role_name like '%replication%';

-- returns
name,role_name,parent_role_name,grant_type,role_level
DBA,MANAGE REPLICATION,SYS_AUTH_SA_ROLE,ADMIN,3
DBA,SYS_REPLICATION_ADMIN_ROLE,MANAGE ROLES,ADMIN ONLY,4
dbo,MANAGE REPLICATION,SYS_AUTH_SA_ROLE,ADMIN,3
dbo,SYS_REPLICATION_ADMIN_ROLE,MANAGE ROLES,ADMIN ONLY,4

So apparently (and as expected) the dbo user/role should have enough permissions to specify a hook procedure.

[regdomaratzki]

Correct, in v16+, you need to have the MANAGE REPLICATION privilege all the time, not just when dbremote/dbmlsync is running.

Stored procedures owned by dbo in all versions should be found when searching for hook procedures. That is the bug I'm fixing in v16+.

[regdomaratzki · Accepted solution]

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.

Reg

[Breck_Carter · Accepted solution]

dbo... one more reason to loathe ASE 🙂

[VolkerBarth · Accepted solution]

Well, not in this case, the original database schema was developped for MS SQL Server a long time ago, so "dbo" was handy. I guess now I should ask "Why should I not use dbo as owner for my schema?"

Or, as Reg has told me: "Everyone in Waterloo would be much happier if your user objects were NOT owned by a mutable system role (see the documentation for the SYSUSER table)." - another lesson learnt:)

[regdomaratzki · Accepted solution]

The change I made has introduced some unexpected failures in our test stream, and I've been forced to back it out for now so we can continue testing the v16 EBF for an escalated customer. I'll post back with new build numbers when I check the change back in.

[VolkerBarth · Accepted solution]

Thanks for the notification, and I guess that's a side-effect that would make you even more "much happier if [my] objects were not...". As stated, I can cope with that issue, as a workaround is available.

[VolkerBarth · Accepted solution]

Reg, are there plans to fix that error in a coming v16/v17 version?

[regdomaratzki · Accepted solution]

I know you will find this hard to believe (I wouldn't believe me), but I was actually running the Mobilink/dbmlsync test suite to verify a change to the database engine needed to implement this fix. I expect the change to be checked into the source code today after I look over the handful of failures from the test stream that ran overnight.

[regdomaratzki · Accepted solution]

I edited my answer to include the new build numbers.

It's v16.0.0 build 2245 and v17.0.0 build 1414 to save you a click if you're reading this in an email.

[VolkerBarth · Accepted solution]

Thanks, Reg, I'll respond here once those new builds are available:)

[VolkerBarth · Accepted solution]

For the record:

Now that v16.0.0.2270 and v17.0.4.2053 are available on Windows, the fix has been applied and the dbo-owned hooks do get called - thanks, Reg!

[Breck_Carter · Accepted solution]

Volker, you have the power to edit questions, answers, even comments, right?

So IMO if you wanted to insert a short Update: ... at the top of Reg's answer, that would be ok... save OPs from having to read further unless they want the details (which are still there).

In other words, be bold 🙂

[VolkerBarth · Accepted solution]

You're so right, sorry:)

[justin_willey · Accepted solution]

or has the Book of Common Prayer has it:

Brethren: Be sober, be vigilant, because your adversary the devil, as a roaring lion, walketh about, seeking whom he may devour: whom resist, steadfast in the faith. But thou, O Lord, have mercy upon us.

[VolkerBarth · Accepted solution]

1 Peter 5:8 - now, that's a "remote hook" of another kind:)

[VolkerBarth]

EDIT: I turned that comment into an answer (though it is none yet) in order to unnest the thread comments.

Reg had previously suggested:

I'd suggest running the v16/v17 dbunload on the database and comparing how your reload.sql is defining permissions versus the way they are done in the reload.sql generated by dbunload.

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.

[VolkerBarth]

Just to add:

SYSUSERPERM shows that the according user is the one and only with "remotedbaauth = Y". And everything else around SQL Remote v16/v17 works fine for that user account, i.e. though she has minimal granted permissions she is perfectly capable to apply all DML changes and all DDL changes via passthrough mode inside a DBREMOTE session.

The problem seems only be related to the ignoring of the hook procedures. - And by default that "RemUser" is member of group PUBLIC which in turn is member of dbo so a dbo-owned hook procedure should certainly be "visible" for that user.

I'm really out of my wits here.

[regdomaratzki]

We'll probably need the remote database to reproduce the problem here. I can't sort out what the issue is from this thread either.

[regdomaratzki]

Random Thought : If you take your v12 database and rebuild it using dbunload -ar, if you run dbremote with the exact same command you used in v12 on the newly rebuilt database, does it find the hook procedures?

[VolkerBarth]

rebuild it using dbunload -ar

To a v16/v17 database, right? - I'm gonna test that tomorrow.

[VolkerBarth]

Well, surprisingly I cannot make v16/v17 dbunload -ar work for a (strongly encrypted) v12 database whereas the same command runs fine with v12.

When using -ar, I would think I have to specify the current DBKEY in the -c connection string, and the same key will be used for the new database. So I supplied that DBKEY, and v12 certainly can use that to rebuild the database. But v16/v17 still ask me for a DBKEY - and when I supply that by additionally specifying -ep or ek, they report

"Unable to open database file <dbfilepath> - <dbfilepath> no database specified."

As stated, the same runs fine with v12.

So - as a workaround - I did a manual reload with v16 and used the generated reload.sql to build a v16 database - and it still does not call these hooks. And that's not a surprise in the end: The related parts of the v16 reload.sql file are still "old school", i.e. using "GRANT REMOTE DBA to ..." - exactly like I have used in the modified v12 script.

---

So what do you need me to do to supply the database? - FWIW, Chris has received a similar one a few weeks ago, cf. that FAQ "Unloading an v10 OEM database fails with v17".

[VolkerBarth]

FWIW, I just tried to use unload -ar against a strongly encrypted SA12 demo database, and that does work well with v12/v16/v17. So it seems there's something particular about my test case (or SR remote databases)?

[VolkerBarth]

@Reg: What do you need me to do to supply the database? - FWIW, Chris has received a similar one a few weeks ago, cf. that FAQ "Unloading an v10 OEM database fails with v17".

[regdomaratzki]

Sorry Volker, I seem to be getting some, but not all updates on this thread being emailed to me and missed the comments from the last few days.

Drop me an e-mail (it's just firstname.lastname@sap.com) and we can talk offline about sending in your database.

[regdomaratzki]

Argh. I completely ignored the fact that the stored procedure was owned by dbo. That's the source of the problem here.

I'll be back again once I know what to do about it.