How to pass User ID and password using Interface tools to an Input tool that uses ODBC

[jkell]

Hello - 

I am trying to make an analytic app that is connected to a database via ODBC. The ODBC requires UID and Password to connect and are tied to each specific user. Therefore I am wondering how I can pass UID and Password to the input tool so that it can be updated each time for different users.

I will also pass date parameters to the SQL statement, but I have completed this portion. But unsure how to proceed with variable configuration of an ODBC.

Thanks!

[geraldo]

@jkell 

The best solution for Alteryx is that the same DSN is created on each machine via Alteryx Manage Data Connections or Manae InDB Connections.
You create the workflow with aka: configured on your machine. If each user creates the aka with the same name on the machine, it will not be necessary to ask for a username and password.
If a DEV or UAT or PROD server has the same aka: created, you can upload it and there will be no problem in your workflow as the users and password are configured on the servers

This is a suggestion.

[jkell]

Thanks for the suggestion Geraldo - couple of responses:

All of them machines will use the same system DSN (which is basically account ID and role ID). But the ODBC is connecting to a protected system and always requires UID and Password, at least upon initial setup.

Are you suggesting that once the DSN is configured and users log in once, they will not have to do log in steps again (as long as a workflow is using the same DSN?)

For example, I configure a DSN for a user today (they perform initial log in steps) and provide them a report. Tomorrow I create a new workflow (that uses a different SQL statement, but uses the same DSN) and give it to the user? Will they not have to perform log in steps again?

I'm still in testing phase so haven't actually gone through the motions but appreciate your thoughts!

[apathetichell]

I mean the obvious answer here is create a service account - have the alteryx process run as the service account. Does this DB support oauth 2? if not the kind of "run as user with user credentials" is failing at the DB level - vs the Alteryx level. And you're planning on retrieving/inserting username/password at run time? there is something wrong here.

[geraldo · Accepted solution]

@jkell 

That's right. You create the ODBC connection and enter the ID and password there. As long as the password is not reset, it is not necessary to log in.

This is also used on the Server in production. A default alias is created and the username and password are entered and registered in Alteryx. You develop with the same alias and when the workflow is uploaded, it will not give an error and will use the production UI and password.

[jkell]

@geraldo Okay great, thanks for the help! Hoping all goes smoothly once I implement these steps!

[geraldo]

@jkell 

An example of how to create:
The ODBC will be created with UI and password, you use this Alteryx mechanism to associate the workflow with the ODBC of users with the same DSN, not having to enter a password when creating the data connection in Alteryx

[jkell]

Perfect, thanks! I have been successful at creating this for myself, but was just unsure how things would change when I involve other users. But always helpful to have visuals so I appreciate you putting this together.