error with sr_add_message_server

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!

dbremote replication http

asked 06 Jul '22, 06:10

Baron
1.8k●119●131●153
accept rate: 50%

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

(06 Jul '22, 07:42) Volker Barth

Replies hidden

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

(06 Jul '22, 08:17) Baron

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.

(06 Jul '22, 09:07) Volker Barth

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

(06 Jul '22, 09:43) Chris Keating

Replies hidden

No, this is done

(06 Jul '22, 09:45) Baron

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 06 Jul '22, 11:44

Baron
1.8k●119●131●153
accept rate: 50%

Wow, a subtle difference with a drastic effect :)

(06 Jul '22, 11:55) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

dbremote ×75
replication ×57
http ×28

question asked: 06 Jul '22, 06:10

question was seen: 266 times

last updated: 06 Jul '22, 11:55

SAP SQL Anywhere

error with sr_add_message_server

Follow this question

Related questions