Draft: Kerberos Constrained Delegation
Goal
My goal is to configure KCD to a SQL server data source, with the user authentication being SAML with OneLogin. My servers and workstations are all joined to an Active Directory domain and I am using AD as the Tableau external Identity store.
Requirements
The requirements for Enabling Kerberos Delegation authentication to a database for Tableau are:
The Tableau Server information store must be configured to use LDAP - Active Directory.
The computer where Tableau Server is installed must be joined to Active Directory domain.
A domain account must be configured as the Run As service account on Tableau Server.
Delegation configured. Grant delegation rights for the Run As service account to the target database Service Principal Names (SPNs).
If I read this Enabling Kerberos Delegation for SQL Server the first step it tells me to configure Kerberos authentication on the server. However I assumed you wouldn't require this to be configured for KCD to a data source. If I then review the guidance on keytab requirements it refers to Data Source Delegation four key points
The computer account for Tableau Server (Windows or Linux) must be in Active Directory domain.
The keytab file that you use for Kerberos delegation can be the same keytab that you use for Kerberos user authentication (SSO).
The keytab must be mapped to the service principal for Kerberos delegation in Active Directory.
You may use the same keytab for multiple data sources.
We also have the article Use SAML SSO with Kerberos Database Delegation, which doesn't refer to enabling kerberos as a user authentication scheme. However it does refer to using ...the Tableau Server keytab to access the database.
Additionally, some guidance in the Configuring Kerberos authentication on Tableau server running on Windows article from my colleague Andrija Marcic.
Supported Scenarios:
Active Directory Kerberos
Unsupported Scenarios:
MIT Kerberos
SQL and SPN's
...A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it's registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn't been performed or fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication isn't used...
I have SQL installed and joined to an AD domain, and currently set to the defaults. The accounts that are running the services are local Virtual Accounts which are auto-managed and can logon to the network and register SPN's.
Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA, gMSA or virtual account when possible. When MSA, gMSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported...
I wanted to check the registering of SPN's worked with virtual accounts, so I queried AD for the SPN's related to the SQL server (screenshot below), I then deleted those SPN's using:setspn -D MSSQLSvc/sql-win2019.thompson365.com sql-win2019and setspn -D MSSQLSvc/sql-win2019.thompson365.com:1433 sql-win2019and restarted the SQL Database Engine and found they were recreated.
We query for the SPN based on the computer name because "...Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$..."
Tableau SPN config
****
Put something about the security implications of this on the runas account:
Without the Tableau HTTP SPN configured you do not get the option to configure delegation.
Testing (reverse engineering)
This great article provides a useful SQL script to confirm your client is using kerberos : https://www.red-gate.com/simple-talk/sql/database-administration/questions-about-kerberos-and-sql-server-that-you-were-too-shy-to-ask/
Configuration
Test
Result
Delete keytab
Add SQL as a datasource in Desktop and check klist/sql script
No issues. Keytab not relevant.
Delete Tableau SPN
Delete SPN whilst SQL service is still running.
Add SQL as a datasource in Desktop and check klist/sql script
This had no impact on desktop as it is making a SQL connection not an HTTP connection.
Remove MSSQL SPN from Runas account
Go users and computers and deselect MSSQLSvc from the delegation tab
With the Tableau HTTP SPN deleted, there is not the delegation option available to delete anything.
(However, even without that you can still sign in)
Delete SQL SPN (setspn -D MSSQLSvc/sql-win2019.thompson365.com sql-win2019)
Add SQL as a datasource in Desktop and check klist/sql script
I am able to sign in via Desktop. It doesn't pick up a kerberos ticket and it connects via NTLM
When I signed in using Tableau Desktop to the SQL server I received this cached ticket when running klist.
Running the SQL Script shows that the connection from the tableau-desktop workstation is using kerberos.
After I'd removed the Tableau SPN's (which removes the delegation config too) the user connection fell back to NTLM.
After recreating the SPN's and delegation configuration ont he runas account, I needed to reboot the workstation to revert back to Kerberos from NTLM.
Keytab in my lab
Useful notes in the article talk about how:
Kerberos keytabs, also known as key table files, are only employed on non-Windows servers. In a homogenous Windows-only environment, keytabs will not ever be used, as the AD service account in conjunction with the Windows Registry and Windows security DLLs provide the Kerberos SSO foundation.
With Tableau it is important to understand the purpose and use case for the keytab file. We have 3 separate uses of the keytab file.
Operating system - which I would refer to as User Authentication
Directory Service - read-access to your AD/LDAP
Datasource delegation - accessing data sources that have kerberos authentication enabled
We have a batch file that provides guidance on how to configure your keytab.
Browser IWA
Logging in to browser on Tableau Server and the workstation. Likely IWA issue with browser below. (Can’t connect to Microsoft SQL Server Detailed Error Message [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'THOMPSON365\tableau-runas'. Integrated authentication failed. 2021-02-02 15:02:39.841, (YBlpjyzPedSdTWKp5GyAsQAAAuo,1:0)
Last updated
