LDAP Authentication by using Windows Active Directory

Learn how to configure SSL or TLS for LDAP authentication by using Windows Active Directory.

For Netezza Performance Server 11.1.0.1 and lower

  1. Add the Windows Active Directory users to the database.
    create user <user> password <password>
    Define the password according to your password policy.
    Example:
    nzsql -c "create user ad_user1 password 'password';"
  2. Set the authentication type.
    SET AUTHENTICATION LDAP
    • Set authentication to AD with the SSL OFF
      1. Run the command.
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER 'AD_SERVER' SSL 'OFF' BINDPW Netezzapwd BINDDN 'cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com'; "

        Where cn refers to the Common Name and dc to the Domain component.

      2. As the root user, update the /etc/sssd/sssd.conf file on the host.
        You might need to manually change the lines in bold if they are not already set.
        =============================/etc/sssd/sssd.conf=============================

[domain/external_ldap]

ldap_tls_reqcert = never
ldap_id_use_start_tls = False
ldap_schema = AD
ignore_group_members = True
auth_provider = ldap
id_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
ldap_id_mapping = True
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
ldap_user_search_base = dc=nzdevelopment,dc=com
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
entry_cache_timeout = 600
ldap_default_bind_dn = cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_uri = ldap://<AD_SERVER>:389
#debug_level = 10
case_sensitive = False
ldap_group_name = CN
ldap_user_name = sAMAccountName

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQANEC2d5xigH0d4w9phf5F9zD4PvXWvxwz61LYsoQq5BM/M7WslokdqbS6CnOyTzGp4XmzCC7owRfYq8sALPYypoAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
      3. As the root user, restart the sssd service.

        If you have many groups and or users on the AD server, you can improve the login performance by adding ldap_group_member = uniqueMember to the sssd.conf file.

        1. Add ldap_group_member = uniqueMember to the [domain/external_ldap] section in /etc/sssd/sssd.conf.
        ignore_group_members = True (add only if not present since this variable was already exist)
ldap_group_member = uniqueMember
        2. Restart the sssd service:
        systemctl stop sssd ; rm -f /var/lib/sss/db/* ; systemctl start sssd

        3. Verify the authentication.

    • Set authentication to AD with the SSL ON
      A certificate must be issued to the AD server by a trusted CA. Obtain the CA certificate file and save it on a location on the Netezza Performance Server system. For the Netezza Performance Server system high availability (HA) systems, save the file in a location on the shared drive, such as a new directory under /nz. Both Netezza Performance Server system nodes must be able to access the certificate file by using the same path name.
      1. Set AD authentication with SSL ON.
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER 'AD_SERVER' SSL 'ON' BINDPW Netezzapwd BINDDN 'cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com'; "
      2. As the root user, update the /etc/sssd/sssd.conf file on the Netezza Performance Server host manually.

        You can connect to the AD server over SSL or TSL. You might need to manually change the lines in bold if they are not already set.

        1. To connect to the AD server over SSL, update the following items.
          ===========================/etc/sssd/sssd.conf=============================
[domain/external_ldap]

ldap_tls_reqcert = demand
ldap_id_use_start_tls = False
ldap_tls_cacert = /nz/caCert/ca_cert.pem
ldap_schema = AD
ignore_group_members = True
auth_provider = ldap
id_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
ldap_id_mapping = True
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
ldap_user_search_base = dc=nzdevelopment,dc=com
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
entry_cache_timeout = 600
ldap_default_bind_dn = cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_uri = ldaps://<AD_SERVER>:636
#debug_level = 10
case_sensitive = False
ldap_group_name = CN
ldap_user_name = sAMAccountName

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQAEPSvINy7WB7Y5ULLHAgp9PNt9Ivdd5KZlp7DDzN7a+5wpRrq8dBoA4RonugOBXhxMA44TglOom7u4cqMBpFeoAAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
[secrets]
        2. To connect to the AD server over TLS, use the following template.
          ===========================/etc/sssd/sssd.conf=============================
[domain/external_ldap]

ldap_tls_reqcert = demand
ldap_id_use_start_tls = True
ldap_tls_cacert = /nz/caCert/ca_cert.pem
ldap_schema = AD
ignore_group_members = True
auth_provider = ldap
id_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
ldap_id_mapping = True
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
ldap_user_search_base = dc=nzdevelopment,dc=com
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
entry_cache_timeout = 600
ldap_default_bind_dn = cn=ad_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_uri = ldap://<AD_SERVER>:389
#debug_level = 10
case_sensitive = False
ldap_group_name = CN
ldap_user_name = sAMAccountName

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQAEPSvINy7WB7Y5ULLHAgp9PNt9Ivdd5KZlp7DDzN7a+5wpRrq8dBoA4RonugOBXhxMA44TglOom7u4cqMBpFeoAAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
[secrets]
        Note: 1. For the ldap_tls_cacert attribute of sssd.conf, mention the CA certificate file name with the correct directory location.

        2. The Active Directory server that is mentioned in the SET AUTHENTICATION command must match with the hostname in the LDAP server certificate file.

      3. As the root user, restart the sssd service.
        systemctl restart sssd

      If you have many groups and or users on the AD server, you can improve the login performance by adding ldap_group_member = uniqueMember to the sssd.conf file.

      1. Add ldap_group_member = uniqueMember to the [domain/external_ldap] section in /etc/sssd/sssd.conf.
      ignore_group_members = True (add only if not present since this variable was already exist)
ldap_group_member = uniqueMember
      2. Restart the sssd service.
      systemctl stop sssd ; rm -f /var/lib/sss/db/* ; systemctl start sssd

      3. Verify the authentication.

For Netezza Performance Server 11.1.0.2 and higher

  1. Add the Windows Active Directory users to the database.
    create user <user> password <password>
    Define the password according to your password policy.
    Example:
    nzsql -c "create user ad_user1 password 'password';"
  2. Set the authentication type:
    • Set authentication to AD with the SSL/TLS OFF
      1. Run the command.
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER 'AD_SERVER' SSL 'OFF' BINDPW Netezzapwd BINDDN 'cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com' ATTRNAME 'sAMAccountName';"
      2. Now the sssd.conf file looks as follows.
        [domain/external_ldap]

###The below common parameters and values should not be changed

ldap_default_authtok_type = obfuscated_password
ldap_schema = AD
ldap_group_name = CN
ldap_user_name = sAMAccountName
ignore_group_members = True
auth_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
id_provider = ldap
entry_cache_timeout = 600
case_sensitive = False
ldap_id_mapping = True
#ldap_group_attribute =
#debug_level = 10

###Supplied from Input

ldap_uri = ldap://AD_SERVER:389
ldap_user_search_base = dc=nzdevelopment,dc=com
ldap_default_bind_dn = cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_tls_reqcert = never
#ldap_id_use_start_tls =
#ldap_tls_cacert =

ldap_default_authtok = AAAQAA5gKJVg+dHVdi2LU9uTepJAJRYtMh1mlO8vp4ysVuFjw5OrxTeY4MteantA1+FLTm2+XGmtdokCsiZAfGExIlsAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
[secrets]

        If you have many groups and or users on the AD server, you can improve the login performance by adding ldap_group_member = uniqueMember to the sssd.conf file.

        1. Add ldap_group_member = uniqueMember to the [domain/external_ldap] section in /etc/sssd/sssd.conf.
        ignore_group_members = True (add only if not present since this variable was already exist)
ldap_group_member = uniqueMember
        2. Restart the sssd service.
        systemctl stop sssd ; rm -f /var/lib/sss/db/* ; systemctl start sssd

        3. Verify the authentication.

    • Set authentication to AD with the SSL ON
      A certificate must be issued to the AD server by a trusted CA. Obtain the CA certificate file and save it on a location on the Netezza Performance Server system. For the Netezza Performance Server system high availability (HA) systems, save the file in a location on the shared drive, such as a new directory under /nz. Both Netezza Performance Server system nodes must be able to access the certificate file by using the same path name.
      1. Set AD authentication with SSL ON.
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER 'AD_SERVER' SSL 'ON' BINDPW Netezzapwd BINDDN 'cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com' ATTRNAME 'sAMAccountName' CACERT '/nz/caCert/ca_cert.pem';"
        CACERT is the path of the CA certificate file.
      2. Now the sssd.conf file looks as follows.
        [domain/external_ldap]

###The below common parameters and values should not be changed

ldap_default_authtok_type = obfuscated_password
ldap_schema = AD
ldap_group_name = CN
ldap_user_name = sAMAccountName
ignore_group_members = True
auth_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
id_provider = ldap
entry_cache_timeout = 600
case_sensitive = False
ldap_id_mapping = True
#ldap_group_attribute =
#debug_level = 10

###Supplied from Input

ldap_uri = ldaps://AD_SERVER:636
ldap_user_search_base = dc=nzdevelopment,dc=com
ldap_default_bind_dn = cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_tls_reqcert = demand
ldap_id_use_start_tls = False
ldap_tls_cacert = /nz/caCert/ca_cert.pem

ldap_default_authtok = AAAQAAIxX3meMywHbwCnnFQRhRJAHpAICVBjoXmg6OhLr9ASy0RijAO4WdYwAioHf5Fmy6yQc0g8/CXOrx7VQ1BbrfYAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
[secrets]

      If you have many groups and or users on the AD server, you can improve the login performance by adding ldap_group_member = uniqueMember to the sssd.conf file.

      1. Add ldap_group_member = uniqueMember to the [domain/external_ldap] section in /etc/sssd/sssd.conf.
      ignore_group_members = True (add only if not present since this variable was already exist)
ldap_group_member = uniqueMember
      2. Restart the sssd service.
      systemctl stop sssd ; rm -f /var/lib/sss/db/* ; systemctl start sssd

      3. Verify the authentication.

    • Set authentication to AD with the TLS ON

      You can establish a secure connection to the LDAP/AD server by using TLS. To do this, enable the TLS flag with the CA certificate file.

      1. Set the AD authentication with the TLS on.
        nzsql -c " SET AUTHENTICATION LDAP BASE 'dc=nzdevelopment,dc=com' NAMECASE lowercase SERVER 'AD_SERVER' SSL 'OFF' TLS 'ON' BINDPW Netezzapwd BINDDN 'cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com' ATTRNAME 'sAMAccountName' CACERT '/nz/caCert/ca_cert.pem';"
        Note: You can't set both TLS and SSL to ON.
      2. Now the sssd.conf file looks as follows.
        [domain/external_ldap]

###The below common parameters and values should not be changed

ldap_default_authtok_type = obfuscated_password
ldap_schema = AD
ldap_group_name = CN
ldap_user_name = sAMAccountName
ignore_group_members = True
auth_provider = ldap
ldap_rfc2307_fallback_to_local_users = True
ldap_referrals = False
override_homedir = /home/%u
ldap_network_timeout = 3
ldap_opt_timeout = 60
cache_credentials = True
entry_cache_group_timeout = 0
entry_cache_user_timeout = 0
ldap_search_timeout = 30
id_provider = ldap
entry_cache_timeout = 600
case_sensitive = False
ldap_id_mapping = True
#ldap_group_attribute =
#debug_level = 10

###Supplied from Input

ldap_uri = ldap://AD_SERVER:389
ldap_user_search_base = dc=nzdevelopment,dc=com
ldap_default_bind_dn = cn=ad_admin_user1,cn=Users,dc=nzdevelopment,dc=com
ldap_tls_reqcert = demand
ldap_id_use_start_tls = True
ldap_tls_cacert = /nz/caCert/ca_cert.pem

ldap_default_authtok = AAAQAAIxX3meMywHbwCnnFQRhRJAHpAICVBjoXmg6OhLr9ASy0RijAO4WdYwAioHf5Fmy6yQc0g8/CXOrx7VQ1BbrfYAAQID
[sssd]
services = nss, ifp, sudo, ssh, pam
domains = external_ldap

[nss]
memcache_timeout = 600
homedir_substring = /home

[pam]
#debug_level = 10

[sudo]
[autofs]
[ssh]
[pac]
[ifp]
[secrets]
        Note: The Active Directory server that was mentioned in the SET AUTHENTICATION command must match with the hostname from the LDAP server certificate file.

      If you have many groups and or users on the AD server, you can improve the login performance by adding ldap_group_member = uniqueMember to the sssd.conf file.

      1. Add ldap_group_member = uniqueMember to the [domain/external_ldap] section in /etc/sssd/sssd.conf.
      ignore_group_members = True (add only if not present since this variable was already exist)
ldap_group_member = uniqueMember
      2. Restart the sssd service.
      systemctl stop sssd ; rm -f /var/lib/sss/db/* ; systemctl start sssd

      3. Verify the authentication.