Expand all | Collapse all

ODBC Authentication Setup

  • 1.  ODBC Authentication Setup

    Posted Wed February 19, 2020 09:31 AM

    Hi all,

    I am going to setup SSO authentication for Informix ODBC connections in AIX.
    Followed a lot of instructions to configure PAM (pam_serv, pamauth, s=4) or Kerberos (CSM, s=7) failed, so I appreciate any help on my project.
    I cancelled to proceed with this manual ( due to deprecated SFU (Services For UNIX package) since Windows Server 2012. So need an alternate to achieve my goal.

    Time ago I already implemented Kerberos for OS authentication against Active Directory - would like to implement the same or similar method for ODBC.
    Each attempt using ODBC failed to connect.

    Would you recommend using PAM or Kerberos to tie ODBC? Or do you have other suggestions?
    Is there a possibility to combine PAM and KRB5?

    Some system details:
    AIX 7.2 + KRB5 authentication (working fine)
    Informix 12.1 + Instance for trial and error

    When neccessary I'll provide my configurations (methods, sqlhosts, onconfig, env or what else you need)

    Best regards,
    Theo (Pilk)

    Theo B (Pilk)

  • 2.  RE: ODBC Authentication Setup

    Posted Wed February 19, 2020 09:50 AM
    I would definitly go for Kerberos.

    Please post your onconfig and online log showing any errors

    Regards Rainer

    Rainer von Bongartz

  • 3.  RE: ODBC Authentication Setup

    Posted Wed February 19, 2020 05:59 PM

    Returned from Whatsapp... I read the post several times, and each time I get the same feeling: I'm confused.
    And it's not really your fault, except in one point: You don't mention your needs. Just want you tried, or intend to try.

    And you have several concepts in your post, and then there are others that may be good to bring into the table. Let me try to break it in smaller parts...

    1) Single sign on is for people who don't want to keep inserting the same password for each application. This seems a no-brainer (is there anyone who likes to repeat the authentication?!) but it's usually much more complex to setup. I don't think I ever heard about SSO without Kerberos

    2) PAM is a flexible framework for authentication. It uses a stack of modules, each doing some "simple" validation, and the final result depends on the configuration for each module and the success of each one

    3) The article you mention is a simple example to use PAM for authentication against an LDAP server (in particular Active Directory). It doesn't cover single sign on. SFU was required at the time to add the "unix attributes" to the LDAP structure of Active Directory. It's not clear to me if current AD versions already include it or not, but if you have AIX authenticating in AD, that's probably solved.

    4) Informix traditionally has the need that the user is recognized by the operating system. This comes from the need to have the user identity for at least three actions:

    a) SYSTEM() stored procedure command

    b) Debug file creation in stored procedures (when using TRACE)
    c) SET EXPLAIN files

    This means, again, traditionally that even if you configure authentication externally (PAM), it will fail if the OS doesn't recognize the user.

    The reason why I mention "traditionally" is because in 11.7 (xC3 I think) this changed as we have the ability to create mapped users (internal users, which can have external authentication, and which we can "map" to OS user(s)).

    5) You ask if PAM and Kerberos can be configured together... I never configured Informix for SSO with kerberos, but assuming there's a kerberos PAM module (I'm sure it exists) I'd say, it would be possible, as long as the kerberos protocol doesn't need anything from the client. But this would need to be proven...

    Now.... Before you start showing configs or making further tests I would suggest you decide which path to go:
    1) Do you need/want to avoid password insert at the Informix application? That would mean SSO. Or do you simply want to avoid creating the users locally?
    2) Does you application use the end user name to authenticate on the database?
    3) If you want/need SSO, do you want to "enrich" your authentication protocol to do something fancy like force different criteria for the authentication to complete (besides the user/password, things like "this user cannot connect outside business hours", or "I want to deny auth to users in a specific file" etc.? Yes to this would point to PAM

    For "simple" SSO with kerberos you should follow the links:

    This doesn't use PAM.
    If you feel you need to use PAM, I would first try to setup "simple" PAM against AD, and the article may help.

    Once you understand how PAM works, you can try to add the kerberos module.
    NEVER TEST PAM within the database server. Always use an external client to test it.

    Unfortunately I don't have an AD to run tests against it...
    Waiting for further feedback


  • 4.  RE: ODBC Authentication Setup

    Posted Thu February 20, 2020 10:17 AM
    Hi both,
    Thanks for your reply!

    First of all, sorry for the confusion about SSO and Kerberos auth.
    I want to set up a an authentication against Active Directory for users using ODBC Client on different workstations.
    Now ODBC Client connects to the database with the local AIX username/password - so there is a local AIX authentication method only (that's the temporary way for the moment).
    Desired is to get rid of this local method and using authentication against Active Directory instead. ODBC connectio should also use Kerberos auth method (or any similar) for example.

    Currently Kerberos is implemented for all AIX accounts/users on System A (sdegel00074). Domain user name + AIX account name is identical. Logon to AIX with Windows Domain password works fine so far.

    There is no need for "real" SSO to avoid password insert. Users should use their own Domain password to connect to the database via ODBC Client.

    Thanks Fernando for both links to IBM instructions.
    Following that way and trying a lot of attempts end up failing - causing more questions:

    Some Details on my environment:
    DBSERVERALIASES d88s1,d88s2,d88s3,d88mux

    d88s3  onsoctcp  sdegel00074  d88s3  csm=(GSSCSM)

    Instruction says: "You are required to configure the SQLHOSTS information about the client computer similarly."
    On client workstation the record above is defined via setnet32:

    IBM Informix Server: d88s3
    HostName: *SDEGEL00074
    Protocol: onsoctcp
    Service Name: d88s3
    Options: csm=(GSSCSM)

    Microsoft ODBC Client loads these values but testing this connection fails with username and domain password.

    CSM config on AIX:
    GSSCSM("/informix121_02/lib/csm/", "", "c=1,i=1")

    as well as on Windows workstation
    GSSCSM("C:\informix117\bin\libixgss.dll", "", "c=1,i=1")

    (this module activated only; file path ok; parameters c and i set for Kerberos)

    Current Kerberos keytab files (d88s3 and informix records just for testing purpose):
    # etc/krb5/krb5.keytab:
    > /usr/krb5/sbin/ktutil
    ktutil: rkt /etc/krb5/krb5.keytab
    ktutil: list
    slot KVNO Principal
    ------ ------ ------------------------------------------------------
    1 3 host/sdegel00074.euro.<domain>.net@euro.<domain>.net
    2 6 d88s3/sdegel00074.euro.<domain>.net@euro.<domain>.net
    3 8 informix/sdegel00074.euro.<domain>.net@euro.<domain>.net
    4 9 informix/d88s3.euro.<domain>.net@euro.<domain>.net

    Instruction says: "Client principals also exist on the KDC computer."
    Does it mean each workstation or user account needs an own keytab to be added to the host system?

    I really apreciate your help to boot on - the more I try the more i get confused.
    Do you already managed such a setup successfully?

    Best regards,

    Theo B

  • 5.  RE: ODBC Authentication Setup

    Posted 29 days ago
    Hi again,

    I do reactivate this discussion after I was able to resolve a communication issue caused by a Client SDK version mismatch (sort of encryption incopatibility). So Windows ODBC Client and Informix instance (v.12.10FC8W1 on AIX7) seem to communicate to each other.

    In this moment I get another error using Microsoft ODBC Data Source Administrator including IBM Informix ODBC Driver 4.10.FC9.

    Connection Details:
    - IBM Informix Server: d88s3
    - HostName: *SDEGEL00074
    - Protocol: onsoctcp
    - Service Name: d88s3
    - Options: s=7,csm=(GSSCSM)
    - User: either informix [DBA] or another Domain user

    Each connection attempt ends up in an client error as well as a record in online log.
    "Test connection was NOT successful [...] CSS: error reading data."

    Online Log:
    13:12:31 listener-thread: err = -5000: oserr = 0: CSM error: gss_acquire_cred: Unspecified GSS failure. Minor code may provide more information No principal in keytab matches desired name

    AIX is setup for Kerberos and domain authentication works fine. The krb5.keytab looks like:

    ktutil: rkt /etc/krb5/krb5.keytab
    ktutil: l
    slot KVNO Principal
    ------ ------ ------------------------------------------------------
    1 3 host/sdegel00074.euro.<domain>.net@euro.<domain>.net
    2 6 d88s3/sdegel00074.euro.<domain>.net@euro.<domain>.net
    3 8 informix/sdegel00074.euro.<domain>.net@euro.<domain>.net
    4 9 informix/d88s3.euro.<domain>.net@euro.<domain>.net

    d88s3 onsoctcp sdegel00074 d88s3 s=7,csm=(GSSCSM)

    concsm.cfg (I also tried each of both library files):
    GSSCSM("/informix121_02/lib/csm/", "", "c=1,i=1")
    #GSSCSM("/informix121_02/lib/csm/", "", "c=1,i=1")

    Please help me to sort out the online log error - "No principal in keytab matches desired name".
    What's wrong with the keytab file?

    I appreciate any help - Thanks in advance!

    Best regards,

    Theo B