Problems with SQL Server shared connection on gallery

[JoBa]

HI

Using Alteryx Server 11.7, I've set up a shared connection.

I see that connection in designer (11.7 as well), and I'm able to use it.

However when I try to publish that to the gallery, I get a mysterious error

While trying to find out what's going on, I tried to check the workflow dependencies, and then I get:

What's causing this?

I've already tried restarting atleryx, clearing the connection history, etc ...

Thanks for any insights!

[Nate1]

I have solved this issue by closing designer, removing my access to the connection, and sharing it with myself again. If anyone knows the actual cause of the issue or a better solution though I would be curious. 

[JoBa]

Hi @Nate1

thanks for the suggestion but that doesn't solve the issue. I've raised a support ticket, will post here if that leads somewhere.

[Nate1]

Hi @JoBa,

One other time when I get a similar error I know the cause was that I renamed the connection but didn't update the password on the gallery from the encrypted value "__EncPwd1__". When I saved the connection again with the proper password it started working. 

I also think in my case the cause might be changing studios. It seems whenever I change the connection doesn't work anymore even though it is shared at the user level. I am on 2018.1 and when I try to connect I get this message.

Please keep me updated on the support ticket outcome. Thanks!

[JoBa]

So after a very useful call with support , the main issue is solved and other problems were confirmed to be known bugs:

There was nothing wrong with the data connection defined in the gallery. The fact that I could create new workflows in designer using that connection is the simple evidence of that.

First some other findings:

1. The fact that I cannot edit the gallery connection (I'm using an "other connections" type to make use of better performing OleDb drivers) after creating it, was confirmed as a bug. Workaround is to delete and recreate with the same name, this is also descibed in other community posts.
2. The problem that, in designer, the workflows using my gallery data connections fail to validate in the dependencies window, is a bug as well. There is no workaround, but it is not blocking either.
3. You need to manage and uncheck the shared connection asset when publishing (yes, every time). That's a nuisance, Alteryx should be able to guess that you don't want to package gallery connections in the first place, but that's what the current state is.

Finally, how to correctly use gallery connections:

1. you need to set a RunAs account for server. Even when not using connections that use AD authentication; the reason is below.
2. you need to create a gallery user for the runas account, and share your gallery data connections with that user. (Bit silly, should't that standard behaviour that you allow the server automation accounts to access those?)
3. And then, and this was the big gotcha: you need to log on to windows (RDP) on the server machine as the RunAs user, open Designer, and connect to the gallery (with the gallery user you've just defined).
4. Now you can publish and run workflows using the gallery data connections.

By the way, only after understanding this process, the last sentence "Lastly, make sure that the server machines are 'signed in' to the gallery so they can receive the shared connections." which I found on this post made sence to me...

Quite a hassle, and badly documented, all of this. If you ask me the gallery connection are a good idea and certainly an improvement from connection management point of view (no more sharing all those connectivity details with users, creating DSNs everywhere,...) but if you ask me, the implementation is quite sloppy and the usage is overly complicated...

Hope this helps those with the same problems ...

[alv728]

I am experiencing what sounds to be a similar issue. Has any of this been resolved?