SingleStoreDB v8.1

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

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>

In this section:

Authenticate with PAM using Active Directory

Overview

Create the Active Directory User

Note

Configure the DNS

Note

Create the keytab File

Linux

Note

Note

Windows

Add Kerberos Support to SingleStoreDB

Note

Troubleshooting

Search results