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.

[kakarukeys]

please reopen this issue as I don't think @renoyjohnm's commit fixed it. My test showed the same error with latest version.

Versions
Details of your environment, including:

• Tableau Online
• Python 3.11.9
• TSC library version 0.37
• API version 3.25

To Reproduce

(same script)

Results

(same error)

The error occured in the GET request

  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(
           ^^^^^^^^^^^^^^^^^^^

req_options was unexpected to the server and the server has no validation on it before using its content.

in the unit test of #1566, the GET endpoint is assumed to be working for whatever request params we send. In fact no params were sent in the test.

  with requests_mock.mock() as m:
      m.get(f"{self.baseurl}/{vconn.id}/connections", text=populate_connections_xml.read_text())
      vc_out = self.server.virtual_connections.populate_connections(vconn)
      connection_list = list(vconn.connections)

[jorwoods]

My minimal reproducible example was not able to reproduce this error on v0.37. Can you provide a minimal example that does?

import tableauserverclient as TSC
import os
from dotenv import load_dotenv

load_dotenv()

server = TSC.Server(os.environ["TABLEAU_SERVER"], use_server_version=True)
auth = TSC.PersonalAccessTokenAuth(os.environ["TOKEN_NAME"], os.environ["TOKEN_SECRET"], site_id=os.environ["TABLEAU_SITE"])

server.auth.sign_in(auth)

for vc in TSC.Pager(server.virtual_connections):
    print(vc.id, vc.name)
    server.virtual_connections.populate_connections(vc)
    for c in vc.connections:
        print(c.server_address)

[kakarukeys]

@jorwoods I did some debugging. It now looks like a server side issue to me.

The minimal example is just making the GET request to https://prod-apsoutheast-a.online.tableau.com/api/3.25/sites/xxx/virtualConnections/yyy/connections with specific ids (some ids return 200, some 500), the request params I used were nothing more than just pagination params (page = 1, no. of items = 100).

The bug you reproduced with your minimal example was an unrelated bug that occured in the update query not the get query.

I'm glad you found that one and had it fixed. As for mine, I have filed a support ticket to Tableau support in February, and they dismissed it as a coding issue on my side. So no further actions for now, I probably will look for a different product.

[jorwoods]

I don't have a virtual connection with 200 connections, but that said, I did investigate the endpoint. The connections endpoint is indeed paginated, and does return the pagination XML element. I tested passing in a pageNumber and pageSize manually to the server, and received a proper response.

request params I used were nothing more than just pagination params (page = 1, no. of items = 100).

You passed these in manually? Letting TSC do the heavy lift seems to work for me, as well as letting the Pager handle it, as well as manually constructing the URL with parameters. If you are able to produce a minimal reproducible example that demonstrates the error, please open another issue so I can look at it with the example that produces the error.

[kakarukeys]

I don't have a virtual connection with 200 connections, but that said, I did investigate the endpoint. The connections endpoint is indeed paginated, and does return the pagination XML element. I tested passing in a pageNumber and pageSize manually to the server, and received a proper response.

request params I used were nothing more than just pagination params (page = 1, no. of items = 100).

You passed these in manually? Letting TSC do the heavy lift seems to work for me, as well as letting the Pager handle it, as well as manually constructing the URL with parameters. If you are able to produce a minimal reproducible example that demonstrates the error, please open another issue so I can look at it with the example that produces the error.

sorry I meant, for some ids, the GET request to
https://prod-apsoutheast-a.online.tableau.com/api/3.24/sites/xxx/virtualConnections/<id>/connections
returned HTTP 200 with proper response, for other ids, the GET request returned HTTP 500 (see my very first post for the error message).

I was using TSC library with local modifications to do the debugging, internally it was sending HTTP requests with requests or urllib3. It seems to me it was a server side issue.

I believe when a user started creating a VC, but never fished the creation, Tableau auto-saved the VC as a "draft" although it could be without any info. When we had these objects, the query returned HTTP 500. After deleting the problematic VCs from tableau cloud admin UI, I could use TSC library to update the connections of the rest without issues.

If you handle server-side bugs as well, unfortunately, I no longer have those deleted VCs to isolate the bug further, but will raise an issue if I encounter it again.