switch datasource connection from oAuth to username / password

[yxl1108]

Describe the bug
have published datasource (PDS) using embedded oAuth authentication to database. Want to use TSC to switch those to use user name / password

    for connection in datasource_connections:

        new_conn_cred = TSC.ConnectionCredentials(
            name = conn_username,
            password = conn_pwd,
            embed = True,
            oauth = False
        )
        connection.username = conn_username
        connection.password = conn_pwd
        connection.embed_password = True
        connection.connection_credentials = new_conn_cred

        server.datasources.update_connection(datasource_items[0], connection)

the above code snippet ran fine, but extract refresh failed with "Authentication failed." error.

Downloaded the tds xml file and found
<connection authentication='oauth'
which should be
<connection authentication='Username Password'

Versions
Details of your environment, including:

• Tableau Server version (or note if using Tableau Online) 2021.1.4 (20211.21.0712.0907)
• Python version 3.6.10
• TSC library version 0.16.0

To Reproduce
See description

Results
Authentication failed.

NOTE: Be careful not to post user names, passwords, auth tokens or any other private or sensitive information.

[aru94]

I could see in the documentation that TSC.ConnectionCredentials class is used for workbook and data source publish requests.
In that case, the parameter set for new_conn_cred wouldn't work, right?
Correct me if I am wrong.
I am looking for a solution to switch from user name & password to an OAuth connection.

[jacalata]

Unfortunately this change (to/from oAuth) isn't currently supported through the API. You might be able to do it using the document api to edit the xml of the downloaded file, if you're willing to try that.

[neozenith]

I am experiencing the same problem simply replacing the password of a Snowflake user that already had a password set. Trying to build out a password rotation automater.

Steps:

1. I update the password of the Snowflake user in the console to a new known value.

2. Then run pretty similar code to above which is referenced in the sample code:
   

   server-client-python/samples/update_connection.py

   Lines 40 to 54 in 777db4e

   	tableau_auth = TSC.PersonalAccessTokenAuth(args.token_name, args.token_value, site_id=args.site)
   	server = TSC.Server(args.server, use_server_version=True)
   	with server.auth.sign_in(tableau_auth):
   	endpoint = {"workbook": server.workbooks, "datasource": server.datasources}.get(args.resource_type)
   	update_function = endpoint.update_connection
   	resource = endpoint.get_by_id(args.resource_id)
   	endpoint.populate_connections(resource)
   	connections = list(filter(lambda x: x.id == args.connection_id, resource.connections))
   	assert len(connections) == 1
   	connection = connections[0]
   	connection.username = args.datasource_username
   	connection.password = args.datasource_password
   	connection.embed_password = True
   	print(update_function(resource, connection).__dict__)

3. The TSC info logs return:

INFO:TSC:Updated datasource item (ID: <datasource GUID redacted> & connection item <connection item GUID redacted>

4. Check in Tableau Cloud Online editor if triggering a refresh on the datasource uses the correct credentials.
5. It has not updated the credentials and fails to make a connection.
6. Perform this enough times and I can lock the Snowflake user for bad password attemps.

When looking at the Chrome Dev Tools > Network tab and manually changed the password on the connection in the console I saw it made calls to undocuments API calls generatePublicKey and within updateConnections there is undocumented parameters encryptedPassword and encryptedPasswordKeyId.

{
    "method":"updateConnections",
    "params":{
        "ids":["<redacted>"],
        "server":"<accountid>.<region>.snowflakecomputing.com",
        "username":"<redacted>",
        "usingRemoteQueryAgent":false,
        "embedPassword":true,
        "encryptedPassword":"<redacted>",
        "encryptedPasswordKeyId":"<redacted>"
    }
}

In theory it is possible. Just the existing examples are not working as documented and the parts that are working are not yet documented.

[neozenith]

I managed to get publishing updated credentials working via this method:

• server.datasources.download()
• server.datasources.publish()

It even overwrote OAuth connection detail credentials with the fixed username/password I was applying to all datasources selected.

Realisation:
I think once I realised why I keep getting pushed towards the Tableau Document API I kinda realised this whole thing is built on a very legacy concept from the Tableau Desktop that you have to download the XML as a file and publish new versions (revisions) of the XML.

Editorialising:
The API is trying it's best to be modern but still anchored to this concept which is more painful than a SOAP API but I can't fault the dev team since they have been keeping a very solid product running as a best in market solution for a very long time so hat tip 🎩 where it is due here.

# Standard Library
import logging
import os
from pathlib import Path
from typing import TYPE_CHECKING

# Third Party
import tableauserverclient as TSC
from tableauserverclient.models import DatasourceItem
from tableauserverclient.server import Server
from dotenv import load_dotenv

if TYPE_CHECKING:
    from tableauserverclient.models.tableau_auth import Credentials

load_dotenv()  # take environment variables from .env.
logging.basicConfig(level=logging.INFO)


tableau_auth: Credentials = TSC.PersonalAccessTokenAuth(
    os.environ.get("TABLEAU_CLOUD_TOKEN_NAME"),
    os.environ.get("TABLEAU_CLOUD_TOKEN_VALUE"),
    os.environ.get("TABLEAU_CLOUD_SITENAME"),
)
server: Server = TSC.Server(os.environ.get("TABLEAU_CLOUD_HOST"), use_server_version=True)

# Establish server connection
with server.auth.sign_in(tableau_auth):
    # Known issues with the datasources.get() method not really returning everything.
    # https://community.tableau.com/s/question/0D54T00000C5OkPSAV/rest-api-recently-published-datasource-missing
    all_datasources = server.datasources.all()

    # Extract shortlist of Datasources by prefix.
    datasources: list[DatasourceItem] = list(
        [datasource for datasource in all_datasources if datasource.name.upper().startswith(os.environ.get("DATASOURCE_PREFIX"))]
    )

    for datasource in datasources:
        print(datasource.name)
        # Retrieve connection details.
        server.datasources.populate_connections(datasource)

        # Downloaded Datasource Document (.tdsx file)
        downloaded_datasource = server.datasources.download(datasource.id, include_extract=False)
        print(downloaded_datasource)

        # Publish an updated version and Overwrite with new credentials
        updated_datasource = server.datasources.publish(
            datasource_item=datasource,
            file=Path(downloaded_datasource),
            mode=TSC.Server.PublishMode.Overwrite,
            connection_credentials=TSC.ConnectionCredentials(
                os.environ.get("CREDENTIAL_USERNAME"), 
                os.environ.get("NEW_PASSWORD"),
                embed=True, 
                oauth=False
            ),
        )