Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Connect To Using is Ignoreing User
has no ratings.
Published: 25 Jan 2013
Revised: 25 Jan 2013 - 1765 days ago
Last viewed on: 25 Nov 2017 (2940 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Connect To Using is Ignoreing User Published by: Bob Cozzi on 25 Jan 2013 view comments(1)

I have an application that connects to a remote database using embedded SQL.

I use:

EXEC SQL CONNECT TO :myRMT USER :rmtUser USING :rmtPWD;

But when I look in the joblog, it is using the current job's user profile to create the connect, not the user I specify in the RMTUSER variable.

The remote systems (the ones calling the SQL CONNECT TO statement) are on IBM i5 v5r4 whereas the headquarters systems is on IBM i v7r1 so 3-level names aren't an option yet.

I really don't want to create the same user on both system as we have people who are not permitted to get to certain systems, but those same systems house data to which they need application-level access.

Here's the cut/paste from the program itself:

Inside the field named store.rmtUser is 'BOBBY' and yet when the connection is attempted it is using the User Profile from the job instead.

if (store.rmt <> '');                                        
   if (store.firstTime);                                     
      EXEC SQL CONNECT TO   :store.rmt                       
                       USER :store.rmtUser USING :store.rmtPwd;          
      store.firstTime = *OFF;                                
   else;                                                     
      exec SQL SET CONNECTION :store.rmt;                    
   endif;                                                    
endif;                                                       

It is in a fetch loop and I am getting data from the local source machine and the remote system for the display.

I though CONNECT TO   USER USING would do the trick. Any suggestions?

[UPDATE: More info... if the user calling the app has USRCLS(*SECOFR) the CONNECT TO USER USING works by connecting to the remote server with the user in the RMTUSER variable.

The specific user we were trying to get working, has USRCLS(*SYSOPR) and no, I did not setup this system.

I wonder what needs to change to make this thing work?]

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL Connect To Using is Ignoreing User
Posted: 4 years 9 months 30 days 14 hours 2 minutes ago
Edited: Fri, 25 Jan, 2013 at 12:15:21 (1765 days ago)

Kept digging and found this; it is a two step process:

Step 1 is performed ONCE on each remote location connecting back to the host server:

CHGSYSVAL SYSVAL(QRETSVRSEC) VALUE('1')

Step 2 is performed ONCE for each User Profile that does NOT exist on the host server or that does NOT have *SECOFR user class:

ADDSVRAUTE USRPRF(XXXXX) SERVER(HOSTSRV) USRID(HOSTUSER) PASSWORD(HOSTPWD)

Of course 'HOSTSRV' is replaced with the remote database server name (must be in all upper case). HOSTUSER and HOSTPWD are replaced with the  user ID and PWD being used to connect to the host server.

 

But in the end, I'm still not happy with this solution. It means that in my RPG program I have to do a CONNECT TO XXXX User HOSTUSER Using :pwd; and if I wanted to connect using another user, I'd have to go add it to the SVRAUTE too. This just seems goofy to me.