Internal server error 500 getting the list of connections of a virtual connection

[kakarukeys]

Describe the bug
getting the list of connections of a virtual connection throws an internal server error 500

Versions
Details of your environment, including:

• Tableau Online
• Python 3.11.9
• TSC library version 0.35
• API version 3.24

To Reproduce

import os
import random
from itertools import islice

import tableauserverclient as TSC


SITE_ID = "xxx"
SERVER_URL = "https://prod-apsoutheast-a.online.tableau.com"


def gen_connections(endpoint, connection_username=None):
    for obj in TSC.Pager(endpoint):
        endpoint.populate_connections(obj)

        for conn in obj.connections:
            if conn.connection_type == "athena" and (connection_username is None or conn.username == connection_username):
                yield endpoint, obj, conn


def update_keys(new_iam_access_key, new_iam_secret_key, endpoint, parent_obj, connection):
    print(f"updating to {new_iam_access_key}: {parent_obj},\n {connection}")

    connection.username = new_iam_access_key
    connection.password = new_iam_secret_key

    endpoint.update_connection(parent_obj, connection)


def rotate_keys(
    server,
    old_iam_access_key,
    new_iam_access_key,
    new_iam_secret_key,
):
    for i in range(3):
        all_items = [
            item
            for endpoint in (
                server.virtual_connections,
            )
            for item in gen_connections(endpoint, connection_username=old_iam_access_key)
        ]

        if not all_items:
            break
        elif i == 2:
            raise Exception("unable to finish the rotation task after 2 tries")

        random.Random(27).shuffle(all_items)

        for endpoint, parent_obj, connection in all_items:
            update_keys(new_iam_access_key, new_iam_secret_key, endpoint, parent_obj, connection)
            yield


if __name__ == "__main__":
    TABLEAU_PAT_TOKEN_NAME = os.getenv("TABLEAU_PAT_TOKEN_NAME")
    TABLEAU_PAT_TOKEN_VALUE = os.getenv("TABLEAU_PAT_TOKEN_VALUE")

    OLD_IAM_ACCESS_KEY = os.getenv("OLD_IAM_ACCESS_KEY")
    NEW_IAM_ACCESS_KEY = os.getenv("NEW_IAM_ACCESS_KEY")
    NEW_IAM_SECRET_KEY = os.getenv("NEW_IAM_SECRET_KEY")

    try:
        NUM = int(os.getenv("NUM")) or None
    except (TypeError, ValueError):
        NUM = None

    tableau_auth = TSC.PersonalAccessTokenAuth(TABLEAU_PAT_TOKEN_NAME, TABLEAU_PAT_TOKEN_VALUE, site_id=SITE_ID)
    server = TSC.Server(SERVER_URL, use_server_version=True)

    with server.auth.sign_in(tableau_auth):
        tasks = rotate_keys(server, OLD_IAM_ACCESS_KEY, NEW_IAM_ACCESS_KEY, NEW_IAM_SECRET_KEY)
        list(islice(tasks, NUM))

Results

Traceback (most recent call last):
  File "/Users/xxx/projects/yyy/update_key_v2.py", line 78, in <module>
    list(islice(tasks, NUM))
  File "/Users/xxx/projects/yyy/update_key_v2.py", line 38, in rotate_keys
    all_items = [
                ^
  File "/Users/xxx/projects/yyy/update_key_v2.py", line 38, in <listcomp>
    all_items = [
                ^
  File "/Users/xxx/projects/yyy/update_key_v2.py", line 17, in gen_connections
    for conn in obj.connections:
  File "/Users/xxx/Library/Caches/pypoetry/virtualenvs/yyy-eWfleEIf-py3.11/lib/python3.11/site-packages/tableauserverclient/server/pager.py", line 80, in __iter__
    current_item_list, pagination_item = self._endpoint(options)
                                         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/xxx/Library/Caches/pypoetry/virtualenvs/yyy-eWfleEIf-py3.11/lib/python3.11/site-packages/tableauserverclient/server/endpoint/virtual_connections_endpoint.py", line 49, in _get_virtual_database_connections
    server_response = self.get_request(f"{self.baseurl}/{virtual_connection.id}/connections", req_options)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/xxx/Library/Caches/pypoetry/virtualenvs/yyy-eWfleEIf-py3.11/lib/python3.11/site-packages/tableauserverclient/server/endpoint/endpoint.py", line 204, in get_request
    return self._make_request(
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/xxx/Library/Caches/pypoetry/virtualenvs/yyy-eWfleEIf-py3.11/lib/python3.11/site-packages/tableauserverclient/server/endpoint/endpoint.py", line 141, in _make_request
    self._check_status(server_response, url)
  File "/Users/xxx/Library/Caches/pypoetry/virtualenvs/yyy-eWfleEIf-py3.11/lib/python3.11/site-packages/tableauserverclient/server/endpoint/endpoint.py", line 159, in _check_status
    raise InternalServerError(server_response, url)
tableauserverclient.server.endpoint.exceptions.InternalServerError: 

Internal error 500 at https://prod-apsoutheast-a.online.tableau.com/api/3.24/sites/xxx/virtualConnections/yyy/connections
b'<?xml version=\'1.0\' encoding=\'UTF-8\'?><tsResponse xmlns="http://tableau.com/api" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://tableau.com/api https://help.tableau.com/samples/en-us/rest_api/ts-api_3_24.xsd"><error code="500000"><summary>Internal Server Error</summary><detail>The server encountered an error and cannot complete your request. Contact your server administrator.</detail></error></tsResponse>'

[kakarukeys]

There was a request to support Virtual Connections in TSC here, and a PR was merged here. So this bug is a regression.

[jorwoods]

This was challenging to troubleshoot, so leaving some notes here. First, setup resources in AWS using Terraform.

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.86.0"
    }
  }

  required_version = ">= 1.9"
}

provider "aws" {
  region = "us-west-2"
}

variable "prefix" {
  type        = string
  description = "Prefix for resources"
}

resource "local_file" "this" {
  content  = "col1,col2\nval1,val2"
  filename = "example.csv"
}

resource "aws_s3_bucket" "this" {
  bucket        = replace("${var.prefix}_bucket", "_", "-")
  force_destroy = true
}

resource "aws_s3_object" "this" {
  bucket = aws_s3_bucket.this.bucket
  key    = "input/${local_file.this.filename}"
  source = local_file.this.filename
}

resource "aws_glue_catalog_database" "this" {
  name = "${var.prefix}_db"
}

resource "aws_glue_catalog_table" "this" {
  catalog_id    = aws_glue_catalog_database.this.catalog_id
  database_name = aws_glue_catalog_database.this.name
  name          = "${var.prefix}_table"
  parameters = {
    "classification" = "csv"
  }
  table_type = "EXTERNAL_TABLE"

  storage_descriptor {
    compressed    = false
    input_format  = "org.apache.hadoop.mapred.TextInputFormat"
    location      = "s3://${aws_s3_bucket.this.bucket}/${dirname(aws_s3_object.this.key)}"
    output_format = "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"

    columns {
      name = "col1"
      type = "string"
    }
    columns {
      name = "col2"
      type = "string"
    }

    ser_de_info {
      parameters = {
        "separatorChar" = ","
      }
      serialization_library = "org.apache.hadoop.hive.serde2.OpenCSVSerde"
    }
  }
}

resource "aws_athena_workgroup" "this" {
  name = var.prefix
  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = false
    result_configuration {
      output_location = "s3://${aws_s3_bucket.this.bucket}/output"
    }
  }
}

data "aws_caller_identity" "this" {}

data "aws_region" "current" {}

data "aws_iam_policy_document" "this" {
  statement {
    actions = [
      "athena:StartQueryExecution",
      "athena:StartSession",
      "athena:StopQueryExecution",
      "athena:Get*",
      "athena:List*",
      "athena:RunQuery",
      "glue:BatchGet*",
      "glue:Get*",
      "glue:List*",
      "glue:TestConnection",
      "glue:CreateConnection",

    ]
    resources = [
      aws_glue_catalog_database.this.arn,
      aws_glue_catalog_table.this.arn,
      aws_athena_workgroup.this.arn,
      "arn:aws:athena:${data.aws_region.current.name}:${data.aws_caller_identity.this.account_id}:workgroup/*",
      "arn:aws:athena:${data.aws_region.current.name}:${data.aws_caller_identity.this.account_id}:DataCatalog/*",
      "arn:aws:glue:${data.aws_region.current.name}:${data.aws_caller_identity.this.account_id}:catalog",
    ]
  }

  statement {
    actions = [
      "s3:Get*",
      "s3:List*",
      "s3:Put*",
    ]
    resources = [
      aws_s3_bucket.this.arn,
      "${aws_s3_bucket.this.arn}/*",
    ]
  }
}

resource "aws_iam_user" "this" {
  name = var.prefix
}

resource "aws_iam_user_policy" "this" {
  name   = var.prefix
  user   = aws_iam_user.this.name
  policy = data.aws_iam_policy_document.this.json
}

resource "aws_iam_access_key" "this" {
  user = aws_iam_user.this.name
}

output "ak" {
  value = aws_iam_access_key.this.id
}

output "sk" {
  sensitive = true
  value     = aws_iam_access_key.this.secret
}

output "athena_server" {
  value = "athena.${data.aws_region.current.name}.amazonaws.com:443;Workgroup=${aws_athena_workgroup.this.name}"
}

output "s3_output" {
  value = aws_athena_workgroup.this.configuration[0].result_configuration[0].output_location
}

output "glue_table" {
  value = aws_glue_catalog_table.this.name
}

output "region" {
  value = data.aws_region.current.name
}

Use the access key and secret key created to populate the virtual connection on the server. Connections can only be created via the Web UI, and not the REST API, so I don't have an example of that. I also named it "bug" so I could find it easier during the script. Then rotate the keys, create the json, and run the python script.

terraform taint aws_iam_access_key.this
terraform plan -out tfplan
terraform apply tfplan
terraform output -json > keys.json

Then run the python script:

#/// script
# requires_python = ">=3.9"
# dependencies = ["tableauserverclient==0.35", "python-dotenv"]
#///
import json
import os
from pathlib import Path

from dotenv import load_dotenv
import tableauserverclient as TSC

load_dotenv()

def update_environment() -> None:
    with (Path(__file__).parent / "keys.json").open() as f:
        keys = json.load(f)
        for k,v in keys.items():
            os.environ[k] = v["value"]
    
def main() -> None:
    server = TSC.Server(os.getenv("TABLEAU_SERVER"), use_server_version=True)
    auth = TSC.PersonalAccessTokenAuth(
        token_name=os.environ["TOKEN_NAME"],
        personal_access_token=os.environ["TOKEN_SECRET"],
        site_id=os.environ["TABLEAU_SITE"]
    )

    with server.auth.sign_in(auth):
        for vc in TSC.Pager(server.virtual_connections):
            if vc.name != 'bug':
                continue
            server.virtual_connections.populate_connections(vc)
            for conn in vc.connections:
                conn: TSC.ConnectionItem
                if conn.connection_type != 'athena':
                    continue
                print(f"{conn.server_address}:{conn.server_port}")
                target = conn

            target.username = os.environ["ak"]
            target.password = os.environ["sk"]
            print(target)
            conn = server.virtual_connections.update_connection_db_connection(vc, target)
            print(conn)


if __name__ == "__main__":
    update_environment()
    main()

The failure happens during the updated process as the ConnectionItem did not have its id, server, or port attributes set correctly for VirtualConnections. The 500 error was because of Tableau Server receiving those required attributes as null.

[kakarukeys]

The 500 error was because of Tableau Server receiving those required attributes as null.

If that is the case, shouldn't it be a 400 error?

[jorwoods]

I believe its because the server side doesn't validate whether or not you sent a connection id. It retrieves it from that path. It doesn't make sense for you to "update" the connection with the ID of None.