Tableau Kerberos Delegation to SQL Server / SSAS – Part 1 – The Theory (Kerberos Tickets, Service Principal Names and Token Size)

By | 1st May 2016

Active Directory is by default geared to a two tier Client/Server architecture, the Client can only use resources either locally or on the Server it directly connects to. For multi-tier Client/Server environments it’s more tricky because the user doesn’t authenticate directly with the target server perhaps not even able to connect because of firewall or routing constraints, the middle tier needs to perform the action of “Delegation” that is to say the user has given authority for the middle tier to present itself using the user credentials to the third tier machine.

This article works through everything you need to know to get Tableau Server working with Kerberos delegation to a Microsoft SQL Server or Microsoft Analysis Services (SSAS) data source. I’ve chopped the post into three parts because it was getting rather long, this first part deals with all the theory – I recommend you study this first even if you understand Kerberos, the second part actual set up instructions and the third post will be around troubleshooting.

The diagram below shows what we are going to achieve, HAZEL uses the browser to connect to Tableau Server running on ESTER which gets data from the SQL Server POPPY using the logon credentials from HAZEL.

Tableau to SQL Server Kerberos Delegation - Environment

Authentication

On successfully logging onto the domain the user gets an encrypted token called a “Ticket to get Tickets” (covered here: Kerberos Explained), the TGT is the users identity and contains all the domains and groups the user belongs to and permissions granted.

When the user tries to authenticate with a resource for instance SQL Server the user sends the TGT to the server which then knows what groups the user has permission to and what permissions – importantly without having to query the AD, incidentally that is why there is often a delay between granting or revoking a permission and it actually taking effect and why logging off/back on fixes it – you get a new TGT! The ticket only contains SID (System Identifiers) – names aren’t held locally, to resolve them the local machine would have to look them up against the AD which is why when the AD isn’t available you’ll see a string something similar to ‘S-1-5-23-23424234-23423423432-234234234-433’ instead of ‘Some Tableau AD group’.

Ticket Size Issues

A logged in user has a TGT which can be passed around for servers to check against before allowing access to resource such as SQL Server, as you probably realise the ticket contains quite a lot of information, the size of the ticket has nothing at all to do with delegation which I’ll talk about shortly, the default maximum ticket size varies depending on the OS the various machines in the chain are using – for Windows XP/Windows Server 2000 it’s 8,000 bytes, from Windows 7/Windows Server 2008 it was 12,000 bytes and in Windows 8/Windows Server 2012 it’s 48,000 bytes, so if your desktop is using say Windows 8 but your Server is running Windows Server 20008 then the client can pass a ticket of 48,000 but you’re server by default can only handle 12,000.

Exceeding the maximum ticket size will cause authentication to fail, the user will get one of a number of error messages. Realistically the user needs to be in many hundreds of AD groups here, researching the issue it would appear every group takes 40 bytes – this article covers it in detail: Problems with Kerberos authentication when a user belongs to many groups. All is not lost – make sure your middle server tier has the MaxTokenSize set to 48,000 which looks to be the maximum for general use for desktop applications and browsers connecting with IIS.

Delegation (Multi-Hop)

Kerberos TGT path on Multi-hop Delegation

Step 1: The local users machine (HAZEL) contains a ticket (TGT) obtained at logon (step 1), the ticket contains all the groups and permissions they have.

Step 2: Local user machine sends the TGT to the middle server (ESTER), the middle server (ESTER) needs to access a remote resource (POPPY) but using the credentials from the local user on HAZEL.

Step 3: Middle server (ESTER) passes the TGT to the remote server (POPPY).

Step 4: Remote server (POPPY) authenticates the local user (HAZEL) and runs anything in the context of that local user, network traffic flow from POPPY to HAZEL through ESTER.

The middle server (ESTER) is acting and will appear to the remote server (POPPY) as the local user – yes, you heard correctly – the middle server acts like the local user so the resource running on the middle server the local user has connected to has all the permissions the local user has – so you need to trust that middle server! Active Directory in Windows secures by default so by default the middle server will not be allowed to send the local users TGT to the remote server – it tries but it’s actually got nothing in it!

Delegation authority needs to be permissioned for the the middle server “computer” as well as the logon that runs the service on the middle server that is connecting out to the remote resource. Before enabling that in AD we need to create a number of “Service Principal Names” (SPN) which I’ll discuss next.

It’s worth pointing out that at no time does the Client talk directly to the third tier server, all network communications route through the middle tier, in fact if you the middle tier machine is connecting to a SQL Server machine then you will see the hostname as ESTER but the login credentials will be whatever they have used to get onto HAZEL.

Service Principle Names (SPN)

SPN’s are used by Kerberos clients (the user on HAZEL in this case) to uniquely identify an instance of a service on a target server (in our case POPPY). The way I like to think about it is that it helps security, for SQL Server we create a SPN that binds to the login that will delegate for the user, in our demo the login originates from HAZEL, the delegation occurs on ESTER through the login svcTableau (that’s the service account running Tableau Server in my environment) against the service instance on POPPY.

Basically to form the SPN we need a) the service account that runs the Tableau Server, b) the fully qualified domain name for the server running SQL Server and c) the port SQL runs on and possibly the instance name depending on how you connect.

The example SPN’s set up for this test environment are shown below (using SETSPN -L svcTableau):

C:\Windows\System32>setspn -L svcTableau
Registered ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net:
MSSQLSvc/poppy.tonydemo.net:14331
HTTP/ester.tonydemo.net
HTTP/ester

The SPN structure is {Service Class}/{Host}:Port where :Port is optional.

{Service Class} for SQL Server

SQL Server Database Engine: MSSQLSvc
SQL Server Analysis Server: MSOLAPsvc.3
SQL Server Reporting Services: HTTP because SSRS actually runs as a webservice through IIS.
SQL Server Browser (only used when the process connects to SSAS using an instance name – see Microsoft KB SPN Required for SSAS): MSOLAPDisco.3

I would strongly recommend using fixed port numbers for named instances otherwise you’ll have to cover off all likely ports SQL Server will use when it restarts and dynamically assigns one – yes – that’s a lot! So – just fix the port number in SQL Server Configuration Manager… Network Settings but I’ll cover that in Part 2.

{Host}

None of this will work unless the DNS is set up correctly so make sure your fully qualified domain name ping’s properly i.e. pinging poppy.tonydemo.net and doing a reverse ping on the IP returns the correct box – we’ll cover that in Part 2 as we go through set up. You only need an entry for the fully qualified domain name and port the instance is listening on eg. MSSQLSvc/poppy.tonydemo.net:14331.

Summary

That covers the theory, in a nutshell – The user logs into the domain on their workstation, AD gives them back an encrypted TGT which is held on their local machine, that TGT holds all the Domains and AD Groups they have permissions to; the user runs something on the middle tier (a worksheet requiring a Data Source for instance), the middle tier connects to the remote resource by passing on the TGT from the user, the third server now things the user is connecting directly; as part of the delegation process checks are made that the middle tier machine and the login used to start the service on the middle tier is allowed to delegate for the service the user requires.

Part 2 covers how this is actually implemented using Tableau Server on the middle tier.

References

Windows Authentication Overview

Kerberos Survival Guide

Kerberos for the Busy Admin

Understanding Kerberos Double Hop

qUICKLY Explained: Service Principal Name: Registration, Duplication

Category: Uncategorised

One thought on “Tableau Kerberos Delegation to SQL Server / SSAS – Part 1 – The Theory (Kerberos Tickets, Service Principal Names and Token Size)

  1. Pingback: Tableau Kerberos Delegation to SQL Server / SSAS – Part 2 – Setting it up | Tony Rogerson SQL Server Data Platform Ramblings

Leave a Reply

Your email address will not be published. Required fields are marked *