How to configure a ldaps or starttls OpenLDAP server and configure ldapsearch/Softerra ldap products/tomcat/cognos/phpldapadmin to use ldaps

Jephe Wu  -  http://linuxtechres.blogspot.com   


Objective: make a normal openldap server to serve ldaps or starttls request and configure ldapsearch/Softerra ldap products/tomcat/cognos/phpldapadmin to use it
Environment: CentOS 4.8, openldap 2.2.13
Concepts:
server configuration file: /etc/openldap/slapd.conf
client configuration file: (used by such as ldapsearch, /etc/openldap/ldap.conf)

HOST ldap.jephe.com
BASE dc=jephe
TLS_CACERTDIR /etc/openldap/cacerts
tls_cacert /etc/openldap/cacerts/cacert.pem  -- most important one, used by ldapsearch, php-ldap(for phpldapadmin) etc, other 3 above might be optional

question: how do I know ldapsearch is reading /etc/openldap/ldap.conf?
Before and after running ldapsearch command line: stat /etc/openldap/ldap.conf to check access time.

The content of /etc/openldap/slapd.conf:

include        /etc/openldap/schema/core.schema
include        /etc/openldap/schema/cosine.schema
include        /etc/openldap/schema/inetorgperson.schema
include        /etc/openldap/schema/nis.schema
pidfile        /var/run/slapd.pid
argsfile    /var/run/slapd.args


[ TLSCipherSuite          HIGH:MEDIUM:+SSLv2:+SSLv3:RSA ]  -- optional
TLSCACertificateFile /etc/openldap/cacerts/cacert.pem
TLSCertificateFile /etc/openldap/cacerts/servercrt.pem
TLSCertificateKeyFile /etc/openldap/cacerts/serverkey.pem
[ TLSVerifyClient        never ]  -- optional


database    bdb
suffix        "dc=jephe"
rootdn        "cn=manager,dc=jephe"
rootpw        password
directory    /var/lib/ldap
index objectClass                       eq,pres
index ou,cn,mail,surname,givenname      eq,pres,sub
index uidNumber,gidNumber,loginShell    eq,pres
index uid,memberUid                     eq,pres,sub
index nisMapName,nisMapEntry            eq,pres,sub
access to attr=userPassword
        by self write
        by anonymous auth
        by dn="cn=manager,dc=jephe" write
        by * none
access to *
        by dn="cn=Manager,dc=jephe" write
        by users read

Steps:
1. self-signed CA certificate and server certificate creation
this part is mainly for creating ca and server certificates:
refer to http://www.openldap.org/faq/data/cache/185.html
on CentOS 4.8, you can find script called 'CA' under /usr/share/ssl/misc which is a part of openssl rpm package.

Firstly, you need to create your self-signed CA and server certificate:
cd /usr/share/ssl/misc
./CA -newca 
(enter your CA info) for common name part, just use name such as jephe
./CA -newreq
(enter your server info, common name must use server domain name such as ldap.jephe.com)
./CA -sign


after that, copy demoCA/cacert.pem to /etc/openldap/cacerts/

cp demoCA/cacert.pem /etc/openldap/cacerts/
cp newcert.pem /etc/openldap/cacerts/servercrt.pem
cp newreq.pem /etc/openldap/cacerts/serverkey.pem


2. startup server
slapd -h "ldap:/// ldaps:///"
note: this way, it will startup ssl/tls capable openldap server, it will accept starttls or ldaps connections


All client application configuration for using ldaps:

1. how to connect from ldapsearch command:
/usr/bin/ldapsearch -x -b 'dc=jephe' '(objectclass=*)' -H ldaps://ldap.jephe.com -D 'cn=manager,dc=jephe' -W  (use ldaps)
or
/usr/bin/ldapsearch -x -b 'dc=jephe' '(objectclass=*)' -Z -D 'cn=manager,dc=jephe' -W  (use starttls, connect at port 389, then starttls to switch to encrypted communication)

Other example commands:
ldapadd -x -W -D "cn=Manager,dc=jephe.dc=com" -f batchuser.jephe.ldif  -H ldaps://ldap.jephe.com

# more batchuser.jephe.ldif
dn: dc=jephe,dc=com
dc: jephe
objectClass: top
objectClass: domain

# entry-id: 3
dn: ou=People,dc=jephe,dc=com
objectClass: top
objectClass: organizationalunit
ou: People

# entry-id: 5
dn: ou=Groups,dc=jephe,dc=com
objectClass: top
objectClass: organizationalunit
ou: Groups

dn: cn=admin,ou=Groups,dc=jephe,dc=com
cn: admin
objectClass: top
objectClass: groupofuniquenames
ou: Groups
uniqueMember: uid=jephe,ou=People,dc=jephe,dc=com


dn: uid=jephe,ou=People,dc=jephe,dc=com
uid: jephe
objectClass: top
objectClass: person
objectClass: organizationalPerson
objectClass: inetorgperson
givenName: Wu
sn: Jephe
cn: Jephe Wu
userPassword: {SSHA}EWo+m4UrXE0yjgefxa4yJ54hz8451234



2. how to connect from Softerra ldap Products
ldap Administrator 2010.1?
create a new profile, use secure connection, it will use ldaps only, then basedn is 'dc=jephe', at credential column, use other credential, simple/cn=manager,dc=jephe/password.

You will receive the warning saying windows cannot verify the certificate, you can import the /etc/openldap/cacerts/cacert.pem into Softerra ldap administrator tool/certificate manager trusted root certificate column.

ldap browser 2.6 [build 650]
look for the below cognos 8 part to generate 2 files (key3.db and cert7.db)
then configuring ldaps profile as follows:
a. Overwrite the key3.db and cert7.db files from the LDAP Browser root directory with the ones just created
b. Edit your existing LDAP profile and change the port to the secure one (eg. 636)
c. on the "LDAP Settings" tab, check the box "Try to use secure connection (only LDAP v.3)"

3. how to connect from tomcat to ldaps?
You have to import above cacert.pem into CA certs as trusted CA certs.
cd /usr/local/jdk/jre/lib/security
usr/local/jdk/bin/keytool -import -file /tmp/cacert.pem -alias ldapca -keystore cacerts


for deleting and listing CA certs, use commands:
/usr/local/jdk/bin/keytool -delete -alias ldapca -keystore cacerts
/usr/local/jdk/bin/keytool -list -keystore cacerts  -v | more


4. how to know the connection is encrypted for ldaps and starttls?
use wireshark rpm package, there's tool tshark.

tshark -n port 389 -i lo/eth0
tshare -n port 389 or port 636 -i lo/eth0


use slapd -h "ldap:/// ldaps:///" -d -1 on ldap server to debug  (refer to http://www.openldap.org/doc/admin24/runningslapd.html )


5. cannot contact LDAP server, CN does not match in certificate
when you use ldapsearch, with -H parameter, you should use the same common name which is used during your server certificate creation, otherwise, ldapsearch will fail.

If you use -Z parameter with ldapsearch, which means you will use starttls instead of ldaps, you should make sure the URI line in /etc/openldap/ldap.conf to match with the common name in server certificate.

6. how to configure cognos 8 to use ldaps instead of ldap?


http://www-01.ibm.com/support/docview.wss?uid=swg21344083 
(Configuring LDAPS (LDAP via SSL) for CRN/Cognos 8)
 
a. use IE to browse https://ldapserver:636 or use  
openssl s_client -connect serveripaddress:636 -showcerts 
to get the CA and server certificates, save it as base64 encoded PEM file
b. download NSS 3.3.2 and NSPR 4.1.2 
from http://www.mozilla.org/projects/security/pki/nss/release_notes_332.html 
extract them to a folder, then copy the contents of NSPR-4.1.2/lib to NSS-3.3.2/bin
go to NSs-3.3.2/bin, then run 
 
certutil -N -d .
certutil -A -n MyServer -d .  -i servercert.pem -t P
certutil -A -n MyCA -d . -i cacert.pem -t C,C,C  (should be optional)

then copy BOTH cert7.db file and  key3.db files under NSS-3.3.2/bin to cognos configuration directory or any meaning directory.

Change Cognos configuration parameters:
1. Provide the absolute path to the cert7.db file for the SSL Certificate Database property in Cognos Configuration.
2. provide ldap server hostname and port number such as jephe.domain.com:636 (must use domain name here, not IP address, the domain name must be same as the common name for server certificate )
3. then restart cognos, then ldaps is ready to use.
 
7. how to use ldaps for phpldapadmin ?

phpldapadmin use Apache php module (php-ldap) as extension to connect to ldap server.
php-ldap reads /etc/openldap/ldap.conf everytime it tries to connect to ldap server,  every time when apache restarts, it reads /etc/openldap/ldap.conf configuration for  tls_cacert /etc/openldap/cacerts/cacert.pem line, read it into memory for use through Apache session until next time web server gets restarted again.


You can use command stat /etc/openldap/ldap.conf to check it actually reads this file.

For /var/www/html/phpldapadmin/config/config.php, you should config it as follows:

$ldapservers->SetValue($i,'server','name','LDAP SP');
$ldapservers->SetValue($i,'server','host','ldaps://ldap.jephe.com:636');
$ldapservers->SetValue($i,'server','base',array('dc=jephe'));
$ldapservers->SetValue($i,'login','dn','cn=Manager,dc=jephe');
$ldapservers->SetValue($i,'login','pass','password');
$ldapservers->setValue($i,'server','port',0);  -- optional


note: please refer to
http://phpldapadmin.sourceforge.net/wiki/index.php/Server:server:host
most important one: the server host part must use the domain name, cannot ip address, domain name must also match the one you created certificate. Cannot use it such as the following:
$ldapservers->SetValue($i,'server','host','ldaps://10.0.0.1:636');

Note: what if you have multiple ldaps servers for multiple dc in config.php in phpldapadmin? you need to take note of the following  facts:
a. the /etc/openldap/ldap.conf can only accept one line of tls_cacert, if you put multiple line of tls_cacert, the later one will be used.
b. you can solve this problem by put 2 CA certs together into one files like below, then both ca certs will be used in phpldapadmin config.php
-----BEGIN CERTIFICATE-----
MII...

......
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
MII...

.....
-----END CERTIFICATE-----






FAQ:

1. ldapsearch doesn't work.
make sure you are using the default ldapsearch which comes with openldap rpm, not the one you compiled. Run 'which ldapsearch' to find out the full path.

2. Softerra ldap browser doesn't work.
See above client configuration part, you should configure key3.db and cert7.db.

3. how to get base64 encoded CA and server certificates for ldaps?
a. use IE to browse https://ldapserver:636 then save as file for both CA and server certificates
b. use openssl s_client -connect serveripaddress:636 -showcerts
Output:
1. [root@jephe /]# /usr/bin/ldapsearch -x -b 'dc=jephe'    '(objectclass=*)' -D 'cn=manager,dc=jephe' -Z -W
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base with scope sub
# filter: (objectclass=*)
# requesting: ALL
#

dn: dc=jephe
objectClass: dcObject
objectClass: organization
o: LDAP Server
dc: dev

# Manager
dn: cn=Manager,dc=jephe
objectClass: organizationalRole
cn: Manager

# People,
dn: ou=People,dc=jephe
ou: People
objectClass: top
objectClass: organizationalUnit


# jephe, People
dn: uid=jephe,ou=People,dc=jephe
cn: Jephe Wu
sn: Wu
uid: jephe
objectClass: inetOrgPerson
objectClass: top
userPassword:: e1NNRDV9enRmT1ppakNTeWljeVM0TytkakZlaEo0azE2Y3NBPT0A


# Groups,
dn: ou=Groups,dc=jephe
objectClass: top
objectClass: organizationalUnit
ou: Groups

# training, Groups,
dn: cn=training,ou=Groups,dc=jephe
cn: training
objectClass: groupOfUniqueNames
objectClass: top
uniqueMember: uid=jephe,ou=People,dc=jephe



2. how to use slappasswd or other program to generate SSHA password for rootpw or usrpassword?

Please refer to http://www.openldap.org/faq/data/cache/347.html, this FAQ states you can use the following program and other programs to generate SSHA password:


#! /usr/bin/perl
#
# This small script generates an Seeded SHA1 hash of 'secret'
# (using the seed "salt") for use as a userPassword or rootpw value.
#
use Digest::SHA1;
use MIME::Base64;
$ctx = Digest::SHA1->new;
$ctx->add('secret');
$ctx->add('salt');
$hashedPasswd = '{SSHA}' . encode_base64($ctx->digest . 'salt' ,'');
print 'userPassword: ' .  $hashedPasswd . "\n";
 
How to verify? -> you can use Softerra LDAP Administrator 2010.1 to verify usrpassword
part.
 
3. how to start ldap service to service ldaps only on CentOS?
If you use openldap rpm, you should find file /etc/init.d/ldap, you can modify this 
file to serve ldaps only when certificate is configured in /etc/openlda/ldap.conf 
 
References:
1. http://www.ldapadministrator.com/forum/viewtopic.php?t=506 (LDAP
 Browser SSL Support)
2. http://www-01.ibm.com/support/docview.wss?uid=swg21344083 
(Configuring LDAPS (LDAP via SSL) for CRN/Cognos 8)
3. http://phpldapadmin.sourceforge.net/wiki/index.php/Server:server:host 
 

Use Openssh chroot and ACL to make user to delete files under certain folder

Jephe Wu -  http://linuxtechres.blogspot.com


Objective: let normal user accounts to be able to delete all files under /usr/local/tomcat/files/ directory and chroot to /usr/local/ after sftp.
Environment: CentOS 5.4, openssh 5.X, /usr/local/tomcat and all subdirectories are owned by tomcat:tomcat


Challenges:
1. default CentOS 5.4 doesn't come with the version of Openssh which is able to do chroot.
2. openssh internfal-sftp chroot requires all components of the pathname must be root-owned directories that are not writable by any other user or group, so we cannot chroot to /usr/local/tomcat/files directory, as tomcat is owned by tomcat , not root.
3. /usr/local/tomcat/files is owned by tomcat, not user accounts. So, although we can chroot to /usr/local/, we still need to let user acounts to be able to delete files.


Approach:
1. install the latest openssh 5.X
2. use chroot feature with internal-sftp, user can only use sftp, not shell, and chroot to /usr/local after that since /usr/local is owned by root.
3. use acl feature to set acl to be able to write for folder /usr/local/tomcat/files, but not able to list files under /usr/local/tomcat

Steps:
1. download and install openssh 5.x  (./configure;make;make install to /usr/local/)

or you can check this page to install compiled RPM:

http://linuxadminzone.com/quickly-upgrade-ssh-openssh-in-centos-linux-to-latest-5-5-version/


2. configure /usr/local/etc/sshd_config as follows:
Subsystem    sftp    internal-sftp   [-u 0002]
Match group chrooted
         ChrootDirectory /usr/local/
         X11Forwarding no
         AllowTcpForwarding no
         ForceCommand internal-sftp [-u 0002]


Note: -u 0002 to specify umask for user after login, you can give 
/usr/local as root:sftp -R and 755 -R
/usr/local/XXXX as apache:sftp -R  and chmod 775 -R and chgrp g+ws XXXX -R

so that 2 users can share the work on the project.

Note: if you want to setup a chrooted sftp only environment for all users belongs to sftponly group. You  can do this:


Match   Group sftponly                                                                                                       
        ChrootDirectory %h                                                                                                   
        ForceCommand internal-sftp                                                                                          
 
or

Match   Group sftponly                                                                                                       
        ChrootDirectory /sftponly                                                                                                
        ForceCommand internal-sftp                                                                                           
 
note: You can give root:sftponly permission to /sftponly directory, if you'd like
to restrict users from writing to that folder, don't give write permission for group.
or create a subfolder under /sftponly then give write permission to that subfolder only, instead of parent folder. 
 
3.create user account jephe:
useradd jephe 
groupadd chrooted
usermod jephe -G chrooted

4. mount the partition which the folder /usr/local/ belongs to with acl options


5. set acl for user jephe
setfacl -m u:jephe:x /usr/local/tomcat/
setfacl -m u:jephe:rwx /usr/local/tomcat/files

Note: so, now user jephe is able to delete any files under /usr/local/tomcat/files, but cannot list any other files under /usr/local/tomcat directory. User can configure winscp to direct switch to /tomcat/files(/usr/local becomes / after chroot for user)

IBM db2 lock wait analysis

Objective: When db2 lock wait happens anytime,  get the name of locked tables and save the statements
Environment: CentOS 5 and DB2 V9.1


Scripts and cronjobs:

# check lock wait process
*/5 * * * * /db2log/scripts/checklockwait.sh > /dev/null 2>&1

# more /db2log/scripts/checklockwait.sh
#!/bin/sh
DATE=`date +%Y-%m-%d`
export PATH=/db2/db2inst1/sqllib/bin:/usr/bin:/bin
. /db2/db2inst1/sqllib/db2profile

rm -f /tmp/lock-wait /tmp/lockid /tmp/lockdetails /tmp/lockemail /tmp/statements.txt /tmp/statements.zip
sync
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
sleep 15
db2 list application show detail | grep -i lock-wait > /tmp/lock-wait
note: if the lock wait remains after 15 seconds, consider it as real lock wait as sometimes the application will hold on tables for a while.

if [ -s /tmp/lock-wait ];then
 cat /tmp/lock-wait | awk '{print $3}' > /tmp/lockid
 while read line
     do
      db2 get snapshot for locks for application agentid $line >> /tmp/lockdetails
      DBNAME=`db2 list application | awk  -v APPHL="$line" '$3==APPHL {print $5}'`
      db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications  >> /tmp/lockdetails
      db2pd -db $DBNAME -locks wait showlocks -transactions -agents -applications -dynamic -repeat 20 1 > /tmp/statements.txt
     done < /tmp/lockid

 cat /tmp/lock-wait /tmp/lockdetails > /tmp/lockemail
 zip /tmp/statements.zip /tmp/statements.txt
 mutt -s "Lock-wait found on database server,please take action" -a /tmp/statements.zip jephe.wu@domain.com < /tmp/lockemail
fi



Notes:
1. found out while tablespace and tables according to their IDs
SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE TBSPACEID = 2 AND TABLEID = 6


Reference:
1. Analyzing lockwait situations in DB2 for Linux, UNIX, and Windows -
http://www.ibm.com/developerworks/data/library/techarticle/dm-0707fechner/index.html
note: please refer to above URL to get the exact statements when lock wait happens. Which statement is blocking.

How to configure and make sure ntpd client is working

Jephe Wu - http://linuxtechres.blogspot.com 

Objective: make sure the NTP client is syncing with the NTP server
Environment:  CentOS 5


Steps:

1. Refer to http://www.pool.ntp.org/en/use.html, configure NTP client as follows:


driftfile /var/lib/ntp/ntp.drift

server 0.pool.ntp.org
server 1.pool.ntp.org
server 2.pool.ntp.org
server 3.pool.ntp.org
 
For even better result, you can use your continental zones as follows (assuming your
your server is staying in Asia)
 
driftfile /var/lib/ntp/ntp.drift  
server 0.asia.pool.ntp.org
server 1.asia.pool.ntp.org
server 2.asia.pool.ntp.org
server 3.asia.pool.ntp.org
  
 
2. manually sync clock once (use ntpdate or just date command manually set your clock)
ntpdate -b pool.ntp.org 
or
date 090417252010
 
note: you can also use pool.ntp.org as windows internet time server

3. start up ntpd
chkconfig ntpd on
service ntpd restart

4. checking NTP is working (as normal user or root )
a. /usr/sbin/ntpq -pn  (If you see asterisk is not on localhost clock, it's working. Take note of that this will take up to 15 minutes after ntpd starts up, because it will make sure the local clock is stable first before syncing with the server)

the delay and offset columns should not be zero for the asterisk line

b. ntpstat