Tableau Kerberos Delegation to SQL Server / SSAS – Part 2 – Setting it up

The ability to use Kerberos delegation against SQL Server and SQL Server Analysis Services (SSAS) was introduced in Tableau Server 8.3; we covered theory in the post Tableau Kerberos Delegation to SQL Server / SSAS Part 1 – The Theory (Kerberos Tickets, Service Principal Names and Token Size) – make sure you have reviewed that before continuing because this article just contains the instructions to get it all working.

Required information prior to starting

Make sure you get all this information to before starting, validate it, set up permissions before starting.

1. Domain Account to run Tableau Server, it doesn’t require any permissions to SQL Server / SSAS because it will simply be delegating using the client credentials (this demo uses tonydemo\svcTableau for Tableau Server and tonydemo\testuser for the local user). If possible use an account that doesn’t have access to the SQL Server for security reasons. The Tableau Server account must be on the same domain as the SQL Server / SSAS machine. Users can be on any domains.
2. Fully Qualified Domain Name to the SQL Server / SSAS machine (for this demo is poppy.tonydemo.net).
3. TCP Port that the SQL Server / SSAS service is listening on – make sure this is a fixed port which I’ll cover later (for this demo is 14331). I’ll reiterate – make sure you use fixed ports because it makes setting the SPN’s up easier but separately from a security and governance perspective it’s good practice! Also – make sure you check SQL Server / SSAS is currently running on those ports – ask for actual evidence!
4. A login that has the correct permissions on SQL Server / SSAS in order for you to create the Tableau Data Source the clients will use (for this demo I’ll use tonydemo\Administrator).
5. A login to test everything is working, the login needs to have permission into Tableau Server and also SQL Server to run the query set up previously for the Data Source (for this demo I’ll use tonydemo\testuser).
6. Active Directory group you will use as part of the Tableau single sign on and mapped to a Tableau role, note: this has nothing at all to do with Delegation as the permissions are set in SQL Server / SSAS, remember – Tableau is simply delegating using the credentials from the user.

Test the Data Source

The connection originates from the middle tier machine i.e. the one running Tableau Server, but instead of using the Tableau Server service account it uses the user credentials (via the delegated TGT to the SQL Server/SSAS). Make sure a test user in the AD domain you are giving access to SQL Server / SSAS for can actually login to SQL Server / SSAS and run the query you want to generate for your Data Source – test the connection from the Tableau Server itself – the easiest way to do that is to allow the test user remote desktop access to the middle tier server and try connecting using ODBC admin tool – basically something you can test connectivity.

As I’ve said earlier I strongly recommend you use static ports, if you don’t then forget using TCP Sockets as a method for the middle tier to connect to SQL Server – you’ll need to use Named Pipes for which I’m not going to cover because you for numerous reasons ought to have static ports in production for SQL Server and SSAS.

I’ve added instruction below on how to configure static ports for SQL Server and SSAS.

Configuring SQL Server to listen on a static port

Using SQL Server Configuration Manager select SQL Server Network Configuration – make sure you get the correct instance name, set the TCP Port under IPAll to the port you desire for example we will use port 14331 for this SQL Server Database Engine instance. By using the port number directly you can stop and disable the SQL Browser because it is not required, well – unless you have other instances/users using the instance name rather than port directly.

Make sure you restart SQL Server, more importantly – make sure the firewall allows inbound connections to that port from the middle tier server – it’s only the middle tier server (ESTER) that connects to database machine (POPPY).

The SQL Server errorlog (run the xp master..xp_readerrorlog through SSMS) will tell you what ports SQL Server is listening on, for my demo environment the output looks like this:

2016-04-30 14:52:08.110 Server Server is listening on [ ‘any’ <ipv6> 14331].
2016-04-30 14:52:08.110 Server Server is listening on [ ‘any’ <ipv4> 14331].

Configuring SSAS to listen on a static port

Using SQL Server Management Studio (SSMS) select “Connect..” and choose “Analysis Services”, connect to the relevant SSAS instance.

Right click on the server name and choose New Query… XMLA and run the modified piece of XMLA for your environment. Change the ID, Name and ServerProperty Port – Value.

Now restart SSAS.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" 
      xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object />
  <ObjectDefinition>
    <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
            xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
            xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" 
            xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
            xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" 
            xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <ID>POPPY\SQL2008R2</ID>
      <Name>POPPY\SQL2008R2</Name>
      <ServerProperties>
        <ServerProperty>
          <Name>Port</Name>
          <Value>20251</Value>
        </ServerProperty>
      </ServerProperties>
    </Server>
  </ObjectDefinition>
</Alter>

You can check which port SSAS is running on in SSMS by connecting to the SSAS instance, right click properties and check the “Running Value” for the Port setting – see below:

Make sure the PING’s work

The middle tier server connects to the database so remote desktop to the middle tier server using an administrator account and ping the fully qualified domain name also do a reverse ping and make sure the IP address return correctly, your output should look like that below:

C:\>ping poppy.tonydemo.net

Pinging poppy.tonydemo.net [10.10.10.12] with 32 bytes of data:
Reply from 10.10.10.12: bytes=32 time<1ms TTL=128
Reply from 10.10.10.12: bytes=32 time<1ms TTL=128
…

C:\>ping -a 10.10.10.12

Pinging POPPY [10.10.10.12] with 32 bytes of data:
Reply from 10.10.10.12: bytes=32 time<1ms TTL=128
Reply from 10.10.10.12: bytes=32 time<1ms TTL=128
…

Add the SPN’s

When requiring a multi-hop connection to SSAS you must use the Instance Name – you cannot connect to SSAS through the port directly; SQL Browser is used (connection is over TCP 2382) to determine the correct port for the instance name which the client then communicates with SSAS over – that requirement means you need to set up SPN’s for SQL Browser as well. For SSAS add SPN’s for both the FQDN as well as the local machine name.

Add the SPN’s that relate the Tableau Server service account (tonydemo\svcTableau) with the remote resource it’s providing delegation services to, you will need a Domain Administrator to run the next bit, it can be ran on any machine in the domain, fire up a CMD prompt and run this:

C:\>setspn -S MSSQLSvc/poppy.tonydemo.net:14331 svcTableau
Checking domain DC=tonydemo,DC=net

Registering ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net
MSSQLSvc/poppy.tonydemo.net:14331
Updated object

C:\>setspn -S MSOLAPSvc.3/poppy:SQL2008R2 svcTableau
Checking domain DC=tonydemo,DC=net

Registering ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net
MSOLAPSvc.3/poppy:SQL2008R2
Updated object

C:\>setspn -S MSOLAPSvc.3/poppy.tonydemo.net:SQL2008R2 svcTableau
Checking domain DC=tonydemo,DC=net

Registering ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net
MSOLAPSvc.3/poppy.tonydemo.net:SQL2008R2
Updated object

C:\>setspn -S MSOLAPDisco.3/poppy svcTableau
Checking domain DC=tonydemo,DC=net

Registering ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net
MSOLAPDisco.3/poppy
Updated object

C:\>setspn -S MSOLAPDisco.3/poppy.tonydemo.net svcTableau
Checking domain DC=tonydemo,DC=net

Registering ServicePrincipalNames for CN=Tableau,CN=Users,DC=tonydemo,DC=net
MSOLAPDisco.3/poppy.tonydemo.net
Updated object

Checkpoint

For a SQL Database Engine instance you should have a single SPN, for a SSAS box you should have 4 SPN’s – triple check you have done these correctly!

Active Directory Accounts and Delegation Configuration

Now the SPN’s are set up the “Service Class” will appear as a choice in the AD config for the machine and user, again we need a Domain Administrator, there are two things to do here – first allow the machine that does the delegation i.e. Tableau Server (ESTER) delegation permission to the service classes it will use, this is actually done against the middle tier machine (ESTER) but using the Services the account Tableau Server runs under. If you remember the SPN setting up – that tied a service to the Tableau Server account (svcTableau) – this is where it starts coming together.

Middle Tier Machine

Open up Active Directory Users and Computers, in the Computers tree select the server running Tableau Server (ESTER) and right click Properties, select the delegation tab; we should secure by default so restrict to specified services only (which is termed constrained delegation). On the Add Services / Users or Computers make sure you select the account you will run the Tableau Server under i.e. tonydemo\svcTableau.

Click OK and if you have set up the SPN’s correctly and the above you should now have a pop-up with the services listed below, remember the MSOLAPDisco.3 is SQL Browser (port should be blank), MSOLAPSvc.3 is your SSAS (port should be your instance name and not a port number), MSSQLSvc is SQL Server Database Engine (port should be the port number).

You likely won’t see the HTTP one, that is actually added by Tableau but we haven’t completed that yet – I’ve structured this demo to do all the Tableau bits last. The importance piece here is you should have the SQL Server / SSAS services shown, choose “Select All” and click OK and you should have a screen like below, click “Apply” and “Ok” and this piece is done and you are ready to do the same but for the account Tableau Server runs under.

Tableau Server Service Account

After creating the SPN’s against tonydemo\svcTableau the delegation tab will appear when you manage the user through AD Users and Computers, if you have reached this part of this list of instructions and the Delegation tab doesn’t appear then you have either set up the SPN’s against a different account or perhaps you are waiting for AD replication to take place i.e. you are looking at a different AD node to the one the SPN’s have just been created in.

In Users, find the account you are going to run the Tableau Server under, pick the Delegation tab and add the MSSQLSvc, MSOLAPDisco.3 and MSOLAPSvc.3 Services as you did before for the Computer.

The account above should also be part of the “Act as part of the operating system” and “Impersonate a client after authentication” policies so use the Local Security Policy on the middle server (ESTER) running Tableau Server and add that permissions (see below):

Tableau Server Configuration

Open the “Configure Tableau Server” on the server, start by adding the AD account that you will run Tableau Server with – do not use a local account, needs to be a domain account.

Set the “User Authentication” mode to “User Active Directory”, specify the Active Directory Domain name and check the “Automatic logon (Kerberos) tab.

The next part requires creating a script, running it and importing a “keytab” file, I recommend creating a directory on the middle tier machine running Tableau Server to store it all in – makes life easier. Click the “Kerberos” tab, check “Enable Kerberos for single sign-on”, you need to click the “Export Kerberos Configuration Script” which creates a .BAT file that needs to be ran by a Domain Administrator – we’ll discuss that next, drop the file in the directory I recommended you create.

The .BAT file generated from Step 1 is shown below with comments stripped, the important pieces have been highlighted and need to be checked before the Domain Admin account executes it.

@echo off
setlocal EnableDelayedExpansion

set /p adpass= "Enter password for the Tableau Server Run As User (used in generating the keytab):"
set adpass=!adpass:"=\"!

echo Creating SPNs...
setspn -s HTTP/ester TONYDEMO\svcTableau
setspn -s HTTP/ester.tonydemo.net TONYDEMO\svcTableau

echo Creating Keytab files in %CD%\keytabs
mkdir keytabs
ktpass /princ HTTP/ester.tonydemo.net@TONYDEMO.NET /pass !adpass! /ptype KRB5_NT_PRINCIPAL /out keytabs\kerberos.keytab

Get a Domain Administrator to log onto the Tableau Server machine, bring a CMD prompt up and run the .BAT file, it creates a keytab file using the Windows ktpass utility in a new sub-directory of “keytabs”.

Import the keytabs file, finally click the “Test Configuration” and you should get the message “SPNs are correctly configured: SPN Configuration Complete”.

You may need to stop and restart Tableau Server for the options to take effect.

Creating a Data Source for Clients to Use

1. Fire up the Tableau Desktop and create a New Sheet.
2. Select “Connect to Data”, select “Microsoft SQL Server” and enter the server name e.g. poppy.tonydemo.net,14331 and use “Trusted Connection”.
3. Give the Data Source a name, for example “Viewer Credentials Blog Demo” and select the Database and Table – set up your query bits, it should be a “Live Connection” – see example below where I have connected to the SQL Server machine we set up earlier, the “play” database and the “somedata” table.
4. Click on “Sheet 1” which is where we can finalise our Data Source and publish it to Tableau Server with the “Viewer Credentials” setting that brings all this together. Select the “Server” option and “Publish data source” and select your data source as below, a login prompt will ask for the Tableau Server you want to connect to, select the server you set up earlier e.g. ester.tonydemo.net:8000.
5. The publish data source to Tableau Server pop-up will appear, click “Edit” under “Authentication” and select “Viewer credentials”, this is the piece that will force Tableau Server to delegate the authentication of the user credentials.
6. Once you have published successfully you can use the Data Source and Tableau Server will pass the local user credentials and use those against SQL Server / SSAS, example below; notice the login name is the local user and not the Tableau Server account tonydemo\svcTableau, also note the hostname is ESTER which is the Tableau Server – in this test I access the Data Source from the user machine HAZEL logged in as tonydemo\testuser.

   For completeness this is a Profiler output from a SSAS connection through delegation, you can see the NTUserName is the login we used on HAZEL which is our client, the ClientHostName shows the request came from 10.10.10.11, on doing a reverse lookup we can see that IP address is indeed the middle tier machine ESTER.

Summary

As you can see from above it works, fairly straightforward to set up but there are a lot of moving parts to get it working; I’m writing a post around diagnosing when you get problems or issues getting it set up.

If you do need help setting this up then give me a ping via email.

References

Tableau Server Help