Alteryx Server Knowledge Base

Definitive answers from Server experts.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Gallery Data Connections FAQ

Alteryx
Alteryx
Created

Guide to Gallery Data Connections - FAQ

 

Creating a Gallery Data Connection will allow ease of access for Alteryx users to connect to databases. The feature also allows the Alteryx Gallery and Database Administrators more governance over what connections are being made as well as who are making the connections.

 

What are the advantages of using a Gallery Data Connection?

 

  • Centralized area for Administrators to create and share database connections
  • Avoids the need for IT to create database connections on each users machine individually
  • Easy permissions management - access to the connections can be enabled or disabled instantly
  • Users no longer need to know the connection information to connect to their data

 

What actually happens when you create the connection on the Gallery?

 

  1. When you create a Gallery Data Connection, a record is created in MongoDB under the AlteryxGallery > dataConnections collection.
  2. Spoiler
    {
        "_id" : ObjectId("5a5882573b910b5758cdd7e2"),
        "ConnectionString" : "4D35F08105015D7A81E5E274760D1C3529146D9516CACD028F1A353994C7DFB2ABAC8A99B1F284CCB7935B96F0FBD2EC9A9239B3305D4DE60D1C749C4E7BC65A597ED943742FB057EDD0F1882FFF98D6ED7888312215761DB1FA02B0EF425F9F98E645E73FB98481AC130D05F1A0CC0DBD42D4AC1F38E8DF8CA5759A6A4823F86C6FC212BD93263F83B90515DF6926B934FA086466A70992DF984C297A47C1DFFD749A642A6267B9FFC87B766127CE6C3D945FC64A8A25A2414DB2450AD6CAAD8D9202BFEBAF22C91B1371E1BA4C9CEB6E454B46B3BF4417D5280E53BCB5BE6AA734B",
        "PasswordSecured" : "",
        "ConnectionName" : "SQL_on_Gallery",
        "Subscriptions" : [],
        "Users" : [ 
            "5a56197d3z920b5758cdd5f6", 
            "503bac102031af71f8f8e479"
        ]
    }
  3. Once the connection is shared, it is also attached to that person's entry in AlteryxGallery > users collection.
  4. Spoiler
    {
        "_id" : ObjectId("503bac145031af11f8f8e479"),
        "Curator" : true,
        "Anonymous" : false,
        "LicenseCurator" : false,
        "Sponsor" : "",
        "Email" : "aketchum@kantoleague.com",
        "FirstName" : "Ash",
        "LastName" : "Ketchum",
        ],
    ...
    ...
    ...
        "DataConnections" : [ 
            "5baa469016ba3e2984be805e", 
            "5bb2022216ba3e5ff4b02b38", 
            "5baa5d0516ba3e2984be83ba", 
            "5c1b721c16ba3e26d43ec3a8", 
            "5bb2036b16ba3e5ff4b02be0", 
            "5a5882573b910b5758cdd7e2", 
            "5cfa1b39dd4214299419c703", 
            "5b924bc516ba3e2178e41deb", 
            "5d3f140edd421409506209a2", 
            "5d3f1498dd421409506209a9"
        ],
        "CanSchedule" : true,
        "CanSetPriority" : false,
        "CanSetWorkerTag" : true,
        "RecaptchaResponse" : null
    }

 

What happens when the connection is used in Designer?

 

  1. The user is authenticated. With Windows Authentication, the user who is logged into the machine/running Designer is authenticated automatically - it is not possible to change this. This means that this user must be a domain account that can authenticate to that server, and only that user's connections will be synced. 
  2. As soon as a user links their Gallery to Designer, a sync takes place that grabs all the connections they have access to and creates a file called GalleryAlias.xml in the user's* profile:
  3. C:\Users\USERNAME\AppData\Roaming\Alteryx\Engine
    *99% of the time this will be the user logged into the machine, but there is also the possibility that the user is right-clicking and running Designer as a different user.
  4. This file will be updated/synced when you:
    1. Open Designer.
    2. Open the Manage Data Connection window from Options > Advanced Settings > Manage Data Connections.
    3. Click Sync All in the Manage Data Connections Window:
    4. gdc_sync all.png

 

 

What happens when you execute a workflow on the gallery that uses a Gallery Data Connection?

 

gdc-process.png

 

  1. The Gallery 'translates' the connection from an XML file. One of the following will be used:
    1. GalleryAlias.xml
    2. __TemporaryAlias.xml
  2. The Gallery performs a permissions check.
    1. The permissions check ONLY applies to the user who uploaded the workflow. If the connection has not been shared with the uploader, it will error:
    2. Unable to translate alias SQL_on_Gallery
  3. The workflow is executed and the connection to the database is attempted. Any further errors will be around the connection string/driver itself, i.e.
  4. Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

 

 

What does 'translate the alias' mean?

 

  • This refers to the unpackaging and reading of the XML file to take the alias name (SQL_on_gallery) to the actual connection string. This must first happen before the connection to the database will even be attempted.
  • If this fails for any reason, you get the following error:

 

Unable to translate alias X

 

 

 

What is the __TemporaryAlias.xml file, and when is it used?

 

  • When you publish a workflow to the Gallery that contains one of these connections, a file called __TemporaryAlias.xml is packaged with the workflow.
  • You can see this file when you click Manage workflow assets during publication:

gdc_alias asset.png

  • By default, this asset is checked - it should be checked so that the workflow can use this XML to translate the alias.
  • Once the workflow runs, it is pulled down and unpackaged into the Staging folder.

 

Which of the two XML files are used?

 

  • The Designer first will try to use the __TemporaryAlias.xml file. This is the preferred way, because as mentioned this XML file is packaged with the workflow,, meaning it can successful translate on whatever machine (worker) it runs on:

rPRv0Euh73.gif

  • If you don't package the __TemporaryAlias file, it will then try to use the GalleryAlias.xml file. As mentioned, since this is in the User's profile, you would need the Run As user to have this file synced and available in their profile. This method is not preferred. Another issue with this method is that the file will need to be synced on all worker machines.

9xiry4DYOt.gif

 

What are the most important things to know to make sure my workflow will run successfully on the Gallery? 

 

  1. Always package the connection when publishing to the Gallery.
  2. From a permissions perspective, once the workflow is on the Gallery, it doesn't matter who runs the workflow for the translation to happen. Only the uploader of the workflow needs to have the connection shared with them.

 

 

 

Comments
Alteryx
Alteryx

If you've had your Gallery Data Connections spontaneously stop working, you can request your Gallery Administrator remove you from the list of Users with which the Gallery Data Connection has been shared and then add you again. "Broken" Gallery Data Connections most often occur when some (any) change has been made to the user's profile in Gallery.  Any change will "break" the connections, including granting the user permission to schedule, prioritize, or assign jobs to a particular worker.  Changes that the user makes such as specifying a time zone or company will also do this. 

 

This sensitivity to change has been corrected in the stable update to Server version 2019.3.5 and later releases. If you can upgrade to that version, this problem shouldn't recur.

 

If you cannot upgrade immediately, then avoid making any changes to user profiles in Gallery.  Sharing Gallery Data Connections with Studios/Subscriptions can be a partial work-around, but if workflows are scheduled any Gallery Data Connections that those workflows use MUST be shared with the owner as a User.  Sharing Gallery Data Connections with Studios/Subscriptions will grant permission to use them to the members of the Studio/Subscription at the time the connection is shared, but may be inconsistent if membership in a Studio/Subscription later changes.

Alteryx Partner
Alteryx Partner

Scenario: I am currently working with a client on my own machine. I have been provided with an internal client account that I use to VPN. I can also use that account to connect to the gallery in Alteryx Designer. The client has provided me with some gallery connections so that I can share these workflows accordingly. However they are not showing up in my list of gallery connections. I have tried just about everything I can to get these to show up on my machine.

 

My question: Is the fact that my local credentials are different than my client/gallery credentials stopping me from seeing those gallery connections?

Alteryx
Alteryx

@Treyson if the Gallery is using Windows Authentication, then yes that would be the cause. With Windows Authentication, the user who is logged into the machine/running Designer is authenticated automatically - it is not possible to change what user this is. This means that this user must be a domain account that can authenticate to that server, and only that user's connections will be synced. 

12 - Quasar

Hi @SophiaF 

 

Great article, thanks.

 

I'm using the CReW runner tool in a workflow on the server. I have one connection that has to be through the a gallery connection because it's a 32bit ODBC. I made all other connections inDB so they could be file connections. Is there a way to attach the assets (gallery connections specifically) into the workflow so that the workflow being run by the runner can use it?

 

Thanks

Katrin

8 - Asteroid

Hi @SophiaF - thank you for this. How do you decode the ConnectionString in the MongoDB AlteryxGallery > dataConnections collection in order to retrieve the Connection Summary (server name and database name)? This is useful when needing to generate a list of hosts.

Also, is there a way to map the data connection to published workflows in MongoDB?

Alteryx
Alteryx

@wildflower I'm not sure how to decode the string, but I would instead suggest using the Admin API using the /serverdataconnections/ endpoint. You can then easily parse the required information from the connectionString value.

7 - Meteor

Thanks @SophiaF Really helpful, it help us understand the cause of "Unable to translate alias" we are facing during Admin API workflow migration and when run the workflow. 

 

In our environment , the Run As User is restricted controlled. so we try to get a "___TemporaryAlias.xml"  to work. ( notice there are three '_' in filename, two '_' in your post) 

 

We perform two test cases at first:

Test #Test CaseValidate ResultRun Workflow Result
1Use Designer  create workflow with Input Data Tool with server data connection (ORACLE) and save to gallery with data connection checked under assetValidation OKRun OK
2Download workflow from test #1, get ___TemporaryAlias.xml  from Gallery Staging folder, add it back to the .yxzp file under winzip. (rename to .zip and change back)Validation OKRun OK

 

Below is sample ___TemporaryAlias.xml get from gallery, notice it contain password hash, I guess it's same in Gallery backed MongoDB. I so performed more test cases:

 

<Aliases>
<Alias name="uuuuuuuuuuuuuuuuuuuuuuuu">
  <Connection>HASH_OF_ORACLE_UAT_DATABASE_CONNECTION_HASH</Connection>
  <Password>HASH_OF_ORACLE_UAT_HASH</Password>
  <SourceUrl></SourceUrl>
  <CosmeticName>ORACLE_UAT</CosmeticName>
  <AliasId>uuuuuuuuuuuuuuuuuuuuuuuu</AliasId>
</Alias>
</Aliases>

 

 

Before run test, change the password of the oracle account, update it's password at data connection and verify it works fine.

Test #Test Case Test Result
3Run the workflow on gallery from Test #1Run OK
4Run the workflow on gallery from Test #2Failed with incorrect user name or password

 

So yes "___TemporaryAlias.xml" make it easy at some point in my case as gallery RunAsUser is restricted and it's not that easy to sync GalleryAlias.xml. However it lead me with workflow contain snapshot of user password and will be out dated once server data connection changed. And all workflow will be broken.

 

Also the way ___TemporaryAlias.xml store the hashed password raised security concern. 

 

Another question about "GalleryAlias.xml", beside open Designer and trigger "Sync All" under manage data connection, can this be done at command line or something ?

 

Alteryx
Alteryx

@Yongcan yes, the downside of using the Gallery data connections and packaging the _tempalias does mean that workflows will need to be re-published from Designer once the connection information is changed on the Gallery (username/password). As you mentioned, the workaround is to then sync the GalleryAlias.xml.

 

For your security concern, please raise a ticket directly with Support with details of the concern from your company/IT team.

 

There is no command line parameter to sync the data connections, but a thought is you could write a script to open Designer as the RunAs user from command line and test if the sync still happens?

8 - Asteroid

@SophiaF Thank you! I was looking for an alternative as there is a known defect with generating private studio Secrets for subscriptions that were created with API enabled (persists through server 2019.4.8). We have a number of studios that were set up this way with numerous workflows in them and I won't be able to make the API calls until that is resolved.

Alteryx
Alteryx

@wildflower I'm not intimately familiar with this defect, but it sound like it only impacts the private studio keys. This is using the Admin API - does that API work for you?

8 - Asteroid

@SophiaF Oh my!! I am so embarrassed, I was inputting the wrong key -- I got the request URL and response body for /serverdataconnections/ and will create a workflow now!! Thank you so much again.

5 - Atom

@SophiaF I just want to make sure I understand fully.

 

Let's say I have 50 workflows scheduled to run daily that are all utilizing a data connection stored in Gallery.  I then realize the data connection needs to use a different SQL user, so I update it in Gallery.  I would then need to go out and open each of those 50 workflows and save them back up in order for them to run with the updated data connection?

 

Thanks in advance for the help!

16 - Nebula
16 - Nebula

@SophiaF @nmccall @Yongcan I did some quick testing today and everything seemed to stay in sync when I changed the data connection if I checked the box.  Here was my method: I created a new data connection and shared it with my designer. I created a workflow and uploaded it to server with the box checked. I ran the workflow and confirmed it worked as expected. I then went to the admin section on the gallery and changed the data connection. I went back to the workflow in the gallery and clicked run, and it picked up the new info. So I don't think a password change would require you to republish the workflows if you're checking the box. Note I'm running 2019.2 and ran an extremely simple data connection test through just to see what it was translating it to.

5 - Atom

Hi,

 

I have the same question as @Kat.

 

When I use Crew Runner to trigger a workflow saved on a NAS drive, I get the error "Unable to translate alias".

 

Is this a limitation of saved data connections, that they must be uploaded as assets when publishing a workflow?

 

Regards,

Hari

Alteryx
Alteryx

@patrick_digan its a tricky thing to test, and I'd likely need to see exactly what you are doing to confirm. If you are testing on a machine which has the gallery synced (either your client or the server machine's designer where you've connected to the gallery), then the GalleryAlias.xml file is present and isn't a true test. What I would advise you test to confirm is executing the job on the server and monitoring the engine file (temp) to check the contents of the _tempalias file and confirm if the changes are there

Alteryx
Alteryx

@harii8 (and @kat apologes for missing this!)

 

I'd need to test this, as gallery data connections have not been tested for this (and therefore is not supported), but my thought is that you need to sync the designer on the machine you are executing the workflow from, which would ensure the galleryalias file is present. As this is saved in the user's profile, the CREW macro would need to execute as that user, which likely means you would need to make sure the Alteryx Service is running as the user who has the connection synced.

 

These files are only able to be saved as assets when publishing to the gallery, as this was the only use case they were built for.

12 - Quasar

Hi @harii8 and @SophiaF 

 

I've been trying to remember how I solved this one. It was so long ago and I'm not at that company anymore.

 

I think I solved it by making the workflow using the ODBC connection run a lot earlier to ensure it was done before the rest of the workflows were done. This way I let it run without the CReW runner, thus avoiding the problem. So @harii8 , maybe you can solve it with staggered timing?

 

I also tried adding the connection into the worfklow (just the connection but not connected to anything) with the runner and then being able to add it as an asset. But for the life of me I can't remember whether that worked! I have a feeling that the runner wasn't able to access the asset.

 

Sorry for not remembering more of the solution!

 

Kind regards

Katrin

5 - Atom
Hi @kat,

Thanks, that's what I've been doing for now. Running the tools with the
connection outside of the workflows run by the crew runners, but was hoping
there would be a workaround.

Thanks anyway! 🙂
5 - Atom

Apologies if this question is dumb, but how do gallery data connection permissions interact with the gallery's service account's permissions?

Consider the following scenario: the company gallery's service account has read access to SQL databases 1 and 2. Person A's account only has access to database 1, but A tries to create a workflow in their designer that reads from database 2. It errors out in Designer, but they upload it to the company gallery, hoping that it will run using the service account's permissions. What happens?

 

 

Alteryx
Alteryx

@quinntai a very good question!

 

The permissions to "translate" the connection are completely separate from the permissions on the database themselves. Alteryx assumes that if you are sharing a connection with users via Gallery Data Connections, you are happy for them to have access to those credentials. 

 

However to go along with your scenario, what would happen if the user was given access to the Gallery Data Connection?

The connection would actually work on their local machine, since its actually using the service credentials that you entered when you created the collection in Gallery. The same connection information is executed on the local designer, or on the server.

 

 

5 - Atom

@SophiaF thanks! that makes a lot of sense. 

 

What I'm still curious about is what would occur if person A publishes that workflow (trying to pull data from sql db 2) to the Gallery and tries to run it from the Gallery? My understanding is that every workflow run from the Gallery uses the service account's permissions, so even if person A didn't have database-level access to sql database 2, as long as the service account does, then the workflow would run fine from gallery. Or, would the gallery first perform a "permission to translate" check and realize that person A doesn't have access to the gallery data connection? In this scenario, I'm trying to prevent person A from gaining access to data they typically don't have (sql db 2) by taking advantage of the service account's permissions.

Alteryx
Alteryx

@quinntai it depends on the connection you make.

 

For example, if my connection is:

odbc:Driver={ODBC Driver 11 for SQL Server};UID=admin;PWD=XXX

 

Regardless of where the connection is ran, the user id and password are in here, so it will work everywhere. It doesn't take into account the user pressing run at all; all Alteryx does is execute this connection string.

 

Alteryx doesn't do any permissions checks to ensure anything on the connection, it simply sends the connection "as is". The only permissions checks are "does this person have access to the Gallery data connection".

 

Second example:

My connection is using integrated windows auth, and therefore the connection string doesn't contain a username and password, but UseSSPI=true. Again, Alteryx just executes what you have in the string directly, so whatever account the workflow runs as (Run As account in your example) will have it executed "as if it was them". If this is the case, you wouldn't want to use a Run As user (assuming this is the only way you cant connect to the database) but would want to utilize Workflow Credentials so users are running as themselves. 

 

So ultimately, it comes down to what you have in the connection itself. That is what is executed.

 

I hope this helps!

Alteryx Partner

@SophiaF  Is there a way to replicate the packaging of the "___TemporaryAlias.xml" file that Designer does when you save to the gallery, so that a workflow could be pushed instead via the Gallery Admin API rather than using designer manually? It would appear that simply putting them together in a zip file, renaming to .yxzp and pushing is insufficient.

Alteryx
Alteryx

@jwalder I've not seen any way to do this manually, and you are correct that this doesn't get packaged when saving to a .yxzp. So far, the only implementation via the APIs I have seen to get this working is to sync the Gallery alias files (and avoid the packaged temp alias file altogether)

Alteryx Partner

@SophiaF The syncing of the Gallery alias files simply updates the Oid value (from Mongo) referenced in the XML for the tool using the connection yes?

Alteryx
Alteryx

@jwalder i'm not sure what you mean by "old value" - could you clarify? It's more like a fallback - if the tempalias isn't there (or can't be there, due to the packaging not including it) it will then need "something else" to get the mongoid of the connection (in this case the galleryalias file)

Alteryx Partner

@SophiaF by old value, I mean if I download a workflow from gallery A, then the workflow has OID values from the mongoDB for gallery A. If I am publishing to gallery B, I need the references to update to gallery B, which is what I was asking about relative to the galleryalias file. Since the galleryalias file is encrypted, it is not possible for me to determine the mechanics.

To that end, I am clearly not understanding the mechanic by which the galleryalias file gets used. In my testing if (in Designer) I refresh the gallery connections of a workflow and it runs correctly, then I subsequently save it to gallery and it displays the connection (___TemporaryAlias.xml presumably) as part of the dependencies then it runs correctly in gallery without issue. If I download that same workflow via the API, and publish it back as a new workflow without changing the file at all, it does not work in gallery. Talking same gallery here: not A and B, just A. I can look at the package and it no longer contains the ___TemporaryAlias.xml. The XML of the workflow points correctly to the OID of the connection in MongoDB, but it still gives the "cannot resolve alias" error. All the aforementioned testing was done from the server itself, not a separate desktop. I assumed that a workflow downloaded via API from gallery could be immediately pushed back via API to the same gallery under a different name, but if there are gallery connections involved, at least without some additional component, that is not possible.

I did managed to figure out how to correctly attach the ___TemporaryAlias.xml file to package and publish via API and subsequently get a successful execution, so I have mitigated my issue, but that doesn't have me understanding the galleryalias play any better. Perhaps it is moot though.

Alteryx
Alteryx

@jwalder the important point is that there is an additional XML file that needs to be used to translate from the MongoID in the workflow, to the database (by the engine). It has to be either the _temporaryalias or the galleryalias XML files, so in your example on Server A > Server A when pulling down and reuploading (therefore with the missing __tempalias) and no galleryalias synced, it fails.

 

In the successful test you mention - is that publishing from Server A to Server A?

 

So to answer what I think you are asking, a high-level overview of what would need to be done to get the migration endpoints to work should be as follows:

1. Create all the existing data connections from Server A on Server B

2. Pull down all the workflows from Server A, replace in the XML all old data connection mongoIDs with the new mongoIDs of the corresponding data connections

3. Push workflows via migration endpoints to Server B

4. Make sure the galleryalias is synced, as we know the _temporaryalias file is not included in the push from step 3

 

I haven't tested this, but in theory that should be the process.

Alteryx Partner

@SophiaF Yes. I did both. Server A-> Server A and Server A-> Server B. Regarding the steps, that is almost exactly what I did, except I included the _temporaryalias when pushing the workflow in step 3.

Alteryx Certified Partner

@jwalder and @SophiaF and @Yongcan We are working on migrating workflows from Server A to Server B, What we have done so far:

  1.  Call admin API to download YXZP to local  
  2. Setup the gallery data connections in Server A and Server B with the same names.
  3. Update YXZP workflow XML from Server A data connectionId to Server B data connectionId, once finished, re-zip to YXZP.
  4. Publish updated YXZP to Server B, but got unable to translate alias error message.

As the above discussion mentioned, I'm missing either syncing Gallery Alias or packaging __temporaryalias.xml.But I still can't figure out how to solve this, here are my questions:

  1. Where to find _temporaryalias.xml file? I tried to upload one package with the data connection asset checked, download it and extract from YXZP but can't find it.
  2. How to sync Gallery alias XML? Do we need to go to the server machine and trigger sync? What exactly will be updated in the file?

 

Thank you for any help!

 

Kun