Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

------------------------------
Prakash Shanmugam
------------------------------

To explain some more, use the command CHGDDMTCPA to set the parameter Lowest authentication method to *USRID or *USRIDPWD. After this, you run SQL CONNECT statement that provides user ID (or ID + PW) that exists in the target LPAR BEFORE running your SQL SELECT statement. 

CONNECT TO <target LPAR ID> USER <user ID> USING <password> 

------------------------------
Satid S

Original Message:
Sent: Mon August 12, 2024 04:36 PM
From: Prakash Shanmugam
Subject: User error while executing 3 part SQL

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

------------------------------
Prakash Shanmugam
------------------------------

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

Thanks @Rich Malloy. Will check with our admin group for that approach of adding all users.

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!

Welcome Prakash. One other thought..if this is NOT an interactive process and is only done in batch...perhaps the job that runs in batch could be done via a service account instead of running it under the individual user profiles that submitted it. 

Thanks @Rich Malloy. Will check with our admin group for that approach of adding all users.

Hi Prakash - you don't need a new group profile, its just one way of accomplishing this. But yes, you can work with your admins/security engineers to add all those users to a new group profile if you desire. Another approach with this is to simply add all your users that you want to execute this into the "database" via addsvraute command. you can use a CL program to do that for you pretty easily enough. 

@Rich Malloy Thanks for the inputs.

In this article it talks about a group profile and adding necessary users to that profile. Then give access to that group profile for remote database. 

Wish we can use ADDSVRAUTE command, so no need to mention user credentials in the program while trying to connect to RDB.

However in our scenario, there are many thousands of user profiles already created and in use. If I create a new group profile, is it possible to assign all existing user ids to that profile? Also believe any new user profiles created, had to be explicitly added to this group. Any suggestions or recommendations please?

Thanks!

This article should help. Its a way of defining a 'service account' to be used for these types of transactions without the need to either have every profile on every lpar or to have the passwords be the same across all lpars for a specific profile. You will likely want to discuss this with your system admins as well though. Good luck - hope this helps

Simplified DDM and DRDA authentication entry management using group profiles (ibm.com)

Need to access data from one IBMi system to the other using 3 part SQL. Both systems are in same server, but different LPARs.

The user profile from source system is expected to exist in target system also. As there are many user ids (1000+) from source system missing in target system, 3 part SQL is failing.

Sample SQL executed in an SQLRPGLE program from Source system:

Select '1'  Into :WkVariable From TrgetSvr/DataLib/GrpTable Where Grp = :wkGrp With UR

Is there a way to execute 3 part SQL under a specific/common user, so not each user had to exist in the target system? 

Errors received:

Message . . . . :   Authorization failure on DRDA/DDM connection attempt. (CPF9190)    

Cause . . . . . :   A connection attempt failed with reason code 6. (User ID not valid. For an IBM i application server this could mean a damaged user profile or PASSWORD(*NONE).)

SQ30082 Authorization failure on distributed database connection attempt

Thanks!