BigSQL and Active Directory

As a default, only Linux local user can connect to BigSQL. After cluster Kerberization, several steps should be undertaken to allow also AD user to connect. https://www.ibm.com/support/knowledgecenter/en/SSCRJT_6.0.0/com.ibm.swg.im.bigsql.doc/doc/bi_admin_biga_enable_authentication.html After the change, AD authentication is enabled and the user can connect to BigSQL using a password defined in Active Directory. It does not allow passwordless connection by using Kerberos ticket.

HDP, Verify that PAM is enabled.

BigSQL -> Configs -> Advanced bigsql-users-env If not, activate and restart BigSQL. After enabling, verify that /etc/pam.d/db2 file exists.

CDP, Cloudera

https://www.ibm.com/support/knowledgecenter/SSCRJT_7.1.0/com.ibm.swg.im.bigsql.doc/doc/bi_admin_biga_enable_authentication.html

/usr/ibmpacks/IBM-Big_SQL/7.1.0.0/bigsql-cli/bigsql-config -enablePam

Restart BigSQL after the change.

Modify /etc/pam.d/db2 file

(required also for CDP cluster)

Replace all pam_ldap.so with pam_sss.so. After modification, the file should be like:

#%PAM-1.0

auth    required    pam_env.so
auth    sufficient  pam_unix.so likeauth nullok
auth    sufficient  pam_sss.so use_first_pass
auth    required    pam_deny.so

account  required   pam_unix.so
account  sufficient pam_succeed_if.so uid < 100 quiet
account  sufficient pam_sss.so
account  required   pam_permit.so

password requisite  pam_cracklib.so retry=3 dcredit=-1 ucredit=-1
password sufficient pam_unix.so nullok use_authtok md5 shadowremember=3
password sufficient pam_sss.so  use_first_pass
password required   pam_deny.so

session  required   pam_limits.so
session  required   pam_unix.so

In the case of HA, the change should be done on both, primary and secondary, nodes.

Verify

Try to connect as AD user.

db2 connect to bigsql user user1

Enter current password for user1: 

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.9.0
 SQL authorization ID   = USER1
 Local database alias   = BIGSQL

Check group membership for a user. It demonstrates how group membership is recognized by BigSQL. It should reflect Linux/AD membership.

db2 "SELECT VARCHAR(GROUP,20) FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID('user2'))"

GROUP               
--------------------
CENTOSGROUP         
DOMAIN USERS        

  2 record(s) selected.