Singular ODBC for SQL to be leverage by all gallery users

[markashman]

Hi I use system DNS in ODBC for Mongo and that works fine, can simple add odbc:DNS=name into a flow and it works both in designer (on the server) and from gallery for all users.

I want to do the same for Azure SQL, but SQL forces you include user and password credentials, so odbc:DSN=name;UID=user;PWD=__EncPwd1__ which also works, but the issue there is when those credentials get rotated I need to update multiple input tools across multiple assets, which is painful.

What I want is a singular connection that can be reused and managed in 1 place directly on the server, much like for MongoDB.

Has anyone achieved this, and if so how?

[KGT]

Have a look at the DCM. The Data Connection Manager separates the connection and credential, so that you can update one spot for the credential.

[markashman]

I have looked at DCM a few times, but never been able to get it to work, I can create and sync between server and designer, but cannot seem to select a DCM connection for use in a flow. Also, the flow is to be shared with 1000s of users, and my understanding was that means you would also need to share the DCM with each of those users.

[KGT]

I haven't had much to do with DCM to know the nuances. The other option is to go back to the old method of setting up a data connection under Options > Advanced > Manage Data Connections. That will then provide an alias and I think the credentials should be centralised and the workflow just reference the alias. The Alias will need to be set up the same on the gallery as the designer though and so you would need access to the Server Designer. (I'm not sure if Gallery Connections are still a thing or whether they use their own UUID and so I don't know if you can just enter it in gallery).

DCM was created for the exact purpose you are after though, to centralise a connection separate from the credentials. So, I'm thinking there must be a way, but as I said, I'm not versed enough in it to know the nuances.

[markashman · Accepted solution]

No worries KGT, tried it cant get it working, going to reach out to Alteryx directly, as DCM is just not intuitive.