How to Install Trusted Certificate on Mac

With new Tableau Server and Desktop certificate validation happens on Tableau side as explained in Tableau and Trusted Certificates for Analytics Extensions post. So for a certificate to be trusted either the certificate itself (self-signed certificate scenario) or certificate(s) it signed with have to be installed on the client machine as trusted.

This post demonstrates how to install certificate as trusted on Mac OS.

NOTE: This post is just an example and shouldn’t be used as a manual. The steps and UI can be different for your OS version and how it is configured.

First step would be to download the certificate you want to install as trusted on your computer. Remember for Rserve you may need to install the whole chain.

Then start Keychain Access application (Finder -> Applications -> Utilities):

In the app go to System Keychains, then Certificates, and drag and drop the certificate you want to install there.

You will see a message about newly installed certificate to be not trusted:

Right-click the certificate and select Get Info menu item:

In the dialog which appears for the certificate information open Trust section and set Secure Sockets Layer (SSL) option to Always Trust. Close the window and confirm the red icon and warning message for the certificate is gone.

Next select New Certificate Preference… item in the context menu for the certificate:

And in the pop-up dialog enter the exact fully qualified domain name (FQDN) the certificate is issued for. Click Add button.

Now the certificate is installed as trusted and for it to be validated and accepted it is recommended to reboot the machine. In general most of certificates modifications on a machine are recommended to have following reboot.

It may be enough (but not guaranteed to be) to restart Tableau Desktop. For how to configure secure connection in Tableau Desktop read Tableau Desktop 2020.1: Advanced Analytics Improvements and How to configure TabPy with authentication and use it in Tableau.

Tableau Desktop 2020.1: Advanced Analytics Improvements

With Tableau Desktop 2020.1 released (https://www.tableau.com/support/releases/desktop/2020.1) there are plenty of improvements and new features. But for the purpose of this blog let’s take a look at External Services the most significant change.

The change is for how secure connection is created. When configuring a connection with main menu Help, Settings and Performance, Manage External Service Connection… here’s how new configuration dialog looks like:

What is different there is no link to specify a certificate for secure connection – there’s just Require SSL option.

To compare this is how the same dialog looks in Tableau Desktop 2019.4:

How certificate validation works now is instead of comparing server (TabPy, Rserve, etc.) certificate with user-provided certificate Tableau validates the server certificate and trusts it only if it is installed as a trusted cert on the client OS or if it is signed with a trusted cert.

The server certificate can be signed with an intermediate certificate that can be signed with another certificate and so on until there’s a root certificate Tableau can trust.

For self-signed certificates (those which are not signed by any other certificate) they should be installed as trusted as well.

For how to install a certificate and make it trusted read documentation for your OS (the steps are very different for each OS).

NOTE: Rserve only sends leaf certificate and not the whole chain (there’s an issue opened for that – https://github.com/s-u/Rserve/issues/140) which makes it impossible to validate the certificate on the Tableau side unless the whole chain is installed as trusted.

Documentation for this new UI and behavior is at https://help.tableau.com/current/pro/desktop/en-us/r_connection_manage.htm page.

How to configure TabPy with authentication and use it in Tableau

Intro

In this post, I will demonstrate how to configure TabPy to require username and password, how to manage users for TabPy and how to connect from Tableau (both Desktop and Server) to TabPy with specifying credentials.

User Management for TabPy

After TabPy is installed (read Tableau Installation Instructions for how to install it) you can use tabpy-user command-line utility for adding and updating TabPy user accounts.

The utility itself supports a set of parameters such as a path to passwords file, operation, username and so on. Run tabpy-user -h to see all the parameters.

Adding a User

To add a user specify a username, a path to passwords file, password and add operation, e.g.:

c:\TabPy>tabpy-user add -u alice -f c:\TabPy\tabpypwd.txt -p P@ssw0rd
Parsing passwords file c:\TabPy\tabpypwd.txt...
Passwords file c:\TabPy\tabpypwd.txt not found
Adding username "alice"
Adding username "alice" with password "P@ssw0rd"...
Added username "alice" with password "P@ssw0rd"

In the example above the user alice with password P@ssw0rd was added to file c:\TabPy\tabpypwd.txt.

You can also let the utility generate a password for you simply skipping -p <Password> parameter:

c:\TabPy>tabpy-user add -u bob -f c:\TabPy\tabpypwd.txt
Parsing passwords file c:\TabPy\tabpypwd.txt...
Found username alice
Authentication is enabled
Generated password: ")7!f}dA_K=hrF7{x"
Adding username "bob"
Adding username "bob" with password ")7!f}dA_K=hrF7{x"...
Added username "bob" with password ")7!f}dA_K=hrF7{x"

In the example above new user bob was added to the same password file with password )7!f}dA_K=hrF7{x.

Updating User Password

It is possible to update the password for a user with update command, e.g.:

c:\TabPy>tabpy-user update -u alice -f c:\TabPy\tabpypwd.txt -p Secret_D0nt_Te11
Parsing passwords file c:\TabPy\tabpypwd.txt...
Found username alice
Found username bob
Authentication is enabled
Updating username "alice"
Updating username "alice" password  to "Secret_D0nt_Te11"

In this example alice‘s password was changed to Secret_D0nt_Te11.

What’s Inside Passwords File?

The password file is just a text file with user names and hashed passwords on each line. If you open the file you will see something like this:

alice edb6473a71775f48538c1cee15dc41269302b06b79260c70ce149d1b24a4192f764570702d5449fa2712c0a99d0db9216c1a452f07a3a3b44dca1b491cd7d516
bob 7716853bdc91132fe4bef86adaac0ae6fa9cf474c5b075e89880fcd21834d2bb16266eb65d0be0a8faa2ee48342708350b95af4af3caebbb8044f59341fcfab6

Those long codes are actually the hashes for the passwords. Instead of keeping passwords in plain text or encrypted form TabPy uses hashes. What it does is makes it impossible (rather incredibly expensive) to recover passwords from those hashes. If you wonder how the passwords are hashed – at the moment TabPy uses PBKDF2 method with 10000 iterations (https://en.wikipedia.org/wiki/PBKDF2).

Deleting a User

Since the password file is just a text file you can delete a user with any text editor simply deleting the whole line with the user name in it.

Configuring TabPy with Authentication

Now when you have the passwords file you can point TabPy to it so it knows to require credentials with all the requests to serve.

NOTE: any changes for the password file do not affect any running instances of TabPy – you will need to restart TabPy for the changes to take effect.

In previous post TabPy: modifying default configuration it was shown how to changes some (or all) TabPy configuration parameters with a config file. Let’s create a config file to turn on authentication as well. I am storing the following configuration in c:\TabPy\tabpy_auth.conf:

[TabPy]
TABPY_PWD_FILE = c:\TabPy\tabpypwd.txt

As you can see the only configuration parameter I am modifying there is the password file path. In real-life scenarios, you will have logger settings, port, timeout and any other of the parameters documented at TabPy Custom Settings page.

Now let’s start TabPy with the config:

c:\TabPy>tabpy --config c:\TabPy\tabpy_auth.conf
...
DEBUG:tabpy.tabpy_server.app.app:Parameter TABPY_PWD_FILE set to "c:\TabPy\tabpypwd.txt" from config file or environment variable
INFO:tabpy.tabpy_server.app.util:Parsing passwords file c:\TabPy\tabpypwd.txt...
DEBUG:tabpy.tabpy_server.app.util:Found username alice
DEBUG:tabpy.tabpy_server.app.util:Found username bob
INFO:tabpy.tabpy_server.app.util:Authentication is enabled
...
INFO:tabpy.tabpy_server.app.app:Web service listening on port 9004

TabPy is running with authentication on!

Connecting from Tableau

For Tableau to communicate with TabPy when credentials are required you need to configure the product with username and password. As mentioned at TabPy Authentication page basic authentication is used at the moment (https://en.wikipedia.org/wiki/Basic_access_authentication) which means username (login) and password sent with each HTTP request to TabPy. This is why it is highly recommended to use a secured communication channel rather than plain text. For how to configure secure connection read Configuring HTTP vs HTTPS documentation page.

Tableau Desktop

For Tableau Desktop go to the main menu, Help, Settings and Performance, Manage External Service Connection. The screenshots below are for Tableau 2019.4.2:

Set connection type to be TabPy/External API, enter Server (host) and Port for your TabPy instance (localhost and 9004 on the screenshot below), set check mark for Sign with username and password and enter credentials for a user:

To confirm the credentials are valid click Test Connection button and popup message with confirming success (or failure details) will show:

Tableau Server

To configure Tableau Server connection to TabPy with authentication follow instructions at TSM Security page. At the moment this post is being written the latest available version of Tableau Server is 2019.4.2 and the steps will be setting up a connection and applying the changes:

c:\user\admin>tsm security vizql-extsvc-ssl enable --connection-type tabpy --extsvc-host my_tabpy_server --extsvc-port 9004 --extsvc-username alice --extsvc-password Secret_D0nt_Te11
...

c:\user\admin>tsm pending-changes apply