ODBC connection

205 pts.
Tags:
AS/400
IP address
ODBC
QZDASOINIT
Is there is anyway I can know the remote IP that connecting to my server (AS/400 server) via ODBC? The AS/400 log dose not show any details about the IP it only tell me that the connection happen but without any details. Also some time the connection failed without report any info. Please, I need your help here.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

might help: NETSTAT option 4? then check the remote address and the user profile using the ODBC connection?

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Without knowing what your server OS version is, it's hard to be certain. However, every recent release going back at least to version 5 of the OS has logged client identification in the history log in message ID CPIAD09.

    The message text is of the form:
    • User &4 from client &8 connected to job &3/&2/&1 in subsystem &6 in &7 on &5.
    Substitution variable &8 contains IP address (or possibly host name).

    But relying on the history log would not be a great idea if you need more certainty. Almost any job might send a CPIAD09 with whatever substitution values are wanted. It's not guaranteed to be from a server job.

    It'd be much better to use the system audit log to track object accesses that you needed. Server job connections can also be tracked. In V5R4 and later, the audit entries contain remote IP addresses.
    35,010 pointsBadges:
    report
  • pdraebel
    Do a DSPLOG JOB(QZDASOINIT). Select the period you need. Clients connecting to QZDASOINIT server jobs will show up together with the IP or Host address.
    7,545 pointsBadges:
    report
  • osamah
    thanks alot TheRealRaven

    my OS version it 7.1, my problem here is this ODBC user i found it disabled daily and after checking the log i only found that the user did 3 trys and then disabled, but without any information about form which remote host name or ip to know 
     this is the the msg ID: CPIAD0B
    and the program who report : QZSOSIGN
    205 pointsBadges:
    report
  • pdraebel
    Looks like I heard this issue before. Sometimes Passwords are stored in the PC application making the connection. When the user changes his password on i it is not automatically changed in the PC application. Knowing the IP you can identify the offending machine, but not the application that holds the stored password. I often see this kind of issue when Users are Mapping drives to the iSeries. Solution: disconnect on the PC, enable profile for Netsserver. Connect on PC using different credentials to get the new Password in.
    7,545 pointsBadges:
    report
  • ToddN2000
    Do a DSPLOG QHST. You should see a message like this. User TESTUSER from client 182.188.1.281 connected to job 510636/QUSER/QZDASOINIT
    132,590 pointsBadges:
    report
  • osamah
    this is the msg apper in my log
    Message . . . . :   *SIGNON server job 561155/QUSER/QZSOSIGN processing      
      request for user FINNT_X on 04/20/15 00:58:41 in subsystem QUSRWRK in QSYS.
    Cause . . . . . :   The *SIGNON server is processing request 1 for user      
      FINNT_X.  The types of requests supported are as follows:                  
        1 -- Retrieve Signon Information                                         
        2 -- Change Password                                                     
        3 -- Generate Authentication Token                                       
        4 -- Generate Authentication Token on Behalf of Another User   
        5 -- Start Server                                                        

    where FINNT_X is my ODBC user, as you see the msg dose not contain any IP to tell me if this user did the try from its server or there is other server or user is making this try in order to disabled the user.
    205 pointsBadges:
    report
  • pdraebel
    If Audit journal is running you can do a CPYAUDJRN ENTTYP(PW). The resulting file will show the IP in the REMOTE ADDRESS field.
    7,545 pointsBadges:
    report
  • pdraebel
    Do a DSPLOG JOB(QZLSSERVER). You get messages about users being disabled for Netserver. Going into the detail of the message CPIB682 (F1) should reveal the IP of the failing connection.
    7,545 pointsBadges:
    report
  • osamah
    dear xxx thanks for your help i follow ur steps but i found the following:

                            Additional Message Information                        
                                                                                  
    Message ID . . . . . . :   CPF1393       Severity . . . . . . . :   70        
    Message type . . . . . :   Information                                        
    Date sent  . . . . . . :   04/20/15      Time sent  . . . . . . :   03:08:44  
                                                                                  
    Message . . . . :   User profile FINNT_X has been disabled.                   
    Cause . . . . . :   User profile FINNT_X on device *N or network address *N in
      subsystem QUSRWRK has been disabled because the maximum number of sign-on   
      attempts specified for the QMAXSIGN system value has been reached.  If the  
      device, subsystem, or network address is *N, then the information was not   
      available.                                                                  
    Recovery  . . . :   To enable the user profile, have the security officer     
      change the STATUS parameter to *ENABLED on the Change User Profile          
      (CHGUSRPRF) command.                                                        
    Technical description . . . . . . . . :   Sign-on attempts include any attempt
      to verify the user profile password. Passwords are verified by sign-on      
      operations, various servers such as File Transfer Protocol (FTP), and API   
    calls such as Get Profile Handle (QSYGETPH).

    after pressing F9 Display message details, i found this:

                               Display Message Details                          
                                                                                
    Message ID . . . . . . :   CPF1393       Severity . . . . . . . :   70      
    Date sent  . . . . . . :   04/20/15      Time sent  . . . . . . :   03:08:44
    Message type . . . . . :   Information                                      
    From . . . . . . . . . :   QUSER         CCSID  . . . . . . . . :   65535   
                                                                                
    From job . . . . . . . . . . . :   QZSOSIGN                                 
      User . . . . . . . . . . . . :     QUSER                                  
      Number . . . . . . . . . . . :     561155                                 
                                                                                
    From program . . . . . . . . . :   QSYSGNON                                 
                                                                                
    Time sent  . . . . . . . . . . :   03:08:44.170830                          
    205 pointsBadges:
    report
  • TheRealRaven
    The thread has gone far from the original question. Since no ODBC logon ever happened, there will be no log of ODBC activity for this case. The connection never got past the Signon server (QZSOSIGN).

    For a failed logon, again look in the system audit journal, QAUDJRN. The message in QHST gives date/time as 04/20/15 03:08:44. Look for T/PW entries in the audit journal, starting at approximately 03:08:43 and up to maybe 03:08:45.

    If entries are not being tracked, then the system administrator has decided they're not useful or important enough.
    35,010 pointsBadges:
    report
  • JohnsonMumbai
    Use netstat *cnn command. Search for the IP using ODBC. Type 8 against the ip to display details of the job. Then type 5 to work with job.& Thereafter use 10 to display the job log.
    1,480 pointsBadges:
    report
  • PutzGrilla

    Use the package WRKODBCJOB free on site -->  http://bryandietz.us/wrkodbcjob.html

    810 pointsBadges:
    report
  • TheRealRaven
    Neither WRKODBCJOB nor NETSTAT will help the actual problem. There is no connection for NETSTAT to show, and no ODBC job ever was contacted. But both could help with the original question. It's just that the original question was changed (a lot) in later comments.
    35,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: