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)

asked 18 Sep '15, 10:22

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 18 Sep '15, 10:46

Can you describe how you migrated the databases?

dbupgrade, dbunload -a?, ...

Reg

(18 Sep '15, 10:25) Reg Domaratzki
Replies hidden
1

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.

(18 Sep '15, 10:44) Volker Barth

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

(18 Sep '15, 10:54) Reg Domaratzki

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...)

(18 Sep '15, 10:54) Volker Barth
Replies hidden

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.

(18 Sep '15, 11:04) Volker Barth

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.

(18 Sep '15, 11:38) Reg Domaratzki
Comment Text Removed

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).

(18 Sep '15, 11:45) Reg Domaratzki

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.

(18 Sep '15, 11:53) Volker Barth
Comment Text Removed

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.

(21 Sep '15, 14:35) Volker Barth

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 :)

(21 Sep '15, 17:37) Breck Carter

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?

(22 Sep '15, 02:04) Volker Barth

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".

(01 Oct '15, 17:26) Breck Carter

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.

(02 Oct '15, 02:59) Volker Barth

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.

(02 Oct '15, 10:18) Reg Domaratzki

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...

(02 Oct '15, 10:27) Volker Barth

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.

(02 Oct '15, 16:09) Reg Domaratzki

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.

(02 Oct '15, 17:26) Volker Barth
1

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+.

(05 Oct '15, 10:06) Reg Domaratzki
showing 2 of 18 show all flat view

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

permanent link

answered 05 Oct '15, 10:52

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.2k33575
accept rate: 41%

edited 08 Feb '16, 11:29

dbo... one more reason to loathe ASE :)

(08 Oct '15, 16:04) Breck Carter
Replies hidden

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:)

(09 Oct '15, 01:36) Volker Barth

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.

(14 Oct '15, 09:02) Reg Domaratzki
Replies hidden
1

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.

(14 Oct '15, 09:42) Volker Barth

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

(04 Feb '16, 17:41) Volker Barth
Replies hidden
1

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.

(05 Feb '16, 09:38) Reg Domaratzki

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.

(08 Feb '16, 11:30) Reg Domaratzki

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

(08 Feb '16, 12:14) Volker Barth

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!

(07 Jun '16, 05:41) Volker Barth

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 :)

(07 Jun '16, 11:47) Breck Carter

You're so right, sorry:)

(07 Jun '16, 11:49) Volker Barth

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.

(07 Jun '16, 13:40) Justin Willey

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

(08 Jun '16, 00:57) Volker Barth
showing 3 of 13 show all flat view

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.

permanent link

answered 18 Sep '15, 12:05

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 23 Sep '15, 04:03

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.

(22 Sep '15, 02:31) Volker Barth
Replies hidden

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.

(22 Sep '15, 11:08) Reg Domaratzki

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?

(22 Sep '15, 11:09) Reg Domaratzki

rebuild it using dbunload -ar

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

(22 Sep '15, 11:32) Volker Barth

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".

(23 Sep '15, 03:49) Volker Barth

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)?

(23 Sep '15, 04:48) Volker Barth

@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".

(25 Sep '15, 03:31) Volker Barth
Replies hidden

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.

(25 Sep '15, 08:32) Reg Domaratzki

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.

(01 Oct '15, 16:51) Reg Domaratzki
showing 2 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×198
×80
×77

question asked: 18 Sep '15, 10:22

question was seen: 1,149 times

last updated: 08 Jun '16, 00:57