Authenticate with PAM using Active Directory
Overview
This guide demonstrates how to authenticate to SingleStoreDB using pluggable authentication module (PAM) and Active Directory (AD).
In the following example:
The database username is
memsql
The default domain and realm are
S2.LOCAL
The Key Distribution Center (KDC) server is on
10.1.0.5
The Kerberos admin server is on
10.1.0.5
The SingleStore client,
singlestore
, is the default SQL client
Create the Active Directory User
On the Windows command line, create the AD user with the following commands.
New-ADUser -Name "memsql" -UserPrincipalName HTTP/memsql.s2.local@S2.LOCAL -PasswordNeverExpires $true
Set-ADAccountPassword memsql -NewPassword $password
Set-ADAccountControl memsql -Enabled $true
The following is the Lightweight Directory Access Protocol (LDAP) Data Interchange Format (LDIF) output from these commands.
dn: CN=memsql,CN=Users,DC=s2,DC=local objectClass: top objectClass: person objectClass: organizationalPerson objectClass: user cn: memsql distinguishedName: CN=memsql,CN=Users,DC=s2,DC=local instanceType: 4 whenCreated: 20220927144419.0Z whenChanged: 20220927144437.0Z uSNCreated: 16445 uSNChanged: 16449 name: memsql objectGUID:: LeVUCRAlL0S8xX5ws/PYKw== userAccountControl: 66048 badPwdCount: 0 codePage: 0 countryCode: 0 badPasswordTime: 0 lastLogoff: 0 lastLogon: 0 pwdLastSet: 133087634643301193 primaryGroupID: 513 objectSid:: AQUAAAAAAAUVAAAANs/swr0GCrmxbKG2XQQAAA== accountExpires: 9223372036854775807 logonCount: 0 sAMAccountName: memsql sAMAccountType: 805306368 userPrincipalName: HTTP/memsql.s2.local@S2.LOCAL servicePrincipalName: HTTP/memsql.s2.local objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=s2,DC=local dSCorePropagationData: 16010101000000.0Z
Note
The account needs to have the
servicePrincipalName
filled in with a proper value. For use with SingleStoreDB Studio, SingleStore recommends using the service as HTTP as in the following example:HTTP/name.domain.local
.In Windows, the
ktpass
utility will create the attribute automatically. If you usektutil
to generate the keytab file, this attribute will not be generated, and you will need to create it using the following command.setspn -S HTTP/memsql.s2.local S2\memsql
Configure the DNS
Note
This is only required if the cluster hosts are not configured to use the Kerberos domain DNS by default. This should only be performed if the host uses a public DNS and cannot resolve the DNS of the domain. For example, where S2.LOCAL
is your Kerberos domain:
ping S2.LOCAL **** ping: S2.LOCAL: Temporary failure in name resolution
If the host can be resolved, skip this step.
ping S2.LOCAL **** PING S2.LOCAL (10.1.0.5) 56(84) bytes of data. 64 bytes from ec2amaz-f5rt8fs.s2.local (10.1.0.5): icmp_seq=1 ttl=128 time=0.328 ms
On each host, update the
/etc/resolv.conf
file.nameserver <ipAddressOfDomainController> options edns0 trust-ad search <fully.Qualified.Domain.Name>
On each host, update the
/etc/krb5.conf
file.[libdefaults] default_realm = S2.LOCAL kdc_timesync = 1 ccache_type = 4 forwardable = true proxiable = true fcc-mit-ticketflags = true [realms] S2.LOCAL = { kdc = 10.1.0.5 admin_server = 10.1.0.5 default_domain = s2.local }
While this is only an example, note that the
[realms]
section contains both the domain and the address for the KDC server.
Create the keytab File
Linux
Note
While a host does not need to be connected to the domain, a Kerberos ticket is required to use these commands.
Generate a Kerberos ticket to authenticate against Active Directory.
kinit HTTP/memsql.s2.local@S2.LOCAL **** Password for HTTP/memsql.s2.local@S2.LOCAL:
klist **** Ticket cache: FILE:/tmp/krb5cc_1000 Default principal: HTTP/memsql.s2.local@S2.LOCAL Valid starting Expires Service principal 09/28/22 11:27:31 09/28/22 21:27:31 krbtgt/S2.LOCAL@S2.LOCAL renew until 09/29/22 11:27:27
Obtain the current Key Version Number (KVN) for the target user in AD.
kvno HTTP/memsql.s2.local@S2.LOCAL **** HTTP/memsql.s2.local@S2.LOCAL: kvno = 13
Note
If you receive the following error, the account does not have the
servicePrincipalName
configured properly.kvno: Server not found in Kerberos database while getting credentials for HTTP/memsql.s2.local@S2.LOCAL
Refer to Service Principal Names - Win32 apps for more information.
Create the keytab file using
ktutil
by adding the KVN (returned from the previous command) to the-k
parameter.ktutil **** ktutil:
ktutil: addent -password -p HTTP/memsql.s2.local@S2.LOCAL -k 13 -e RC4-HMAC **** Password for HTTP/memsql.s2.local@S2.LOCAL:
ktutil: wkt /tmp/memsql.keytab
ktutil: q
Confirm that the keytab file was created properly.
klist -kt /tmp/memsql.keytab **** Keytab name: FILE:/tmp/memsql.keytab KVNO Timestamp Principal ---- ----------------- -------------------------------------------------------- 13 09/28/22 09:57:14 HTTP/memsql.s2.local@S2.LOCAL
Create a Kerberos ticket using the keytab file:
kdestroy
kinit -kt /tmp/memsql.keytab -S HTTP/memsql.s2.local HTTP/memsql.s2.local@S2.LOCAL
klist **** Ticket cache: FILE:/tmp/krb5cc_1000 Default principal: HTTP/memsql.s2.local@S2.LOCAL Valid starting Expires Service principal 09/28/22 09:58:17 09/28/22 19:58:17 HTTP/memsql.s2.local@S2.LOCAL renew until 09/29/22 09:58:17
Windows
Create the keytab file using
ktpass
.ktpass.exe /princ HTTP/memsql.s2.local@S2.LOCAL /mapuser S2\memsql /pass Pass@word1 /out memsql_s2_rc4.keytab /crypto RC4-HMAC-NT /ptype KRB5_NT_PRINCIPAL /mapop set **** Targeting domain controller: EC2AMAZ-F5RT8FS.s2.local Successfully mapped HTTP/memsql.s2.local to memsql. Password successfully set! Key created. Output keytab to memsql_s2_rc4.keytab: Keytab version: 0x502 keysize 64 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 3 etype 0x17 (RC4-HMAC) keylength 16 (0x659de2671ddd13848b8a511e97893da6)
You may also use
ktpass
to create the keytab file which contains all of the keys.ktpass.exe /princ HTTP/memsql.s2.local@S2.LOCAL /mapuser S2\memsql /pass Pass@word1 /out memsql_s2.keytab /crypto all /ptype KR B5_NT_PRINCIPAL /mapop set **** Targeting domain controller: EC2AMAZ-F5RT8FS.s2.local Successfully mapped HTTP/memsql.s2.local to memsql. Password successfully set! Key created. Key created. Key created. Key created. Key created. Output keytab to memsql_s2.keytab: Keytab version: 0x502 keysize 56 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 4 etype 0x1 (DES-CBC-CRC) keylength 8 (0x0b198326622cab85) keysize 56 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 4 etype 0x3 (DES-CBC-MD5) keylength 8 (0x0b198326622cab85) keysize 64 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 4 etype 0x17 (RC4-HMAC) keylength 16 (0x659de2671ddd13848b8a511e97893da6) keysize 80 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 4 etype 0x12 (AES256-SHA1) keylength 32 (0xb95546ad7be4c19071aeeaa1e8ad30eea63c82 d217e874e2de74ce1964ec628b) keysize 64 HTTP/memsql.s2.local@S2.LOCAL ptype 1 (KRB5_NT_PRINCIPAL) vno 4 etype 0x11 (AES128-SHA1) keylength 16 (0xcdb53c4aa15a2833710f83a32f899401)
Add Kerberos Support to SingleStoreDB
Test the connection to the KDC server by issuing a ticket to the service user on the aggregators.
kinit -kt memsql.keytab -S HTTP/memsql.s2.local HTTP/memsql.s2.local@S2.LOCAL
klist **** Ticket cache: FILE:/tmp/krb5cc_1001 Default principal: HTTP/memsql.s2.local@S2.LOCAL Valid starting Expires Service principal 09/28/22 10:25:44 09/28/22 20:25:44 HTTP/memsql.s2.local@S2.LOCAL renew until 09/29/22 10:25:44
Copy the keytab file to each aggregator to ensure that the file is the same on each host. Copy the file to the following location.
sudo cp <filename>.keytab /etc/memsql/
Change the permissions on the file.
sudo chown memsql:memsql /etc/memsql/<filename>.keytab
Update the
memsql.cnf
file.gssapi-keytab-path = /etc/memsql/<filename>.keytab gssapi-principal-name = HTTP/memsql.s2.local@S2.LOCAL
Restart each aggregator. The following command will restart all nodes.
sdb-admin restart-node --all --yes
Log into the Master Aggregator node using the SingleStore client.
singlestore **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.32 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. singlestore>
Add the Kerberos GSS-API authentication method to the target user.
GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS 'user1@S2.LOCAL'; **** Query OK, 0 rows affected, 1 warning (0.01 sec)
Note
If sync permissions are enabled (
SET GLOBAL sync_permissions = ON;
), you must login into each aggregator and manually add the user.In later Toolbox versions, the plugin is already present on the server at the following location:
/usr/lib/singlestore-client/plugin/
.Connect to SingleStoreDB using the Kerberos credentials.
singlestore -h10.2.198.9 --plugin-dir=/usr/lib/singlestore-client/plugin/ -uuser1 **** ERROR 1105 (HY000): Client GSSAPI error (major 458752, minor 0) : gss_init_sec_context - No credentials were supplied, or the credentials were unavailable or inaccessible
Troubleshooting
The command above will fail if a ticket for the specified user has not been obtained. Run the following commands to resolve this issue.
Obtain a Kerberos ticket for the user that is accessing the cluster.
kinit user1@S2.LOCAL **** Password for user1@S2.LOCAL:
klist **** Ticket cache: FILE:/tmp/krb5cc_1001 Default principal: user1@S2.LOCAL Valid starting Expires Service principal 09/27/22 15:02:43 09/28/22 01:02:43 krbtgt/S2.LOCAL@S2.LOCAL renew until 09/28/22 15:02:36
Connect to the cluster using the Kerberos ticket and credentials.
singlestore -h10.2.198.9 --plugin-dir=/usr/lib/singlestore-client/plugin/ -uuser1 **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.32 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. singlestore>