Question Re: error with sr_add_message_server in Technology Q&A

error with sr_add_message_server

Baron — Wed, 06 Jul 2022 06:10:11 GMT

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!

Re: error with sr_add_message_server

VolkerBarth — Wed, 06 Jul 2022 07:42:59 GMT

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

Re: error with sr_add_message_server

Baron — Wed, 06 Jul 2022 08:17:18 GMT

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' );

Re: error with sr_add_message_server

VolkerBarth — Wed, 06 Jul 2022 09:07:42 GMT

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.

Re: error with sr_add_message_server

chris_keating — Wed, 06 Jul 2022 09:43:50 GMT

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 Database as the Message Server

Re: error with sr_add_message_server

Baron — Wed, 06 Jul 2022 09:45:55 GMT

No, this is done

Re: error with sr_add_message_server

Baron — Wed, 06 Jul 2022 11:44:53 GMT

I have two databases:

@@version = '17.0.9.4882': this one had the problem, the procedure sr_add_message_server is defined here with sql security definer
@@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.

Re: error with sr_add_message_server

VolkerBarth — Wed, 06 Jul 2022 11:55:15 GMT

Wow, a subtle difference with a drastic effect 🙂