RE: Configuration/setup required for 3-part naming for SQL

The screenshot of the Relational Database Entry Detail is at the bottom of the OP.

- Dan

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bdietz400
Sent: Thursday, March 28, 2024 9:27 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Configuration/setup required for 3-part naming for SQL

Not by a screen. Look into ADDRDBDIRE for each of your remote DBs

--
Bryan Dietz

On Mar 27, 2024, at 10: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. ***