Using Tomcat Realm to perform Active Directory Authentication/Authorization

Introduction

I have been asked to implement an authentication and authorization mechanism to an existing legacy application built using Software AG Apama.

The best option in this case would be to implement a custom Login Module for Apama but this was not an option due to many non-tech reasons.

Workaround

Apama generates a .war file during build process and it is executed under Apache Tomcat.

So I have decided to use Tomcat to provide this security mechanism.

Implementation

To do so I have changed my server.xml and included the following config.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<Realm className="org.apache.catalina.realm.LockOutRealm">
<Realm className="org.apache.catalina.realm.CombinedRealm">
<!-- This Realm uses the UserDatabase configured in the global JNDI
resources under the key "UserDatabase". Any edits
that are performed against this UserDatabase are immediately
available for use by the Realm. -->
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>
<Realm className="org.apache.catalina.realm.JNDIRealm"
adCompat="true"
connectionName="CN=Application Service User,OU=Crypto,OU=Example Applications Group,DC=example,DC=net"
connectionPassword="service_user_password"
connectionURL="ldap://example.net:389"
userBase="DC=example,DC=net"
userSearch="(&amp;(sAMAccountName={0})(memberOf:1.2.840.113556.1.4.1941:=CN=DEV_CRYPTO_DASHBOARD,OU=Crypto,OU=Example Applications Group,DC=example,DC=net))"
userSubtree="true"
roleBase="DC=example,DC=net"
roleName="cn"
roleNested="true"
roleSearch="(&amp;(member={0})(cn=DEV_CRYPTO_DASHBOARD))"
roleSubtree="true"
/>
</Realm>
</Realm>

Caveats

Role validation

JNDIRealm expects to do the authentication process and load roles from LDAP. The application itself should validate access based on roles granted for the authenticated user.

Since changing legacy application was not an option we are validating user membership at user search.

Nested groups

In Active Directory it is possible to add another group as a member of a group to improve directory management.

To validate user membership in this case we must use LDAP_MATCHING_RULE_IN_CHAIN custom matching rule during the search process as described here.

To do so we have to change or membership (memberOf=CN=DEV_CRYPTO_DASHBOARD,OU=Crypto,OU=Example Applications Group,DC=example,DC=net) to (memberOf:1.2.840.113556.1.4.1941:=CN=DEV_CRYPTO_DASHBOARD,OU=Crypto,OU=Example Applications Group,DC=example,DC=net).

PostgreSQL authentication over pam and sssd with Active Directory

How to setup a postgresql to authenticate on Active Directory domain using pam and sssd.

Configure PAM

Create a service configuration in PAM for PostgreSQL at /etc/pam.d/postgresql

1
2
3
4
5
6
7
8
9
10
11
12
13
# PAM configuration for the PostgreSQL
# Standard Un*x authentication.
@include common-auth
# Standard Un*x authorization.
@include common-account
# Standard Un*x session setup and teardown.
@include common-session
# Standard Un*x password updating.
@include common-password

Configure SSSD

Edit your sssd.conf and add a GPO mapping for PostgreSQL

1
ad_gpo_map_remote_interactive = +postgresql

Final version of /etc/sssd/sssd.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[sssd]
domains = example.com
config_file_version = 2
services = nss, pam
default_domain_suffix = example.com
[domain/example.com]
default_shell = /bin/bash
krb5_store_password_if_offline = True
cache_credentials = True
krb5_realm = EXAMPLE.COM
realmd_tags = manages-system joined-with-adcli
id_provider = ad
fallback_homedir = /home/%u
ad_domain = example.com
# use_fully_qualified_names = True
ldap_id_mapping = True
access_provider = ad
full_name_format = %1$s
ad_gpo_map_remote_interactive = +postgresql

Restart your sssd.

1
sudo systemctl restart sssd

Configure PostgreSQL

pg_hba.conf

First configure your /etc/postgresql/12/main/pg_hba.conf to use pam.

1
2
3
4
5
6
7
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/16 pam pamservice=postgresql

Create a roles

For each user that is going to login on postgresql create a role and assign permissions.

1
CREATE ROLE "jrgcombr" SUPERUSER NOCREATEDB CREATEROLE INHERIT LOGIN;

Running Samba shares on Ubuntu 20.04 LTS (Focal Fossa)

I few days ago I was helping a client to move some systems to run under Linux. They are quite used to Windows environments and they would like to have an environment where they could have a workflow similar to the one they have using Windows servers.

Environment

  • Windows 2016 Domain Controller
  • Windows 10 Workstations
  • Ubuntu 20.04 LTS (Focal Fossa) Application Server
  • Sudoers must be granted via Active Directory group

Requirements

  • Log to Linux servers using Active Directory account
  • Ability to copy files from Windows workstations to Linux servers using Windows Explorer
  • Ability to use ACLs on Linux in similar way to how they are done in Windows

Solution

SSSD Configuration

Hostname & DNS

Set a proper hostname for your server with correct domain component.

1
sudo hostnamectl set-hostname myubuntu.example.com

Disable systemd-resolve

Ubuntu 20.04 comes with systemd-resolve which you need to disable for the server to access your network DNS directly.

1
2
3
4
sudo systemctl disable systemd-resolved
sudo systemctl stop systemd-resolved
sudo unlink /etc/resolv.conf
sudo vim /etc/resolv.conf

Install required packages

A number of packages are required for joining an Ubuntu 20.04 system to Active Directory (AD) domain.

1
2
sudo apt update
sudo apt -y install realmd libnss-sss libpam-sss sssd sssd-tools adcli samba-common-bin oddjob oddjob-mkhomedir packagekit

Discover Active Directory domain

The realm discover command returns complete domain configuration and a list of packages that must be installed for the system to be enrolled in the domain.

1
sudo realm discover example.com

1
2
3
4
5
6
7
8
9
10
11
12
13
example.com
type: kerberos
realm-name: EXAMPLE.COM
domain-name: example.com
configured: no
server-software: active-directory
client-software: sssd
required-package: sssd-tools
required-package: sssd
required-package: libnss-sss
required-package: libpam-sss
required-package: adcli
required-package: samba-common-bin

Ensure that all listed packages are also installed.

Join Active Directory (AD) domain

An AD administrative user account is required for integrating your Linux machine with Windows Active Directory domain. Check and confirm AD admin account and the password.

The realm join command will set up the local machine for use with a specified domain by configuring both the local system services and the entries in the identity domain. The command has a number of options which can be checked with:

1
sudo realm join -U Administrator example.com

Test your new configuration.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
realm list
example.com
type: kerberos
realm-name: EXAMPLE.COM
domain-name: example.com
configured: kerberos-member
server-software: active-directory
client-software: sssd
required-package: sssd-tools
required-package: sssd
required-package: libnss-sss
required-package: libpam-sss
required-package: adcli
required-package: samba-common-bin
login-formats: %U@example.com
login-policy: allow-realm-logins

Edit /usr/share/pam-configs/mkhomedir and set Default: yes to get it enabled.

1
2
3
4
5
6
7
Name: Create home directory on login
Default: yes
Priority: 0
Session-Type: Additional
Session-Interactive-Only: yes
Session:
optional pam_mkhomedir.so

Activate your configuration.

1
sudo pam-auth-update

Ensure “activate mkhomedir” is selected, it should have [*]

Tune your setup

In my scenario I’ve decided to tune some things because I’m dealing with just one domain.

  • full_name_format = %1$s to show just username omitting domain name
  • use_fully_qualified_names = True removed to omit domain name
  • fallback_homedir = /home/%u to create homedirs with just username
  • default_domain_suffix = example.com to have a default domain since we are omitting it

Final version of /etc/sssd/sssd.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[sssd]
domains = example.com
config_file_version = 2
services = nss, pam
default_domain_suffix = example.com
[domain/example.com]
default_shell = /bin/bash
krb5_store_password_if_offline = True
cache_credentials = True
krb5_realm = EXAMPLE.COM
realmd_tags = manages-system joined-with-adcli
id_provider = ad
fallback_homedir = /home/%u
ad_domain = example.com
# use_fully_qualified_names = True
ldap_id_mapping = True
access_provider = ad
full_name_format = %1$s

Whenever there is a change in sssd.conf, restart is required.

1
sudo systemctl restart sssd

Test it

Status should be running.

1
systemctl status sssd

If the integration is working, it should be possible to get an AD user info.

1
2
id jrgcombr
uid=1783929917(jrgcombr) gid=1784800513(domain users) groups=1783870513(domain users),435833711(example_group),435833701(linux_sudoers)

Configure sudoers

Adjust your visudo to linux_sudoers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults env_reset
Defaults mail_badpass
Defaults secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin"
# Host alias specification
# User alias specification
# Cmnd alias specification
# User privilege specification
root ALL=(ALL:ALL) ALL
# Members of the admin group may gain root privileges
%admin ALL=(ALL) ALL
%linux_sudoers ALL=(ALL) ALL
# Allow members of group sudo to execute any command
%sudo ALL=(ALL:ALL) ALL
# See sudoers(5) for more information on "#include" directives:
#includedir /etc/sudoers.d

Samba Configuration

Install packages

1
sudo apt install samba

Adjust your smb.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
[global]
workgroup = EXAMPLE
realm = EXAMPLE.NET
security = ads
kerberos method = secrets and keytab
server string = %h server (Samba, Ubuntu)
log file = /var/log/samba/log.%m
max log size = 1000
logging = file
panic action = /usr/share/samba/panic-action %d
obey pam restrictions = yes
unix password sync = yes
passwd program = /usr/bin/passwd %u
passwd chat = *Enter\snew\s*\spassword:* %n\n *Retype\snew\s*\spassword:* %n\n *password\supdated\ssuccessfully* .
pam password change = yes
map to guest = bad user
idmap config * : backend = tdb
idmap config * : range = 100000-199999
idmap config EXAMPLE: backend = sss
idmap config EXAMPLE: range = 200000-2147483647
usershare allow guests = yes
[example_share]
path = /var/example_share
read only = no
browsable = true
writable = yes
inherit permissions = yes
valid users = @example_group
map archive = no

Fix guests mapping on Ububtu

Ubuntu default installation has an issue with S-1-5-32-546 mapping.

1
sudo net groupmap add sid=S-1-5-32-546 unixgroup=nogroup type=builtin

Well-known security identifiers in Windows operating systems

Install acl

Ubuntu does not come with acl packages installed but the filesystem does come with acl enabled by default.

1
sudo apt install acl

Configure share permissions

Adjust permission to only example_group members can work on shared files from samba share and from Linux as well.

1
2
3
chmod 2770 /var/example_share
chown root:example_group /var/example_share
setfacl -m g:example_group:rwx,d:g:example_group:rwx /var/example_share

Check it

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
getfacl /var/example_share
# file: .
# owner: root
# group: example_group
# flags: -s-
user::rwx
group::rwx
group:example_group:rwx
mask::rwx
other::---
default:user::rwx
default:group::rwx
default:group:example_group:rwx
default:mask::rwx
default:other::---

Enable samba

1
2
sudo systemctl enable smbd
sudo systemctl start smbd

Conclusion

Setup is now done, by now you should have one samba active directory integrated, clients authenticating via Kerberos and one flat uid/gid structre.

SQLCLR - System.IO.FileLoadException: Could not load file or assembly

Today I was working with Caroline (Carol) Lavecchia Pereira on a issue with SQLCLR.

Even the error message being very clear of what was going on, I wasn’t expecting this behavior since it is not usual to mess around with system DLLs registrations.

Symptoms

After a server migration some CLR routines stopped working on the new server showing the following error.

1
2
3
4
5
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "MyUserRoutine":
System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at MyNamespace.MyUserRoutine(String param0, String param1, String param2, String param3, String param4, String param5, String param6, String param7, String param8, String param9)

Diagnosing

Checking DLL load

At first I was expecting this error had something to do with the user DLL, so I decided to get it loaded outside SQLCLR just to check if it was ok.

1
PS C:\temp> [System.Reflection.Assembly]::LoadFile("C:\temp\MyDLL.dll")

The DLL loaded without any errors, so I was back do SQLCLR.

Checking KB

The error message itself pointed to this KB asking you to use ALTER ASSEMBLY to get it fixed.

I think this KB could have provided some examples of the ALTER ASSEMBLY statement and this KB is also missing some DLLs like System.ServiceModel, SMDiagnostics and System.IdentityModel.

Fix

1
2
3
4
5
6
7
8
9
10
11
ALTER ASSEMBLY [System.ServiceModel]
FROM 'C:\Windows\assembly\GAC_MSIL\System.ServiceModel\3.0.0.0__b77a5c561934e089\System.ServiceModel.dll'
ALTER ASSEMBLY [SMDiagnostics]
FROM 'C:\Windows\assembly\GAC_MSIL\SMDiagnostics\3.0.0.0__b77a5c561934e089\SMDiagnostics.dll'
ALTER ASSEMBLY [System.IdentityModel]
FROM 'C:\Windows\assembly\GAC_MSIL\System.IdentityModel\3.0.0.0__b77a5c561934e089\System.IdentityModel.dll'
ALTER ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\Windows\assembly\GAC_MSIL\System.Runtime.Serialization\3.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll'

Births and moon phase

Introduction

A few days ago I was talking to a friend of mine about how some days are busier at hospitals than others. I’ve been told that when we are at a full moon hospitals receive more woman to give birth.

I got puzzled by that statement and decided to check on it.

Getting some data

Births

I was worried that it would be hard to get some info on births, since any medical relevant data are usually very expensive. At first, I tried to get it from IBGE (Brazilian entity responsible for census). No luck.

Then I decided that birth location should not be relevant since the moon would affect entire earth if it was the case. Searching on internet I found a dataset from SSA (EUA Social Security Administration) with birth amount by date from 2000 to 2014.

Birth data

Moon phase

How moon phase works

First I had to look how Lunar phase works, before reading about it I thought the place you were on Earth would affect what phase of the moon you would see. Wrong. Regardless where you are (Brazil, Canada, Japan, etc) you are going to see the same moon phase.

I was expecting to find a moon phase table easy over the web but I was wrong. I found some sites that does the math for you and give you the results but I was not in the mood to write a scraper for that.
After some research I found an old Python code that does the math, but it was written in Python 2. I was expecting to be easy to convert it to Python 3 but part of math was related to Julian days and datetime library changed since Python 2.

Then, I hacked it to use jdcal and generated a table with moon phase from 2000 to 2014.

Moon data

Joining data

Since datasets are not that big I joined then on Excel using VLOOKUP. I’ve also added some extra columns like weekday, Excel date, day type (working day, weekend).

Full dataset

Analysis

Correlation

So I got all data needed to answer the question: Is moon cycle correlated to amount of births?

Chart

Just to get a glimpse on how the data plots. Curious pattern.

Births vs % illuminated

Correlation results

Looks like there is no correlation on moon cycle and birth amount.

Births vs % illuminated correlation

Means

What if only a full moon affect births? Well, lets compare means.

Means

Hypothesis test

Null hypothesis: There is no difference between average of births in full moon and average of births in other phase of the moon.

t-test

P-Value greater than 0.05, so accept null hypothesis. There no statistical difference between those means.

Biphasic Chart

I got puzzled by the biphasic pattern of the plot. I started to wonder if something else was affecting births or if there were some seasonality on the series.

Births vs % illuminated

Let’s plot data on time series.

births by date

As expected, too much data to see something. Scattering it would be better.

births by date scattered

Way better. Also biphasic, something is going on. Next post I’m going to try to figure it out.

Conclusion

There is no significant evidence that full moons affect births, although looks like there is some pattern on this data.

References

https://en.wikipedia.org/wiki/Lunar_phase
http://bazaar.launchpad.net/~keturn/py-moon-phase/trunk/annotate/head:/moon.py
https://en.wikipedia.org/wiki/Julian_day
https://oneau.wordpress.com/2011/08/30/jdcal/
https://github.com/phn/jdcal
https://github.com/fivethirtyeight/data/tree/master/births