# 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](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/cluster-management-with-tools/singlestore-tools-installation/singlestore-client-installation.md), `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 New-ADUser -Name "memsql" -UserPrincipalName HTTP/memsql.s2.local@S2.LOCAL -PasswordNeverExpires $true ``` ```shell Set-ADAccountPassword memsql -NewPassword $password ``` ```shell Set-ADAccountControl memsql -Enabled $true ``` The following is the Lightweight Directory Access Protocol (LDAP) Data Interchange Format (LDIF) output from these commands. ```shell 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 > 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:```shell > ping S2.LOCAL > > ``````output > > ping: S2.LOCAL: Temporary failure in name resolution > ```If the host can be resolved, skip this step.```shell > ping S2.LOCAL > > ``````output > > 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 options edns0 trust-ad search ``` 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. ```shell kinit HTTP/memsql.s2.local@S2.LOCAL ``` ```output Password for HTTP/memsql.s2.local@S2.LOCAL: ``` ```shell klist ``` ```output 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. ```shell kvno HTTP/memsql.s2.local@S2.LOCAL ``` ```output 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](https://learn.microsoft.com/en-us/windows/win32/ad/service-principal-names) for more information. 3. Create the keytab file using `ktutil` by adding the KVN (returned from the previous command) to the `-k` parameter. ```shell ktutil ``` ```output ktutil: ``` ```shell ktutil: addent -password -p HTTP/memsql.s2.local@S2.LOCAL -k 13 -e RC4-HMAC ``` ```output Password for HTTP/memsql.s2.local@S2.LOCAL: ``` ```shell ktutil: wkt /tmp/memsql.keytab ``` ```shell ktutil: q ``` 4. Confirm that the keytab file was created properly. ```shell klist -kt /tmp/memsql.keytab ``` ```output 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: ```shell kdestroy ``` ```shell kinit -kt /tmp/memsql.keytab -S HTTP/memsql.s2.local HTTP/memsql.s2.local@S2.LOCAL ``` ```shell klist ``` ```output 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`. ```shell 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 ``` ```output 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. ```shell 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 ``` ```output 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. ```shell kinit -kt memsql.keytab -S HTTP/memsql.s2.local HTTP/memsql.s2.local@S2.LOCAL ``` ```shell klist ``` ```output 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 sudo cp .keytab /etc/memsql/ ``` 3. Change the permissions on the file. ```shell sudo chown memsql:memsql /etc/memsql/.keytab ``` 4. Update the `memsql.cnf` file. ```shell gssapi-keytab-path = /etc/memsql/.keytab gssapi-principal-name = HTTP/memsql.s2.local@S2.LOCAL ``` 5. Restart each aggregator. The following command will restart all nodes. ```shell sdb-admin restart-node --all --yes ``` 6. Log in to the Master Aggregator node using the SingleStore client. ```shell singlestore ``` ```output 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 GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS 'user1@S2.LOCAL'; ``` ```output 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](https://docs.singlestore.com/db/v9.1/security/administration/synchronizing-permissions-across-your-cluster.md). 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 singlestore -h10.2.198.9 --plugin-dir=/usr/lib/singlestore-client/plugin/ -uuser1 ``` ```output 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 kinit user1@S2.LOCAL ``` ```output Password for user1@S2.LOCAL: ``` ```shell klist ``` ```output 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 singlestore -h10.2.198.9 --plugin-dir=/usr/lib/singlestore-client/plugin/ -uuser1 ``` ```output 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 * [Add a New Client Host](https://docs.singlestore.com/db/v9.1/security/authentication/authenticate-with-pam-using-active-directory/add-a-new-client-host.md) *** Modified at: May 14, 2026 Source: [/db/v9.1/security/authentication/authenticate-with-pam-using-active-directory/](https://docs.singlestore.com/db/v9.1/security/authentication/authenticate-with-pam-using-active-directory/) (An index of the documentation is available at /llms.txt)