Authenticate with PAM using Active Directory

Overview

This guide demonstrates how to authenticate to SingleStore 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

1. On the Windows command line, create the AD user with the following commands.

   Shell

   Copy

   New-ADUser -Name "memsql" -UserPrincipalName HTTP/memsql.s2.local@S2.LOCAL -PasswordNeverExpires $true

   Shell

   Copy

   Set-ADAccountPassword memsql -NewPassword $password

   Shell

   Copy

   Set-ADAccountControl memsql -Enabled $true

   The following is the Lightweight Directory Access Protocol (LDAP) Data Interchange Format (LDIF) output from these commands.

   Shell

   Copy

   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 SingleStore 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 use ktutil to generate the keytab file, this attribute will not be generated, and you will need to create it using the following command.

   Shell

   Copy

   setspn -S HTTP/memsql.s2.local S2\memsql

Configure the DNS

ping S2.LOCAL

ping: S2.LOCAL: Temporary failure in name resolution

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

1. On each host, update the /etc/resolv.conf file.

   Copy

   nameserver <ipAddressOfDomainController>
   options edns0 trust-ad
   search <fully.Qualified.Domain.Name>

2. On each host, update the /etc/krb5.conf file.

   Copy

   [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

Add Kerberos Support to SingleStore

1. Test the connection to the KDC server by issuing a ticket to the service user on the aggregators.

   Shell

   Copy

   kinit -kt memsql.keytab -S HTTP/memsql.s2.local HTTP/memsql.s2.local@S2.LOCAL

   Shell

   Copy

   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

2. 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.

   Shell

   Copy

   sudo cp <filename>.keytab /etc/memsql/

3. Change the permissions on the file.

   Shell

   Copy

   sudo chown memsql:memsql /etc/memsql/<filename>.keytab

4. Update the memsql.cnf file.

   Shell

   Copy

   gssapi-keytab-path    = /etc/memsql/<filename>.keytab
   gssapi-principal-name = HTTP/memsql.s2.local@S2.LOCAL

5. Restart each aggregator. The following command will restart all nodes.

   Shell

   Copy

   sdb-admin restart-node --all --yes

6. Log into the Master Aggregator node using the SingleStore client.

   Shell

   Copy

   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>

7. Add the Kerberos GSS-API authentication method to the target user.

   Shell

   Copy

   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.

8. In later Toolbox versions, the plugin is already present on the server at the following location: /usr/lib/singlestore-client/plugin/.

9. Connect to SingleStore using the Kerberos credentials.

   Shell

   Copy

   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.

1. Obtain a Kerberos ticket for the user that is accessing the cluster.

   Shell

   Copy

   kinit user1@S2.LOCAL

   Password for user1@S2.LOCAL:

   Shell

   Copy

   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

2. Connect to the cluster using the Kerberos ticket and credentials.

   Shell

   Copy

   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>