Use Kerberos authentication with Microsoft SQL Server resources

Learn how to configure a self-hosted instance to use Kerberos authentication with Microsoft SQL Server resources.

If you self-host Retool, you can use Kerberos authentication with Microsoft SQL Server resources. This requires creating the necessary Kerberos configuration file and making changes to the deployment configuration.

Deprecated authentication method

Kerberos authentication relies on Windows authentication for Microsoft SQL Server resources, which has been deprecated. Use Username and password authentication with a SQL Server login instead. Refer to the deprecation announcement for migration steps.

1. Prepare the Kerberos configuration file

Docker

Kubernetes

This configuration file contains the required information to implement Kerberos authentication. Create a file named krb5.conf and use the following template, replacing placeholders with values for your environment.

[libdefaults]
    default_realm = <YOUR-REALM>                 # e.g., http://EXAMPLE.COM 
    dns_lookup_realm = false                      # optional
    dns_lookup_kdc = false                        # optional
    ticket_lifetime = 24h                         # optional
    renew_lifetime = 7d                           # optional
    forwardable = true                            # optional

[realms]
    <YOUR-REALM> = {                            # e.g., http://EXAMPLE.COM 
        kdc = <KDC-HOSTNAME-OR-IP>              # e.g., kdc.example.com
        admin_server = <KDC-HOSTNAME-OR-IP>     # e.g., kdc.example.com
    }

[domain_realm]
    <YOUR-DOMAIN> = <YOUR-REALM>                # e.g., corp.local = http://EXAMPLE.COM 
    .<YOUR-DOMAIN> = <YOUR-REALM>               # e.g., .corp.local = http://EXAMPLE.COM 

[logging]
    default = FILE:/var/log/krb5.log            # Log file for Kerberos
    kdc = FILE:/var/log/kdc.log                 # Log file for KDC operations
    admin_server = FILE:/var/log/kadmind.log    # Log file for admin server

2. Add the configuration to your deployment

Docker

Update the deployment's Dockerfile to reference the Kerberos configuration file.

FROM tryretool/backend:X.Y.Z

# section:begin - copy krb5.conf

# Switch to root to add Kerberos configuration
USER root

# Copy Kerberos configuration into the container
COPY krb5.conf /etc/krb5.conf  # Ensure the destination path is correct

# Switch back to the application user
USER retool_user

# section:end - copy krb5.conf

# Start the Retool API
CMD ./docker_scripts/start_api.sh

Kubernetes

If you use a Kubernetes-based deployment, you must create a Dockerfile build the image, and push it to the registry.

Update the deployment's new Dockerfile to reference the Kerberos configuration file.

# section:begin - copy krb5.conf

# Switch to root to add Kerberos configuration
USER root

# Copy Kerberos configuration into the container
COPY krb5.conf /etc/krb5.conf  # Ensure the destination path is correct

# Switch back to the application user
USER retool_user

# section:end - copy krb5.conf

Once complete, restart your deployment for the changes to take effect.

3. Configure the SQL Server resource

A Kerberos principal is the unique identity that represents a user, service, or host. It is comprised of three parts.

Part	Description	Example
Primary	The name of the user, service.	jenny_appleseed or HTTP
Instance	An optional part used to distinguish principals that share the same primary name. This is commonly used for specific services (e.g., the host for machine accounts). If omitted, it defaults to the primary user principal.	admin
Realm	The domain-like boundary that the principal belongs to, typically written in uppercase.	EXAMPLE.COM

A complete principal is formatted as primary/instance@REALM. To use Kerberos authentication with SQL Server resource, set the Database username to the Kerberos principal.

Windows authentication resources only accept allowlisted ODBC connection options in Connection options. Configure encryption and certificate verification under the resource SSL/TLS settings.

Troubleshooting Kerberos authentication

Use the following guidance to troubleshoot connection issues to SQL Server resources when using Kerberos authentication.

Verify Service Principal Name (SPN)

The SPN identifies the service instance within Kerberos. This must be the same value as the SQL Server's FQDN. You can use Powershell to verify the SPN on the SQL Server host machine.

setspn -L COM\<DomainAccount>

# Example output
# MSSQLSvc/sqlserver.example.com:1433 <DomainAccount>

Check the credential cache

Kerberos uses a cache to store tickets for authentication. This cache is relied upon by other tools, such as kinit and sqlcmd.

Inspect tickets

Use klist to inspect the Kerberos ticket cache and default principal.

klist

Example output

Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: custom_user@EXAMPLE.COM

Clear the cache

Use kdestroy to clear the cache.

kdestroy

Set the cache location

You can specify a different location by updating the KRB5CCNAMME environment variable.

export KRB5CCNAME=/tmp/custom_kerberos_cache

Enable trace logging

You can enable detailed trace logs to help troubleshoot Kerberos authentication issues with the KRB5_TRACE environment variable.

export KRB5_TRACE=/tmp/kerberos_trace.log

Trace logs are available using kinit and with other tools that use Kerberos.

To add these to the container, update the Dockerfile to include additional environment variables.

ENV KRB5CCNAME=/tmp/krb5cc_cache
ENV KRB5_TRACE=/dev/stdout

Once set, all trace logs output to container logs. Retool recommends this approach if you need to troubleshoot Kerberos issues.