So I tired to make MariaDB to work with SSSD to I can use AD user account to access the MariaDB and I found many many web sites with questions not answers.
I spent 3 days chasing down this problem and found the easiest solution.
So here we go,
I used a Oracle Linux 8, and install SSSD and MariaDB, and setup SSSD and PAM to work with it.
Your Linux server name should be <servername>.<your domain name> not <servername>.localhost
eg: MaridDBserver.domain.com
I assume you already installed the MariaDB.
Install SSSD (Required)
#dnf install sssd realmd
oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation
openldap-clients policycoreutils-python-utils -y
#realm discover
<yourdomain name>
eg: #realm discover domain.com
# realm join
--user=<your domain user account name> <domain name>
eg: realm join --user= john domain.com
Edit /etc/sssd/sssd.conf (optional)
Changed
use_fully_qualified_names
= True
fallback_homedir =
/home/%u@%d
to
use_fully_qualified_names
= False
fallback_homedir =
/home/%u
restart sssd
# systemctl restart SSSD
Install chronyd (required to match time with AD, change settings if you have local npt service)
#dnf -y install chrony
Check chronyd status
#systemctl restart
chronyd
#chronyc sourcestats
Check status of SSSD
# systemctl status sssd
If you see any error fix it before you move on.
Install PAM for MariaDB (required)
Login to MariaDB as root and install PAM plugin
> mysql -u root -p
> Install soname 'auth_pam'
> > show plugins soname like '%pam%';
+------+--------+----------------+-------------+---------+
| Name | Status | Type | Library | License |
+------+--------+----------------+-------------+---------+
| pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
+------+--------+----------------+-------------+---------+
1 row in set (0.001 sec)
> exit
Edit SSSD.conf (required)
# vi /etc/sssd/sssd.conf
This is my setting, change yours accordingly.
----------------------------------------------
[sssd]
domains = domain.com #<- change this
config_file_version = 2
services = nss, pam
debug_level=9 #<-- comment out once SSSD with mariaDB works
[domain/domain.com]
ad_domain = domain.com #<- change this
krb5_realm = domain.com #<- change this
realmd_tags = manages-system joined-with-adcli
cache_credentials = True
id_provider = ad
krb5_store_password_if_offline = True
default_shell = /bin/bash
ldap_id_mapping = True
use_fully_qualified_names = False
fallback_homedir = /home/%u
#access_provider = ad #<- we will be using simple access provider not AD, comment it out.
access_provider=simple #<- add this
#access_provider=permit #<- this is for debugging purpose, google it if you are curious.
simple_allow_groups=mariadbusers # <- this is an AD security group, we will create later, name it to something you like.
[pam]
debug_level=9 #<-- comment out once SSSD with mariaDB works
[nss]
debug_level=9 #<-- comment out once SSSD with mariaDB works
[mariadb]
debug_level=9 #<-- comment out once SSSD with mariaDB works
----------------------------------------------
Restart SSSD
# systemctl restart sssd
Create mariadb and mysql files, it is called "PAM service name" or "authentication_string" by mariaDB
# cd /etc/pam.d
# vi mariadb
and add two line below
auth required pam_sss.so domains=<your domain name>
account required pam_sss.so domains=<your domain name>
eg:
auth required pam_sss.so domains=domain.com
account required pam_sss.so domains=domain.com
Copy the mariadb file.
# cp mariadb mysql
Ok you are almost there
Login to mariaDB and create new users.
> login -u root -p
There are 2 ways to create a user.
> create user '<username>'@'%' identified via pam using 'mariadb';
OR
> create user '<username>'@'%' identified via pam;
The <username> is a domain user name.
eg: if you have a AD user domain\john
> create user 'john'@'%' identified via pam;
OR
> create user 'john'@'%' identified via pam using 'mariadb';
Commit the change
> flush privileges;
Query OK, 0 rows affected (0.002 sec)
Note: % denotes allow from all IPs, change it accordingly to meet your security requirements.
If you add using 'mariadb', it will use the auth string /etc/pam.d/mariadb to initiate the auth, the auth file will tell the mariDB to use pam_sss.so
If you do not add using 'mariadb', it will use the auth string /etc/pam.d/mysql to initiate the auth, the auth file will tell the mariDB to use pam_sss.so
ref: https://mariadb.com/kb/en/authentication-plugin-pam/#creating-users
-------------------------------------------------------------------------------------------------
This is from the article:
You can also specify a PAM service name for MariaDB to use by providing it with the USING clause. For example:
CREATE USER username@hostname IDENTIFIED VIA pam USING 'mariadb';
This line creates a user that needs to be authenticated via the pam authentication plugin using the PAM service name mariadb. As mentioned in a previous section, this service's configuration file will typically be present in /etc/pam.d/mariadb.
If no service name is specified, then the plugin will use mysql as the default PAM service name.
-------------------------------------------------------------------------------------------------
Verify user creation
In this example I created john with mariadb auth string and sam without auth string. sam will use mysql auth string (aka PAM service name).
MariaDB [(none)]> select host,user,plugin,authentication_string from mysql.user;
+---------------------------+-------+--------+-----------------------+
| host | user | plugin | authentication_string |
+---------------------------+-------+--------+-----------------------+
| localhost | root | | |
| % | john | pam | mariadb |
| % | sam | pam | |
+---------------------------+-------+--------+-----------------------+
7 rows in set (0.001 sec)
We have both /etc/pam.d/mariadb and /etc/pam.d/mysql, just incase ! OK?
Now create a AD security group "mariadbusers".
You remember "simple_allow_groups=mariadbusers # <- this is an AD security group, name it as you like." on sssd.conf?
Yes, use the same name, and add users on it.
In this example we should add "domain\john" and "domain\sam"
You must add all AD users who need to login to the server plus mariadb users. It will control server access as well. If your name is not member of this group you won't be able to putty to the server.
Linux Local users such as root is not subject to this group, we can't add linux users to Ad group any ways right? lol
How this work?
When john@domain.com try to login to the server, SSSD will auth and allow john to access the server if john is member of the security group.
Once john logged into the server and try to login to mariaDB, it will use auth string '/etc/pam.d/mariadb' to auth and check if the user is allowed to login using SSSD.conf's access_provider, in this case the Ad group "mariddbusers'
So what happens when you created user without auth string and you don't have /etc/pam.d/mysql? but the user is member of the security group "mariadbusers"?
The user will be able to login to the linux server but not able to login to mariadb, you will get "permission denied" error.
Have fun!
