Re: Configuration/setup required for 3-part naming for SQL -- MIDRANGE-L

Let's say I am on my GDI lpar. Two WRKRDBDIRE entries of interest are:
GDI *LOCAL
GDWEB GDWEB

Now on my GDWEB lpar I have these two WRKRDBDIRE entries of interest
GDI GDI
GDWEB *LOCAL

Now on GDI I have also ran
ADDSVRAUTE USRPRF(ROB) SERVER(gdweb) PASSWORD(redacted)
ADDSVRAUTE USRPRF(ROB) SERVER(gdi) PASSWORD(redacted)

and on GDWEB I have ran the same.

So now on GDI I can run
STRSQL
SELECT * FROM GDWEB.SYSIBM.SYSDUMMY1

I have a routine that when I change my password to change all the 'SVRAUTE
entries on all lpars. I didn't say the procedure is fully automated.

On Wed, Mar 27, 2024 at 11:13 PM Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
wrote:

What are the requirements to enable 3-part naming in SQL? Google was not
my friend.

We're on 7.4 and have DB2 Multisystem (which I seem to recall was a
requirement) on all our partitions. User ID and password are the same on
all partitions.

CONNECT TO QASYS1 USER DBALE USING 'the_correct_password'
^ returns SQ30061 "Relational database QASYS1 not found." Which is weird,
because WRKRDBDIRE clearly shows QASYS1 (see below)

Using either:
1) select * from QASYS1.DBALELIB.TESTFILE
or 2) CONNECT TO QASYS1 USER DBALE USING 'a_wrong_password'
results in:

Message ID . . . . . . : SQ30082 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Authorization failure on distributed database
connection
attempt.
Cause . . . . . : A connection attempt failed with reason code 17.
17 -- The security mechanism requested by the client is not supported or
allowed at the server. See recovery information below.
Recovery . . . : A common cause for reason code 17
is that the server requires a password, but because the client does not
have
a password to send, sends only a user ID. Or, the server requires an
encrypted password and the client did not send an encrypted password. A
password can be supplied by the user in two ways:
1 -- By using the USER ... USING ... clause on the SQL CONNECT
statement, or
2 -- By using the ADDSVRAUTE CL command to add a server authorization
entry
for the remote server under the user's profile. The server name (DRDA
RDB
NAME) must be entered in upper case.
The DRDA server can be configured with the CHGDDMTCPA CL command to not
require a password, or to not require an encrypted password.

DSPSVRAUTE, on both the source and target partition, shows my user profile
with Server=*ANY and Password Stored=*YES.

Display Relational Database Entry Detail
Relational database . . . . . . : QASYS1
Remote location:
Remote location . . . . . . . : QASYS1
Type . . . . . . . . . . . . : *IP
Port number or service name . : *DRDA
Remote authentication method:
Preferred method . . . . . . : *USRENCPWD
Allow lower authentication . : *ALWLOWER
Secure connection . . . . . . : *NONE
Encryption algorithm . . . . . : *DES
Relational database type . . . . : *REMOTE
*** CONFIDENTIALITY NOTICE: The information contained in this
communication may be confidential, and is intended only for the use of the
recipients named above. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution, or
copying of this communication, or any of its contents, is strictly
prohibited. If you have received this communication in error, please return
it to the sender immediately and delete the original message and any copy
of it from your computer system. If you have any questions concerning this
message, please contact the sender. ***
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.