Sign inTry Free

Authenticate with PAM using Active Directory

On this page

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.

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

    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

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

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

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

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

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

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

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

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

  1. 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 SingleStore

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

  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.

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

  3. Change the permissions on the file.

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

  4. Update the memsql.cnf file.

    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.

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

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

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

  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.

    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.

    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

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

In this section

Last modified: April 26, 2023

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK