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!

asked 06 Jul '22, 06:10

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

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
Replies hidden

No, this is done

(06 Jul '22, 09:45) Baron

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.

permanent link

answered 06 Jul '22, 11:44

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

Wow, a subtle difference with a drastic effect :)

(06 Jul '22, 11:55) Volker Barth
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:

×78
×60
×31

question asked: 06 Jul '22, 06:10

question was seen: 453 times

last updated: 06 Jul '22, 11:55