Did you know that you can navigate the posts by swiping left and right?

Let's automate all the things!

06 May 2016 . automation . Comments #server #hack

First things first - nothing you read here is supported!!

Try at your own risk - here be dragons

You've been warned!!!


credit where credit is due

This blog post is based on work I did while I was at Slalom and is part of a much larger effort you will be hearing about in the very near future. Many thanks to Slalom for supporting the research and not thinking I was a crazy person (or at least putting up with the crazy). I’ve been sitting on this documentation for about a year now, but then I saw this on my twitter feed this morning.

I talked with Tamas about what he was working on, and this post is a good complement to what he has or will be releasing. He’ll be getting into the nitty gritty about configuring Tableau services. He’s got the how, I’ve got the what.

So…what sort of things can you configure in Tableau Server? If you read the documentation, this is the official list:

Except that’s not the real list. I’m here to tell you that any option you can select in Configure Tableau Server you can set.

Anything in any of these tabs Allthethings

And these things too allthethings2

You can do it all!

Everything you are about to see is tested and validated - but there are some parts of that process I can’t talk about. This documentation is good for every version of Tableau Server through 9.3. Through the rest of the blog, I’ll show you how to configure each setting, as well as any other things you need to know in order to make it all work.


This project was borne out my frustration with setting up, installing, and administering Tableau Server. So much waiting and clicking and waiting some more. I wished I could make Tableau do whatever I want via the command line (no Powershell spoofing of keystrokes allowed).

Like what, you ask? You know that sentence in the Server Admin guide that says

Select whether to use Active Directory to authenticate users on the server. Select Use Local Authentication to create users and assign passwords using Tableau Server’s built-in user management system. You cannot switch between Active Directory and Local Authentication later.

I’m calling BS. With this method, you can switch on the fly if you want, no uninstalling necessary. Do what you want.

What about adding or reconfiguring workers on the fly - or an automated HA setup? Yeah, you can do that too!

  • Automated Key activation/deactivation? - check
  • Update all services on all boxes? - check
  • Adjust SSL/SAML providers? - check
  • Dynamic Parameters? - not gonna happen
  • Tableau + DevOps? - You better believe it!

So, how did I figure all this out?

mattdamon


Tableau Server Configuration: The basics

Before we can configure Server, we have to deal with two more pieces of configuration data. First is your registration, which are all the details you have to enter when you activate your license key. This is stored in the Registry: HKLM\Software\tableau\registration\data

For those of you who aren’t familiar with the Windows Registry, it’s a small database that stores settings. HKLM refers to settings on your Local Machine (HKEY Local Machine = HKLM).

To edit the registry, you have to execute the following command

reg add HKLM\\Software\\tableau\\registration\\data /v [value] /d [data] /f [force overwrite]

Below is the list of values that you’ll need to update to complete the “Registration” process:

  • city
  • company
  • country
  • department
  • email
  • first_name
  • industry
  • last_name
  • phone
  • state
  • title
  • zip

You may remember that Industry and Department are both drop downs - so here are the lists for those menus

Industry

industry

Departments

  • Accounting/Finance
  • Engineering/Development
  • General Mgmt/Administration
  • Human Resources
  • Legal
  • Marketing
  • Operations
  • Channel
  • Product Management
  • Purchasing/Merchandising
  • Sales
  • Science
  • Support/Service
  • Other

As of 10.1, you have a new command tabadmin register which accepts a .json file with all of these fields. No more Registry hacking.

Here’s a sample file


  {
    "first_name" : "Luke",
    "last_name" : "Skywalker",
    "email" : "lukes@rebels.edu",
    "company" : "Rebel Alliance",
    "title" : "Jedi Master",
    "department" : "Education",
    "industry" : "Force",
    "phone" : "206-555-1212",
    "city" : "Dune Sea",
    "state" : "Tatooine",
    "zip" : "12345",
    "country" : "Outer Rim"
  }  

The second is activating your License. There was an old KB article for doing this, but tabadmin has you covered

tabadmin activate --key [which is your license key]

You can deactivate a key by switching deactivate for activate.


Tableau server configuration - the process

Now that we are done with license and registration, it’s important to understand how Tableau actually does its configuration work. Understanding this process was the key to extracting all this wonderful documentation.

To do this, I used Process Monitor, which is a close cousin to Paul Banoub’s favorite tools Process Explorer and Perfmon. Both come out of the Sysnternals suite, which you should go get because it’s free and awesome.

The YML files

All of Tableau’s configuration details are stored in two files.

  • tabsvc.yml - C:\ProgramData\Tableau\Tableau Server\config. Tabsvc records the non-default settings for Tableau and is a great place to see what has been changed on y our box.
  • workgroup.yml - C:\ProgramData\Tableau\Tableau Server\data\tabsvc\config. This is the motherlode, and contains all the settings for everything. DO NOT EDIT THIS FILE DIRECTLY, EVER!!!

When you open Configure Tableau Server, you’ll see the following in C:ProgramData\Tableau\Tableau Server:

  • A default YML file: temp_default_opts.yml
  • A changed YML file: temp_current_opts.yml

If you watch the folder when you open “Configure Tableau Server,” you’ll see the file appear like a rare Pokemon. You can double-click it to open and save somewhere else.

These are what they seem - defaults and your current options. Then you make some changes, and hit OK. In the same folder as the other two YML files, you’ll see another rare appearance: temp_validate_opts.yml.

You may notice that the RunAs User password is in cleartext. Don’t worry, this is the only time you’ll see that value appear anywhere. It’s not hanging around somewhere - well it is, but it’s salted and encrypted. It’s safe.

Tableau then validates the options you selected, rewrites the YML, installs tabsvc and then you are ready to go.


Tableau Server Configuration - the settings

Here it is. I’ll go screen by screen and document each setting. Some things changed between 9.1, 9.2, and 9.3, and I’ll highlight those as I go. The basis for this script is the following:

tabadmin stop
**tabadmin set key value**
tabadmin configure
tabadmin install --password
tabadmin start

General

General

Server Run As User

  • Username: service.runas.username “DOMAIN\User”
  • Password: service.runas.password

Note about Run As User Password. Depending on which version of Tableau you are using, you may have to try it two different ways. The initial research was performed on Tableau 9.2. For that process, it was necessary to use the tabadmin install --password method. For 9.3, if you run tabadmin set service.runas.password and then do tabadmin configure, it works the same way, AND you shouldn’t see the password in cleartext anywhere.

User Authentication

  • wgserver.authenticate local/activedirectory/saml/openid

Active Directory

  • Domain: wgserver.domain.fqdn
  • Nickname: wgserver.domain.nickname
  • Enable Automatic Login: wgserver.sspi.ntlm true/false

Gateway

  • Port Number: worker.gateway.port ##
  • Open Port in Windows Firewall: install.firewall.gatewayhole true/false
  • Include Sample Data: install.component.samples: you can only set this at initial setup (but its install.component.samples true/false)

Server Crash Reporting

  • Enable Crash Reporting: servercrashupload.enable true/false
  • Crash Reporting Schedule Time: servercrashupload.scheduled_time HH:MM:SS AM/PM Coordinated Universal Time

Data Connections

Connections

Caching

  • Refresh Less Often: vizqlserver.data_refresh (this is default, so you shouldn’t need to change back to this. If you don’t want the other ones, just leave this alone)
  • Balanced: vizqlserver.data_refresh ##
  • More Often: vizqlserver.data_refresh 0

Initial SQL

  • vizqlserver.initialsql.disabled true/false

Servers

This is where you learn how to automatically setup and connect your workers. This means that you can auto-deploy, configure, or reconfigure workers. I’ll show you how to change the configuration, push the configuration, and adjust workers so that they are pointing at the Primary.

Your workers will need to be on the same domain for them to communicate successfully!

Servers

Primary Adjustments - worker0

Unlike the UI, your changes here will just be applied, you may not get an error or warning if you misconfigure (like forget to add a Data Engine or File Store somewhere).

  • VizQL: worker0.vizqlserver.procs #
  • Application Server: worker0.vizportal.procs #
  • Background Server: worker0.backgrounder.procs #
  • Cache Server: worker0.cacheserver.procs #
  • Data Server: worker0.dataserver.procs #
  • Data Engine: worker0.dataengine.procs #
  • File Store: worker0.filestore.enabled true/false
  • Repository: pgsql0.host Machine Name of Primary
  • Search and Browse: worker0.searchserver.procs 1
  • Gateway: worker0.gateway.enabled true/false

workers - worker1 -> workerN

  • Add a Worker: worker.hosts “Primary, IP1, IP2”
  • workerN.XXX.procs [just replace worker0 with workerN]
  • If you want to run a repository on another host: pgsql1.host IP address of worker
  • If you have more than one pgsql repository: pgsql.preferred_host MACHINE NAME

To make sure the Primary and Workers can talk to each other, you need to modify the service on each of the Workers to listen for the primary.

net stop "Tableau Administrative Server"
sc config tabadmsvc binpath= "C:\\Program Files\\Tableau\\Tableau Server\\worker\\admin\\tabadmsvc.exe start --primary IP Address of Primary"
net start "Tableau Administrative Server"

Now that they can communicate, you need to push that configuration out to them. You due this by running tabadmin prep workers.


Alerts and Subscriptions

There’s a little bit of UI drift between 9.2 and 9.3, so I’ll do my best to document the changes. The commands are the same across, but there is an extra tab and a few extra settings available in 9.3.

This is 9.2

92 Alerts

This is 9.3

93alerts

The SMTP information in 9.2 has moved to SMTP Setup in 9.3 to make room for Disk Space Monitoring.

SMTP Setup

  • Send email alerts for server health: svcmonitor.notification.smtp.enabled true/false
  • Enable email subscriptions: subscriptions.enabled true/false

  • SMTP: svcmonitor.notification.smtp.server smtp address
  • Username: svcmonitor.notification.smtp.send_account username
  • Port: svcmonitor.notification.smtp.port ##
  • Password: svcmonitor.notification.smtp.password password
  • Enable TLS?: svcmonitor.notification.smtp.ssl_enabled true/false
  • Send email from: svcmonitor.notification.smtp.from_address email
  • Send email to: svcmonitor.notification.smtp.target_addresses email
  • Tableau Server URL: svcmonitor.notification.smtp.canonical_url TABLEAU SERVER URL

Disk Space Monitoring (9.3 Add-on)

  • Record disk space usage information: storage.monitoring.record_history_enabled true
  • Send alerts when unused drive space drops: storage.monitoring.email_enabled false
  • Warning threshold: storage.monitoring.warning_percent 20
  • Critical threshold: storage.monitoring.critical_percent 10
  • Send email alert every: storage.monitoring.email_interval_min 60

SSL

For SSL to work correctly, you need to put (or push) your certificates into a specific folder. The path is [Drive Letter]:\Program Files\Tableau\Tableau Server\SSL

SSL

External Webserver SSL

FQP = Fully Qualified Path from above

  • SSL Enabled: ssl.enabled true/false
  • SSL Certificate File: ssl.cert.file [FQP to .crt]
  • SSL Certificate Key File: ssl.key.file [FQP to .key]
  • SSL Certificate Chain File: ssl.chain.file [FQP to chain .crt]
  • Mutual SSL and automatic login: ssl.client_certificate_login.required true
  • SSL CA certificate file: ssl.cacert.file [FQP to .crt]

Internal Repository Database ssl

  • Required for all Connections: pgsql.ssl.enabled true && pgsql.ssl.required true
  • Off: pgsql.ssl.enabled false
  • Optional for direct user connections: pgsql.ssl.enabled true

SAML

This section and the next one cannot be fully automated without intervention. Both require a user to run external configuration scripts or functions. For SAML, you can mock up the correct information with a dummy file and do your work ahead of time.

Like SSL, you’ll need a specific file path for placing your XML and certs. This path is [Drive Letter]:\Program Files\Tableau\Tableau Server\SAML

SAML

  • Use SAML for single sign-on: wgserver.authentication.login saml
  • Tableau Server return URL: wgserver.saml.returnurl protocol/url:port

This will declare the following configuration settings wgserver.saml.protocol http/https, wgserver.saml.port port #, wgserver.saml.domain url

  • SAML entity ID: wgserver.saml.entity id
  • SAML Certificate File: wgserver.saml.cert.file [FQP to .crt]
  • SAML Key File: wgserver.saml.key.file [FQP to .key]
  • SAML IdP metadatafile: wgserver.saml.idpmetadata.file [FQP to .xml]

Normally, you would need Tableau to export the metadata file for registration with your identity provider. However, you can generate it yourself, because it’s basically a template. It looks like this:

XML

There is one part of the XML you will have to fill out on your own (or at least do some parsing) - the Certificate. It’s in the section

<md:KeyDescriptor use="encryption">
  <ds:KeyInfo xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    <ds:X509Data>
      <ds:X509Certificate></ds:X509Certificate>
    </ds:X509Data>
  </ds:KeyInfo>
</md:KeyDescriptor>

In the X509Certificate goes your actual certificate, which you can extract by opening up your .crt and pasting it between <ds:X509Certificate></ds:X509Certificate>.

Here is what it looks like:

CRT

Since you can extract that data out, this means you could automate the creation of the XML needed to bind Tableau to your IDP.

Be warned, if your using non-standard claims, like email instead of username, you will have to adjust this through tabadmin set wgserver.saml.idpattribute.username.


Kerberos

kerberos

It is possible to automatically configure Tableau to use Kerberos, but be warned, you have to do some upfront. Kerberos is a subset of the Active Directory authentication function, and enabling it requires you to run a configuration script to create a .keytab file. Once you have this folder, you tell Tableau where it lives and it handles the rest.

A few extra notes

  • The Run As User account (the Tableau Server service account) must be an AD domain account. Local accounts, including NTAUTHORITY\NetworkService will not work.
  • The Run As User account must be in the same domain as the database services that will be delegated.
  • Constrained delegation: The Run As User account must be granted access to the target database Service Principal Names (SPNs).
  • Data Source authentication: If you plan to use Kerberos to authenticate to Microsoft SQL Server or MSAS databases, or with delegation for Single sign-on (SSO) to Cloudera Impala, enable the Run AS User account to act as part of the operating system. For more information, see Enable Run As User to Act as the Operating System.
  • External Load Balancer/Proxy Server: If you are going to use Tableau Server with Kerberos in an environment that has external load balancers (ELBs) or proxy server, you need to set these up before you configure Kerberos in the Tableau Server Configuration utility. See Add a Load Balancer and Configure Tableau to Work with a Proxy Server for more information.

Here’s a shot of the batch script you have to run to start the process, and I’ve reproduced the basics below.

batch

@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/DOMAIN DOMAIN\username
setspn -s HTTP/DOMAIN DOMAIN\username
echo Creating Keytab files in %CD%\keytabs
mkdir keytabs
ktpass /princ HTTP/DOMAIN /pass !adpass! /ptype
KR85_NT_Principal /out keytabs\kerberos.keytab

Once this file has been run, you have this file kerberos.keytab. You have to have this before you can configure Kerberos on Tableau Server. then you point Tableau to the file by running the config command.

If that doesn’t intimidate you, here’s the two commands:

  • Enable Kerberos: wgserver.kerberos.enabled true/false

There is no tabadmin set command for the keytab. Instead, you have to use a very undocumented command.

tabadmin publishkeytab "PATH\TO\KEYTAB\file.keytab"


SAP HANA

HANA

This tab is used to configure SSO for SAP’s super-fast HANA database. Like Kerberos, there is a lot of external work involved to make this happen. Since this isn’t a post about how to configure HANA, I’ll let you go read the docs.

General settings

  • Use SAML to enable single sign-on for SAP HANA: wgserver.sap_hana_sso.enabled true
  • SAML Certificate File and SAML Key File: tabadmin publishhanassofiles –certificate “C:\Program Files\Tableau\Tableau Server\SAML\test.crt” –key “C:\Program Files\Tableau\Tableau Server\SAML\test.der”

tabadmin publishhanassofiles does the heavy lifting for you by altering two lines in workgroup.yml

  • wgserver.sap_hana_sso.saml.cert.file.name hana_cert.pem
  • wgserver.sap_hana_sso.saml.key.file.name hana_pkey_pkcs8.der

Username Format: wgserver.sap_hana_sso.username.format

  • Username only: username
  • Domain name and username: domain_and_username
  • email: email

Username case: wgserver.sap_hana_sso.username.case

  • Preserve Case: preserve
  • Uppercase: upper
  • Lowercase: lower

OpenID

One of the new additions to Tableau 9.3 is the ability to use an OpenID provider (Google and Yahoo being the big fish in that pond) for your authentication and identity provision. In short, it’s another type of SAML.

You’ll need to collect a few things from your provider to complete this section, and this will depend on your provider. OpenID only works with local authentication. Depending on your provider (like if you use Google), you may have to use a full email address.

Make sure your IdP is prepared to accept requests from Tableau Server. You’ll need to get the Client ID, Client Secret (token), and configuration URL. This is very similar to the SAML setup from before, just a little simpler.

openid

  • Use OpenID Connect for SSO: wgserver.authentication.login openid
  • Provider client ID: vizportal.openid.client_id
  • Provider client secret: vizportal.openid.client_secret
  • Provider configuration URL: vizportal.openid.config_url
  • Tableau Server external URL: MACHINE NAME

The last section will generate the URL to bind your Tableau Server to your IdP. To generate it, it takes the following form:

http(s)://EXTERNALURL/vizportal/api/web/v1/auth/openIdLogin

What about the Initial Admin User?

You can automate that too…but not with tabadmin (yet). This can be accomplished via tabcmd. In case you aren’t familiar, tabcmd installs with your Server, so no additional work is required.

  • tabcmd initialuser –username “admin” –password “P@ssword!” –friendly “Tableau Admin”

This has to be run on the primary machine AND if you are using Active Directory, that user must be present in the directory when you run the command. Otherwise, it won’t work.

Tableau 10 Features

Now that Tableau 10 is released (or about to be - depending on when you read this), I’ve updated the section below with some tabadmin commands that you can use to configure Tableau Server to your heart’s desire. The good news is that the standard configuration window hasn’t changed. All those features are exactly as they were in 9.3 (with the exception of SAML, which gets a fancy drop-down instead of a check-box).

  • Desktop License Reporting: tabadmin set features.DesktopReporting true
  • Email Notification of Extract Failure: tabadmin set backgrounder.send_email_on_refresh_failure false
  • Failure Threshold Setting (how many days old an extract can be): tabadmin set wgserver.alerts.observed_days

BONUS FEATURE

For those who don’t know, Paul Banoub is the Tableau Server Master. He recently posted about how to launch Lync sessions from within Tableau workbooks. This is a great collaboration feature that everyone should use. You can read it yourself, but here’s the Tableau Server commands that make it work.

  • tabadmin set vizqlserver.url_scheme_whitelist sip
  • tabadmin set vizqlserver.url_scheme_whitelist im

If you are adventurous, you can substitute Lync (which uses the im protocol), for the one Slack uses.

  • tabadmin set vizqlserver.url_scheme_whitelist slack

In your URL action, you want to use the following syntax

slack://channel?id=[ChannelID]&team=[teamID] - this will launch you into a #channel in the Slack app
slack://user?id=[userID]&team=[teamID] - this will open the user profile for whoever you want to chat with in the Slack app

Due to Slack’s webapp restrictions, you can’t actually launch the web version inside a workbook. You can open it in a separate window though, like so:

https://slackteamname.slack.com/messages/@user

How do you get the ChannelID, UserID, and TeamID?

  1. Get your API token
  2. Query your team’s info
  3. Query your channels’ info
  4. Query your user’s info

You can user Slack’s interface or Postman. If you want to export the list and post it somewhere for other’s to use, I’d recommend Postman.


So…what now?

Now you know everything there is to know about Tableau Server configuration. What can you do with it?

You should absolutely put tabadmin on your path. That will make everything much simpler and you won’t have to type cd Program Files\blah\blah\bin ever again. Once you’ve done that, you can adopt as much DevOps as you would like.

In other words, you can dynamically deploy, modify, scale up, scale out, and upgrade on-the-fly with little to no downtime. AND when Tamas gets around to documenting his dark magic, you’ll probably be rid of downtime for good.

Let’s start with the easy (and free stuff).

Beginner

Do you use Windows? Of course you do, you are a Tableau user! Did you know you can use Powershell to remotely access and run scripts? No?

It’s a built-in feature that lets you write (and schedule) Powershell modules directly from your machine to run on your Tableau Server. You’ll need Admin rights to the Server box - but once you have that, follow these steps and get to automating!

Advanced

Since everything Tableau Server-related can be run via Powershell, now we can start getting a bit crazy. If you are on AWS, you can use a brand-new feature called Run Command. This is a feature for all AWS Windows instances that lets you send commands remotely (think SSH or remote Powershell) without having to log into the box. Ever.

The first way to do this is to access your AWS Console

AWS Console

Once you are there, you can run a command by selecting AWS-RunPowerShellScript and typing in your script.

RunPS

All your activities are logged and you can use the console to check progress and outputs. Read more here


If you like Run Command but want a bit more automation, AWS supports that too (to the surprise of nobody)!

  1. Make sure you have Python installed.
  2. Open your command line and type pip install aws-shell
  3. Start and configure your terminal
$ aws-shell
aws> configure
AWS Access Key ID [None]: your-access-key-id
AWS Secret Access Key [None]: your-secret-access-key
Default region name [None]: region-to-use (e.g us-west-2, us-west-1, etc).
Default output format [None]:
aws>

From your terminal, you use run command by invoking the ssm syntax. Documents are written in JSON and can be created beforehand or will accept Powershell scripts as an input via a parameter.

Let’s say you want to get the status of the Tableau Server before doing anything. You would open your aws-shell and type this:

ssm send-command --instance-ids [LIST YOUR INSTANCE IDS] --document-name AWS-RunPowerShellScript ---Parameter @{'commands'=@('tabadmin status')}

The aws-shell will provide you with autocomplete help along the way.

awsautocomplete

If you want to give it a try follow these instructions.

You can create your own documents to run on some schedule, or create them on the fly.

Jedi

The AWS automation is great, but that’s still a lot of work. What if there was a way to create templates, tests, and schedules and let it run all by itself? You want to be agile and have your infrastructure respond to your needs. You want infrastructure as code, so you can automate everything in a controlled environment.

This is the DevOps dream - and now it is possible for Tableau. Pick your platform, here’s how to make it work.

Use the powershell_script resource to execute Powershell scripts on any system controlled by your Chef Server.

Use the powershell module for executing Powershell commands.

Use the salt.modules.cmdmod with shell='powershell'

Use the PowerShell Packer to run Powershell scripts on Windows machines. It will look something like this

{
  "type": "powershell",
  "inline": ["command"]
}

This works as part of the broader HashiCorp platform for building and managing infrastructure. You can use their tools to deploy across any environment you want, and in any combination!

This one is a little more complicated. Ansible’s method is based on PowerShell remoting, via Python. To run these scripts, you can create them in advance (much like AWS), or use a syntax like so

- name: raw module
  hosts: Windows
  tasks:
    - name: do stuff
    raw: type your commands here

There you have it. Everything you ever wanted to know about how Tableau Server configuration works, how to automate it, and some cool things you can do. The content in this post is current through Tableau 9.3 and will be updated once Tableau 10 hits Beta 4.

As always, thanks for reading and let me know if you have any questions!