Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    36

    Unanswered: Tracking unsuccessful connection attempts to database.

    Hello,

    We have a DB2 UDB v8.1 FP5 database on AIX 5.3 server. Sometime we encounter users trying to connect using the wrong password which locks the user. We would like to track the IP address of the unsuccessful attempts made.

    I wanted to know is there any way from the database logs that we can track the connection attempts with the IP of the client fro where the attempt is made?

    Thanks.

  2. #2
    Join Date
    Jul 2008
    Posts
    2
    Quote Originally Posted by ritz
    Hello,

    We have a DB2 UDB v8.1 FP5 database on AIX 5.3 server. Sometime we encounter users trying to connect using the wrong password which locks the user. We would like to track the IP address of the unsuccessful attempts made.

    I wanted to know is there any way from the database logs that we can track the connection attempts with the IP of the client fro where the attempt is made?

    Thanks.
    some useful information must be there in db2diag.log

  3. #3
    Join Date
    Jul 2008
    Posts
    9

    IP address

    Try this one:

    1. List applications
    2. Find the Application Id
    Applicaion ID 17216851.1444.000B00180031 (for Example)

    IP address (group two characters together and the convert from hex to dec - you can use Calculator program - just turn on Scientific option from View menu)
    17=23
    21=33
    68=104
    51=81

    So IP adress is 23.33.104.81

    Port number in hexa
    1444 = 5188

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can track that with the DB2 Audit Facility. Check the documentation on how to set that up, but be careful to only limit reporting to unsucessful access attempts.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2008
    Posts
    9

    Thats true.... But....

    Putting DB2audt is a good idea as checking for db2diag.log file. But in db2audt we need to change the file path as well as the sizes. where as all the user info got registered in db2diag with the diag level 0/1 as well. So file size will be limited. As well in linux and mainframes db2audt can give u trouble for file(PS & PDS) path and permissions.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by niladridb2dba
    Putting DB2audt is a good idea as checking for db2diag.log file. But in db2audt we need to change the file path as well as the sizes. where as all the user info got registered in db2diag with the diag level 0/1 as well. So file size will be limited. As well in linux and mainframes db2audt can give u trouble for file(PS & PDS) path and permissions.
    The DB2 Audit facility will capture all the necessary information about an unsuccessful connection attempt, including user, client IP address, etc. It is easy to set it up to only capture unsuccessful attempts. As for the rest of your comments, I have not idea what you are talking about.

    BTW, if the only DB2 certification exam you are able to pass is the "Database Associate" exam (which is basically about SQL and not DB2 database administration), then you really don't know much about DB2 database administration.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2005
    Posts
    36
    Thanks for all the inputs.

    The db2audit was on and I managed to get the information against the VALIDATE failure events.
    However, I am still not sure about how to get the IP address. Here is a sample entry:

    timestamp=2008-07-02-23.30.04.878565;category=VALIDATE;audit event=CHECK_GROUP_MEMBERSHIP;
    event correlator=2;event status=-1092;
    database=DB2INST;userid=DB2USER;authid=DB2USER;exe cution id=db2admin;
    origin node=0;coordinator node=1;
    application id=GA44094F.P1C2.0D0B72153004;application name=myapp;
    auth type=SERVER_ENCRYPT;

    How do I get the IP address. I checked in the Admin Implementation Guide but still am clueless about this.
    Last edited by ritz; 07-03-08 at 06:22.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    GA44094F is the IP address. Convert each pair of Hex numbers to IP address in decimal:

    GA.44.09.4F is in hex and convert these to decimal.

    For a DRDA connection, if the first letter is above F, convert it as follows:
    G=0
    H=1
    I=2
    J=3
    etc

    10.68.09.79
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jul 2008
    Posts
    9

    TO Mr. A

    Dear Sir
    I really acknowledge ur knowledge . From u like persons we are learning many things.

    Just one request..... while reply to the juniors u can a bit polite... Just a request from a junior DBA......


    With regards........................................... ..

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Where was Marcus impolite? Marcus is giving very qualified answers. If you don't like the way we discuss in this group, then just don't read it and/or ask questions. Besides, there are quite a few posts in this group that ignore some basic rules of politeness, for example the ones listed here: Must Read before posting
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Where was Marcus impolite? Marcus is giving very qualified answers. If you don't like the way we discuss in this group, then just don't read it and/or ask questions. Besides, there are quite a few posts in this group that ignore some basic rules of politeness, for example the ones listed here: Must Read before posting
    He didn't like when I said that someone who only passes the DB2 Associate Exam (which is basically about SQL) is not a real DBA.

    I know that there are many excellent DB2 DBA's who are not certified, however, my position is that even if one passes the DB2 Associate Exam it would be best to not even mention anything about DB2 Certification in one's signature line until they pass the DB2 DBA Exam.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Ah, certifications. Nice topic...

    It is a matter of perception, I guess... I see it exactly like you, Marcus. Anyone can pass the DB2 Associate certificate with just:
    • basic knowledge of SQL and relational database systems
    • learning a little bit about DB2 products and positioning,
    • carefully reading the test questions and thinking about them.

    To bring it even more to the point: he who fails the DB2 Associate certificate shouldn't work with any relational database system henceforth.

    In the past, I had a group of master students who took a 3 month (4 hours per week) database administration course. Nearly all of them got the DB2 Admin certification, and close to 50% of them even managed to pass the DB2 Advanced Administration certification. Granted, a bit of luck was involved for some (good/friendly questions, accidentally choosing the right answer), but nevertheless it puts DB2 Associate into perspective.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Actually, if one has a good knowledge of SQL, one usually can afford to miss all the questions about DB2 products and positioning on the DB2 Associate Exam and still get a passing score.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jul 2008
    Posts
    9
    Actually Regarding Mr. A impolite is a betterment.... I like ur suggestion like not reading the topics.... I'll do that.....


    Its always better to avoid high ego persons............

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •