error with sr_add_message_server

[Baron]

What could be the reason that while calling sr_add_message_server('Cons'), I get the following error:

Unable to connect to server 'dbremote_msgs_server': DirectoryAccess servers require an externlogin

the user Cons is existing and the publish role is granted to him, and a role is created for him!

[VolkerBarth]

Is there an EXTERNLOGIN entry for the according remote server "dbremote_msgs_server"? AFAIK, this is required unless the directory access server was created with option ALLOW 'ALL' USERS.

---

As this error appears during the "sr_add_message_server" call, what have you specified as @owner parameter? Because the procedure will do a CREATE EXTERNLOGIN for that username (among others) to "dbremote_msgs_server...

[Baron]

How can I check whether there is EXTERNLOGIN or not?

I call the procedure exactly like in the documentation:

CALL sr_add_message_server( 'cons' );

[VolkerBarth]

select *
from sysserver S key left join sysexternlogin EL
where S.srvname = 'dbremote_msgs_server'

FWIW, the contents of the SQL Remote management procedures like sr_add_message_server() is available in the SYSPROCEDURE system table, so that may provide further hints.

[chris_keating]

Was this missed?

 CREATE ROLE FOR USER cons;

See the following: Tutorial: Setting up a Replication System Using the HTTP Message System with the Consolidated Databa...

[Baron]

No, this is done

[Baron · Accepted solution]

I have two databases:

1. @@version = '17.0.9.4882': this one had the problem, the procedure sr_add_message_server is defined here with sql security definer
2. @@version = '17.0.9.4803': this worked fine, the procedure sr_add_message_server is defined here with sql security invoker

Changing the sql security to invoker has solved the problem in the first database.

[VolkerBarth · Accepted solution]

Wow, a subtle difference with a drastic effect 🙂