Dyanamic Connection String and Password Hashing

ialjenaidel

Dear all,

I'm trying to build a rather simple workflow that compares the structure (schema) of a number of tables between the source database and the distention database. The idea is simple if detect changes run a stored procedure, call API, etc.

My idea is to a create a table (alteryx_lookup) contains:

• System name (Business term)
• The connection string of the source (Password hashed by SQL)
• The connection string of the distention (Password hashed by SQL)
• Run a custom query to get the needed tables to track 

and I will loop thought this table system by system.

I have tested it when comparing between different databases tables within the same server it runs as excepted but now I want to make it run where the source and target are not in the same server, I have faced couple of problems and challenges.

Challenges so far:

• The source and dentitions are not in the same environments
• The two databases might be of a different type (MSSQL to PostgreSQL, PostgreSQL to MSSQL, MSSQL to MSSQL, MySQL to MSSQL)

Questions:

• How to make the connection string dynamic ?
• The connation string would most likely would be stored in the alteryx_lookup table i mentioned above with rest of system information, and the password would be hashed by SQL. Now how can Alteryx read the password and decode it to use in the connection string of the dynamic-input tool?
• Can I just store the Username, password and Dataabse type(MSSQL, MySQL) or do I have to provide the entire connation string?

Please let me know if you need any clarification.

Your help is highly appreciated.

Thanks.

DanM

@ialjenaidel 

This is a tough one since you are dealing with connections. You may find that it may be easier to just setup all the connections and then do comparisons rather then attempting to update connections on the fly. If you did go that route, you could then create a batch macro to compare the schemas based on a comparisons lookup table. This would at least take the connection issues you may run into out of the equation.

If you did decide to go the dynamic route you would have to provide the entire connections string, password and all to update the tool. The issue you may run into however, is the actual call to the database when switching or you could run into driver issues as well since they may use different drivers.

I would suggest next time to provide a workflow of what you have attempted thus for so that the Community members can take a closer look at what you have attempted and your logic.

Hopefully this helps.