---

MemSQL supports Kerberos third-party authentication using the Generic Security Services API (GSSAPI). When a Kerberos-authenticated user attempts to connect to MemSQL, their credentials are automatically validated and they are optionally granted access to MemSQL.

This document explains how to enable, configure, and troubleshoot this feature.

Assumptions and Prerequisites

This document assumes that you are familiar with Kerberos concepts and know how to administer a realm. Depending on your Kerberos environment, some of the guidance in this document may require changes to adhere to your realm’s configuration.

Before you can start using Kerberos authentication in MemSQL, ensure that your system meets the following prerequisites:

• MemSQL Version: 5.7.4 or newer
• MariaDB Client Version: Currently, no official MySQL clients support Kerberos/GSSAPI authentication. However, specific MariaDB clients do support Kerberos, and they are compatible with MemSQL. You can use any of the following ODBC, Java/JDBC, or C++ clients to authenticate MemSQL users:
• MariaDB Command-Line Interface (CLI) version 10.1.17 or newer: MariaDB 10.1 Series (stable) contains a command-line interface that can be used to connect to MemSQL. To download MariaDB 10.1 Series, click here.
• MariaDB ODBC Connector version 3.0.0 Alpha or newer: MariaDB ODBC Connector version 2.0.13 does not support Kerberos/GSSAPI. However, it may be supported in future versions. If your organizational policies allow usage of prerelease software, Kerberos/GSSAPI support is included in version 3.0.0 Alpha. To download any version of the MariaDB ODBC Connector, click here.
• MariaDB JDBC Connector version 1.5.7 or newer: Download the MariaDB JDBC Connector here.
• MariaDB C++ Connector version 2.3.2 or newer: Download the MariaDB C++ Connector here.
• MariaDB GSSAPI Authentication Plugin: In addition to a MariaDB client or connector, you must also have the GSSAPI Authentication Plugin available on the connecting machine. The plugin is then referenced in the connection string when connecting with a client or connector. The GSSAPI plugin is packaged with the MariaDB database software itself, so you must download one of the stable or prerelease versions here. Once downloaded, the plugin is located in a path such as mariadb-<version>/plugin for Unix/Linux, or mariadb-<version>\lib\plugin for Windows. For more information about the plugin, see the MariaDB documentation.

Configuring the KDC

To authenticate users with the Kerberos tickets, MemSQL must be provided with a service principal name (SPN) stored in a keytab. The SPN is a unique identifier for MemSQL, and the keytab file contains the encrypted keys for the MemSQL SPN. Together, the SPN and keytab file serve as a credential for MemSQL that allows your cluster to automatically authenticate Kerberos users.

Your MemSQL deployment environment may affect the way your SPNs and keytab files are generated. For example, if you are using a load balancer in front of your MemSQL cluster, its SPN may need to be added to the keytab file that will be copied to each aggregator node in the cluster.

The following sections describe how to create an SPN and keytab file for KDC servers running on Unix/Linux and Windows.

Creating a Service Principal Name and Keytab File

To create a SPN for MemSQL, you must have sufficient permissions to access the KDC server’s terminal and to create new SPNs.

Unix Kerberos KDC (MIT or Heimdal)

The following steps use the kadmin command-line interface to create a SPN and keytab file on a Unix-based or Linux-based KDC server. See the kadmin documentation for more information.

To create the SPN for MemSQL, execute the following command, replacing host.domain.com with your fully-qualified domain name for the host server:

$ kadmin -q "addprinc -randkey memsql/host.domain.com"

You can verify that the SPN was created successfully by executing the following getprinc command:

$ kadmin -q "getprinc memsql/host.domain.com"

After the MemSQL SPN has been created, you can create the keytab file. Execute the following command, replacing /path/to/memsql.keytab with your desired output path, and host.domain.com with your fully-qualified domain name for the host server:

$ kadmin -q "ktadd -k /path/to/memsql.keytab memsql/host.domain.com"

Note the output path for the keytab file, as you will need to copy it to MemSQL aggregator nodes in future steps. You can verify that the keytab file was created successfully by executing the ktutil command:

$ ktutil

At the ktutil prompt, enter the path to the keytab file you just created and then list its contents:

$ ktutil: read_kt /path/to/memsql.keytab
$ ktutil: list

If the keytab was successfully added, you will see output similar to the following:

slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    3          memsql/host.domain.com@DOMAIN.COM
   2    3          memsql/host.domain.com@DOMAIN.COM

Note the KVNO value, which represents the Key Version Number (KVNO). If you create a new keytab in the future, the encryption keys stored in the older keytab will be invalidated. You can use this KVNO value to compare an older and newer keytab file for versioning purposes.

Windows Active Directory KDC

The following steps use ktpass.exe, which is a command-line tool available for Windows versions configured with Active Directory Domain Services (AD DS). When ktpass.exe is executed with the appropriate parameters, it generates a keytab file that maps an existing Active Directory user principal name (UPN) to a MemSQL service principal name (SPN) and allows Kerberos-authenticated users to connect to the cluster. See the ktpass.exe documentation for more information.

Execute the following command in a new command line window with Administrator permissions, replacing any placeholder values with your own:

ktpass.exe /princ memsql/host.domain.com@DOMAIN.COM /mapuser memsqluser /pass +rndpass /out memsql.keytab /crypto all /ptype KRB5_NT_PRINCIPAL /mapop set

• host.domain.com@DOMAIN.com: Replace with your fully-qualified domain name for the host server
• memsqluser: Replace with the name of the Active Directory user object to bind with the MemSQL SPN

Configuring MemSQL for Kerberos Authentication

Once you have generated a SPN and a keytab on the Kerberos KDC server, you can configure MemSQL to allow access for Kerberos-authenticated users.

Depending on your MemSQL cluster configuration, the SPN and keytab file may need to be configured on one or many aggregator nodes. For example, if your cluster contains one master aggregator and one child aggregator, you must configure both nodes to allow client connections for Kerberos-authenticated users.

The following steps describe how to use the SPN and keytab file you created in earlier sections.

Copying the Keytab File to MemSQL

A keytab file is a credential that can be used to access network resources, so it is important to consider the security implications of copying it from one location to another. Additionally, since a keytab is a binary file, you must use a transfer method that will not corrupt the file. For example, if FTP is configured to transfer as an ASCII file type instead of a binary file type, the keytab will be corrupted.

Use a secure file transfer method, such as scp, to copy the keytab file from the KDC to each MemSQL aggregator node. After the keytab file has been copied, its filepath on the aggregator node will be used to configure the memsql.cnf file in the next step.

Configuring memsql.cnf

Before Kerberos-authenticated users can connect to MemSQL, you must configure each MemSQL aggregator node’s memsql.cnf file with two variables:

• gssapi-keytab-path: The local path to the keytab file that was created on the KDC and copied to the node. For example: /path/to/memsql.keytab
• gssapi-principal-name: The SPN for MemSQL that was created on the KDC. For example: memsql/host.domain.com@DOMAIN.COM

To set these variables in the memsql.cnf file:

1. Ensure that the cluster is in a stopped state before continuing.
2. Open the node’s memsql.cnf file in a text editor.
3. Add the following line to the file anywhere below the [server] declaration:

[server]
...
gssapi-keytab-path = /path/to/memsql.keytab
gssapi-principal-name = memsql/host.domain.com@DOMAIN.COM
...

4. Save the file.
5. Repeat this process for each aggregator node’s memsql.cnf file.
6. After the memsql.cnf file has been updated for each aggregator node in the cluster, start the cluster.
7. Verify that the change was successful by executing the following command on one of the affected nodes:

memsql> SHOW GLOBAL VARIABLES LIKE 'gssapi%';
--------------
SHOW GLOBAL VARIABLES LIKE 'gssapi%'
--------------

+-----------------------+-------------------------------------+
| Variable_name         | Value                               |
+-----------------------+-------------------------------------+
| gssapi_keytab_path    | /path/to/memsql.keytab              |
| gssapi_principal_name | memsql/host.domain.com@DOMAIN.COM   |
+-----------------------+-------------------------------------+
2 rows in set (0.00 sec)

Granting a User Kerberos Authentication Permissions

Now that the MemSQL cluster has been configured, you can grant permissions for a Kerberos-authenticated user to connect to MemSQL. To grant permissions, you simply map a MemSQL user to their Kerberos SPN. You can map Kerberos SPNs in two ways: by providing the name of a single Kerberos SPN, or by using regular expressions to match with or more Kerberos SPNs.

Mapping a Single Kerberos Identity

The following example grants an existing MemSQL user ('user1'@'%') permissions to authenticate via Kerberos on all databases, where their Kerberos SPN is user1@EXAMPLE.COM:

GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS 'user1@EXAMPLE.COM';

When this command is executed, the 'user1'@'%' MemSQL user is mapped to the user1@EXAMPLE.COM Kerberos SPN. After the command is executed, all subsequent MemSQL connection attempts for 'user1'@'%' will require a ticket-granting-ticket (TGT) from the KDC for the user1@EXAMPLE.COM SPN. If a user attempts to connect with a TGT for a different SPN, the connection will fail.

Mapping with Regular Expressions

You can optionally use regular expressions in the AS clause of the GRANT statement. This functionality is useful if you want to map a single MemSQL user to one or more Kerberos SPNs across multiple domains, or if you have other unique realm requirements.

Regular expressions can only be used in the AS clause of the GRANT statement. By default, regular expressions are disabled in the AS clause.

To enable regular expressions support, the first character in your AS clause must start with a forward slash: /. We recommend that the string should use standard ^ (start of line) and $ (end of line) operators to indicate when the regular expression should start and end.

Consider the following example:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@EXAMPLE\.COM$';

This example will use regular expressions to match the user1@EXAMPLE.COM string in any Kerberos SPN that attempts to authenticate with MemSQL. It uses proper start of line and end of line operators to indicate that only the full user1@EXAMPLE.COM SPN should be matched. It’s possible to introduce unexpected behavior without providing these operators. Consider the following example:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/user1@EXAMPLE\.COM$';

The example above differs from the previous example by omitting the ^ start of line operator. This simple omission introduces significant security implications, because another Kerberos SPN might contain the substring of user1@EXAMPLE.COM, such as other_user1@EXAMPLE.COM. Always ensure that your regular expression produces the intended result.

Example: Optional Subdomain

The following example maps the MemSQL user 'user1'@'%' with a Kerberos SPN that contains an optional subdomain:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@(SUBDOMAIN\.)?EXAMPLE\.COM$';

Example: Optional Domain Suffix

The following example maps the MemSQL user 'user1'@'%' with a Kerberos SPN across domains with different suffixes, namely .COM, .NET, or .ORG:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@EXAMPLE\.(COM|NET|ORG)$';

Connecting to MemSQL as a Kerberos-authenticated User

After a MemSQL user has been associated with a Kerberos SPN, you can connect to MemSQL as that user. Before authenticating however, you must have a valid TGT in your credential cache. Depending on your domain and system configuration, there are many different ways to acquire a valid TGT. For example, the most common method on Linux/Unix is to execute kinit from the terminal. On Windows, TGT acquisition and renewal is typically done automatically, as per the Active Directory group policy configuration.

Using the MariaDB command-line interface for example, you can execute the following command to connect to MemSQL as user1:

mysql --plugin-dir=/path/to/plugin-dir -u user1 --prompt="memsql> "

Note that this authentication method requires you to specify the plugin directory that contains the auth_gssapi plugin for MariaDB, as described in the Prerequisites section above. Depending on the host operating system, you must provide the appropriate GSSAPI authentication plugin version. MariaDB provides authentication plugins for 32-bit and 64-bit versions of each supported operating system.

If you want to connect using ODBC, consider the following example below:

Driver=<path-to-mariadb-odbc-connector>;Database=information_schema;ServerName=10.0.0.1;Port=3306;UID=myusername;Plugin_Dir=<path-to-gssapi-plugin-directory>;

Troubleshooting

You may encounter errors during initial configuration or when attempting to connect to MemSQL as a Kerberos-authenticated user. This section describes common error scenarios and how to resolve them.

Service Principal Name Does Not Match

Consider the following client error:

ERROR: p8188 t99998 c5 Retrieved client name 'user2@EXAMPLE.COM' does not match expected 'user1@EXAMPLE.COM'. Access denied
ERROR: p8188 t99998 c5 ProcessHandshakeResponsePacket() failed. Sending back 1045: Retrieved principal does not match expected Kerberos username

This error scenario is caused by attempting to connect as a MemSQL user with a SPN that differs from the SPN to which the MemSQL user is bound. For example, a MemSQL user ('user1'@'%') is bound to the user1@EXAMPLE.COM SPN. The client’s currently initialized SPN is user2@EXAMPLE.COM. If you attempt to connect to MemSQL as the 'user1'@'%' user, MemSQL will deny access because 'user1'@'%' is not bound to user2@EXAMPLE.COM. To resolve this error, retrieve a TGT using kinit for the correct user1@EXAMPLE.COM SPN and try the connection again.