Tips for running scripts with TSM on Windows

Version 3

    Tableau Services Manager, the new way to engage with Tableau Server, replaces TabAdmin with version 2018.2+ on Windows. This replacement of TabAdmin has works a bit differently than admin and has some additional challenges that TabAdmin didn't have. Because TSM runs as a service on Tableau Server rather than as a standalone command, it's requirements are a bit different, especially in regards to automation with TSM.

     

    Running TSM with the CALL Command in Batch Files

    TSM is itself a batch file(technically, a cmd file, which is an updated batch file specification) and requires any commands to be run with the CALL command. Without CALL, the batch file will transfer execution to TSM and any further commands will be ignored. For more information, see this Third Party document on the CALL command. https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/call

     

    Powershell scripts do not have this requirement and you may want to use powershell scripts for automating tasks for security purposes discussed below.

     

    Logging in for TSM

    Because Tableau Services Manager now runs as service, interfacing with it requires sending the credentials of an administrator account to Tableau Services Manager through one of two methods. If the user running the script can login and thus has a user directory on the server, you can run the 'tsm login' command to enable a session and then use that session for any other tsm commands you'd like to run in your script. Note, the user running the script can be a non-administrator user but the credentials used to login must be those of an administrator. The session is recorded in the user directory of the account that is running the script and doesn't need to be referenced after the 'tsm login' command.

     

    The second way to authenticate to Tableau Services Manager is to add the '-u username' and '-p password' arguments on each command. All tsm commands allow the '-u' and '-p' arguments so that you can easily run a tsm command without requiring a session.

     

    See the following for more information on Logging in with TSM: https://onlinehelp.tableau.com/current/server/en-us/tsm_overview.htm#authentication

    Command reference for 'tsm login' command: https://onlinehelp.tableau.com/current/server/en-us/cli_login.htm

     

    Securing Login Credentials for TSM using Powershell

    With the introduction of TSM and it's replacement of TabAdmin, it becomes more difficult to secure credentials for login to TSM as keeping passwords in clear text in your scripts will often violate security standards. Luckily, Microsoft Windows has a means to mitigate much of the security issue by creating encrypted login credentials via DPAPI(Data Protection Application Programming Interface) and Powershell. You can also secure those credentials via an AES encryption routine although the encryption key and credentials must both be readable and the combination creates a security through obscurity scenario which does not adequately protect your information from someone who has both files. If you do decide to use this method, please keep the files in two different unrelated directories and try to lock down any permission to those files to only be readable by the intended account. This is the only way to secure your credentials if you are using an account that does not have permission to login to the machine running Tableau Server or if you are running the scripts on a machine other than the host for Tableau Services Manager.

     

    Running Powershell Scripts as a Scheduled Task

    Unlike batch(.bat) scripts, Powershell scripts(.ps1 files) can't be run directly from Task Scheduler. To schedule a Powershell script to run periodically, you will need to instead call powershell with the "-file <path-to-file>" argument. I've added a link to the Additional Sources below that explains this in more detail.

     

    Creating Login Credentials

    The first step to making this as secure as possible is to create an encrypted authentication credential. There are two ways to do this, using DPAPI, which is the most secure version, and using AES Encryption. Both methods are displayed below.

     

    Using DPAPI

    To create the credentials file for use with Tableau Server you will need to create a Powershell script(.ps1) to run the commands. To start, login with the user that you want to run the commands. As previously stated, this user does not have to be an administrator but the credentials that login to Tableau Services Manager must be. Once logged on, you should create a directory to hold the credentials. An unshared directory under C:\Users with the non-administrator login, BackupAdmin, is used in my example below. This keeps the credentials file hidden from non administrator accounts.

     

    I create the file, setCredentials.ps1, in notepad in the same directory that I intend to put the files. You can delete this script when you have your credentials. Note the FilePath variable will need to be adjusted to your needs. I try to note those variables that need to be updated in the comments for each cmdlet.

     

    Contents of createCredential.ps1:

    <#

    FilePath should be changed to an appropriate file path for your use. The directory should be created and the current user

    needs access to write to the directory. Preferences are that this is an unshared file within the user directory structure.

    The directory must exist beforehand so please create any directory structure that you want to use.

    #>

    $FilePath = "c:\Users\BackupAdmin\.tableau\cred.sec"

    $Credential = Get-Credential

    $Credential.Password | ConvertFrom-SecureString | Set-Content $FilePath

     

    Running this script will generate a poup credential window for you to set your login credentials for an account on the machine. It then creates a file called cred.sec in C:\Users\BackupAdmin\.tableau or whatever directory and filename you specify in setCredentials.ps1. Feel free to delete this script when you are done. This credentials file can now be used in any scripts run by this user. If you'd like to use a credential file for another user, you'll need to repeat this step as DPAPI will use a different encryption key depending on the user.

     

    Using AES Encryption(not recommended)

    Caution: As previously mentioned, using AES encryption requires a readable key by the same account that reads the credentials file and could create a possible security violation by using security by obscurity. This does, however, make the credentials portable between users and even computers. This is very similar to the above but you will need to first create a readable AES key file. It is suggested that these files not be held together but in different directory trees as keeping them together slightly increases the likelyhood that someone getting the credentials file will also have the key file. Again, this is not so much protection as it is a minor deterrent.

     

    Contents of createCredentialWithAES.ps1

    <#

    Change $KeyPath to an appropriate directory. Preferably one in a different directory stree than the User directory.

    This key can be used by anyone to decrypt the credentials file so using every means to secure it is recommended.

    The directory must exist beforehand so please create any directory structure that you want to use.

    #>

    $KeyPath = "C:\Users\BackupAdmin\.tableau\tableau.key"

    $AESKey = New-Object Byte[] 32

    [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($AESKey)

    $AESKey | out-file $KeyPath

     

    <#

    FilePath should be changed to an appropriate file path for your use. The directory should be created and the current user

    needs access to write to the directory. Preferences are that this is an unshared file within the user directory structure.

    The directory must exist beforehand so please create any directory structure that you want to use. The .tableau

    directory is created the first time you run 'tsm login' so it's a good place to put the credentials once that

    directory is created.

    #>

    $FilePath = "c:\Users\BackupAdmin\.tableau\credEnc.sec"

    $Credential = Get-Credential

    $Credential.Password | ConvertFrom-SecureString -Key $AESKey | Set-Content $FilePath

     

    Running this script will create two files. The credential file 'credEnc.sec' and the AES Key, tableau.key and can be used in scripts similar to the DPAPI version. You need to read and include the key and then use it to decrypt the password with an additional key argument.

     

    Using Credentials in a Script

    Now that the credentials files are created, they can be used to automate tasks in powershell.

     

    The below script shows a scenario where the script logs into Tableau Services Manager with a session and then continues to get the current status of Tableau Server services. You can similarly use the '-u' and '-p' on any command instead of using a session with 'tsm login' but using 'tsm login' method uses the password less for any scripts that requires multiple tsm actions or for an account that does not have a writable user directory.

     

     

    Contents of readCredentialFile.ps1:

    <#

        FilePath should be changed to an appropriate file path for your use. The directory should be created and the current user

        needs access to write to the directory. Preferences are that this is an unshared file within the user directory structure.

        The directory must exist beforehand so please create any directory structure that you want to use. The .tableau

        directory is created the first time you run 'tsm login' so it's a good place to put the credentials once that

        directory is created.

    #>

    $FilePath = "c:\Users\BackupAdmin\.tableau\cred.sec"

    <#

    Change Username appropriately, remembering to add the correct Domain and a \ to the username

    #>

    $Username = "DOMAIN\AdminUser"

     

    $encryptedCred = Get-Content $FilePath | ConvertTo-SecureString

    $cred = New-Object System.management.Automation.PsCredential($Username, $encryptedCred)

    <#

        At this point, you can substitute the following for the password in any tsm command

        $cred.GetNetworkCredential().Password

    #>

    $Pass = $cred.GetNetworkCredential().Password

    tsm login -u "$Username" -p "$Pass"

    $Pass = ""

    tsm status -v

     

    To do the same thing with the AES encrypted credential, use this syntax:

     

     

    Contents of readCredentialFileWithAES.ps1

    <#

        Change $KeyPath to an appropriate directory. Preferably one in a different directory stree than the User directory.

        This key can be used by anyone to decrypt the credentials file so using every means to secure it is recommended.

        The directory must exist beforehand so please create any directory structure that you want to use.

    #>

    $KeyPath = "C:\Users\BackupAdmin\.tableau\tableau.key"

    <#

        FilePath should be changed to an appropriate file path for your use. The directory should be created and the current user

        needs access to write to the directory. Preferences are that this is an unshared file within the user directory structure.

        The directory must exist beforehand so please create any directory structure that you want to use. The .tableau

        directory is created the first time you run 'tsm login' so it's a good place to put the credentials once that

        directory is created.

    #>

    $FilePath = "c:\Users\BackupAdmin\.tableau\credEnc.sec"

    <#

    Change Username appropriately, remembering to add the correct Domain and a \ to the username

    #>

    $Username = "Domain\AdminUser"

     

    $AESKey = Get-Content $KeyPath

    $encryptedCred = Get-Content $FilePath | ConvertTo-SecureString -Key $AESKey

    $cred = New-Object System.management.Automation.PsCredential($Username, $encryptedCred)

    <#

        At this point, you can substitute the following for the password in any tsm command

        $cred.GetNetworkCredential().Password

    #>

    $Pass = $cred.GetNetworkCredential().Password

    tsm login -u "$Username" -p "$Pass"

    $Pass = ""

    tsm status -v

     

    Logging Into TSM without Setting a Variable

    Some applications may require that no unencrypted password is held in memory. If you omit the -p password option on the command line, it will prompt for a password, which is why scripts without a password on the command line will appear to stall. But this can work to your advantage. Additionally without the -u username argument, it will assume the current user as reported by windows, so if you are running the script on the account that you'd like to be running the command with, you can omit it. If I'm running the above script and I want to omit the $Pass step, I can use echo and pipe to pass it through to the prompt. It will also make it quite a bit cleaner as can be seen below. This script doesn't show any identifying information so is also a bit more secure.

     

    Contents of readCredentialFileNoVar.ps1

        <#

            FilePath should be changed to an appropriate file path for your use. The directory should be created and the current user

            needs access to write to the directory. Preferences are that this is an unshared file within the user directory structure.

            The directory must exist beforehand so please create any directory structure that you want to use. The .tableau

            directory is created the first time you run 'tsm login' so it's a good place to put the credentials once that

            directory is created.

     

            Note: I've replaced the username variable in this file with the $env:username environment variable which should be set

            by the system.

        #>

        $FilePath = "$env:userprofile\.tableau\cred.sec"

     

        $encryptedCred = Get-Content $FilePath | ConvertTo-SecureString

        $cred = New-Object System.management.Automation.PsCredential($env:username, $encryptedCred)

     

        echo $cred.GetNetworkCredential().Password | tsm status -v

     

    Following these instruction should help you create scripts to automate Tableau Server more securely with Powershell and credentials files that interact with Tableau Services Manager.

     

    Additional Resources:

    Powershell Scripting: https://docs.microsoft.com/en-us/powershell/scripting/powershell-scripting?view=powershell-6

    TSM Reference: https://onlinehelp.tableau.com/current/server/en-us/tsm.htm

    Powershell Reference Get-Credential: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/get-credential?view=powershell-6

    Powershell Reference ConvertTo-SecureString: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/convertto-securestring?view=powershell-6

    Powershell Reference ConvertFrom-SecureString: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.security/convertfrom-securestring?view=powershell-6

    Migrate from Tabadmin to the TSM CLI: https://onlinehelp.tableau.com/current/server/en-us/tabadmin_to_tsm_cli.htm

    Password Encryption For TABCMD: https://community.tableau.com/docs/DOC-5423

    Windows Data Protection API: https://msdn.microsoft.com/en-us/library/ms995355.aspx

    Weekend Scripter: Use the Windows Task Scheduler to Run a Powershell Script: Weekend Scripter: Use the Windows Task Scheduler to Run a Windows PowerShell Script – Hey, Scripting Guy! Blog