Alteryx Server Knowledge Base

DanM · ‎02-15-2017

The Database Connection Share is a feature that will allow ease of access for your Alteryx users to your 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. This article reviews the procedure for creating a database connection share through the Gallery Admin. Users will then be able to see these "Gallery" connections on their desktop version of Alteryx and connect to the database with the permissions granted by the Admin.

Prerequisites

Alteryx Server
- Version >= 11.0
- Gallery Admin permissions
Alteryx Designer
Corresponding supported database driver, installed on both Server and users' machines.
Database Name and Database Hostname, provided by the database administrator.

Procedure

Login to your admin account on your private Gallery, and proceed to the Admin setup window:
You then see a field on the left-hand side called Data Connections.
Once in the Data Connections, you will have the option to create or edit existing connections:

In newer versions of Gallery, the Add New Data Connection button is on the top right:
When creating new data connections, choose the connection type you are going to use.
Note: The drivers will need to be installed on both the Gallery and the user's machine in order to use the connection.
Test the database connection:
Once the connection is tested and ready to go, you can select that data connection. Two tabs will show in the window, General Information, and Users and Studios. The users that you share the connection with will only see the Name field and none of the connection information.
When selecting the Users and Studios tab, the Admin will be able to see who currently has access to that connection are able to remove and add users to the connection. Note that users will have two profiles; one will be their Studio account (with the icon showing three people)and the other would be an individual user account. If you want to share the data connection, then select the specific user's account.

It is recommended to share Data Connections with individual users and not to share with Studios or Active Directory groups due to known issues where data connections are lost.
Once the connection is shared, the user will be able to look into their Saved Data Connections in the Input Data Tool. If they do not have access to the Gallery, where the connection was established, the user will have to Add a Gallery. Once they have access to the Gallery, they will see next to that Gallery name a new connection. The connection will show the name given and (Gallery).

In newer versions of Designer, click on Gallery to see the list of data connections:
The user can see all of the connections shared with them in the new Manage Data Connection located in the Options\Advanced Options menu in their Alteryx Designer.

Common Issues

Error: Data source name not found and no default driver specified

See Data source name not found and no default driver specified

Additional Resources

Help Documentation: Data Connections

patrick_digan · ‎03-09-2017

@DanM This new feature is awesome! My one question is how to effectively have this work for our private Alteryx server account as well (which is a service account). I can assign the data connections to the service account that runs all of our jobs in our Alteryx Private Server, but it doesn't look like the service account is picking it up when I ran a test job. The server just responds that it can't translate the alias. I verified on the server that the gallery alias.xml file doesn't yet exist for the service account. Am I missing something? I essentially want 1 spot (the gallery) where I can manage all the connections for all users and the service account running our server jobs. At this point, I'm going to have to maintain all of our credentials in the gallery for users, and then maintain a separate copy on the our private server.

Thanks!

DanM · ‎03-09-2017

You will have to setup a system alias on the Gallery. The advantage of the connections on the Gallery is to manage user connections not the system connections. The service account has no way to communicate with the Gallery and that is why you are receiving the error.

https://help.alteryx.com/11.0/index.htm#AliasManager.htm

Rocky360 · ‎04-03-2017

@DanM: This feature looks great. However, is there a way I can share a connection with all users? Rather than having to add each user (possibly twice - once for user, once for studio)?

DanM · ‎04-03-2017

@Rocky360 if you are using Windows Auth on your Gallery, you should be able to use the group function you may have within your company groups i.e. Sales. Otherwise you would have to create the group. You could also give everyone access to a certain studio and the database share will share with everyone in the studio.

Rocky360 · ‎04-03-2017

Thank you, that gives me a couple of options to try.

KenMoorhead · ‎04-05-2017

@DanM -- I'm having a similar problem to @patrick_digan re: alias translation issues.

You mentioned the below in a response:

You will have to setup a system alias on the Gallery. The advantage of the connections on the Gallery is to manage user connections not the system connections. The service account has no way to communicate with the Gallery and that is why you are receiving the error.

Does this mean to create a System alias on the server using the designer license installed there? E.g. log in to the server VM, open Alteryx designer, navigate to Manage Data Connections, and create there? Would I want to use the same name on the server as the connection I've created in the Gallery so there aren't translation problems?

DanM · ‎04-05-2017

@KenMoorhead the Gallery connection would need to be shared with the Gallery admin or the Run As user depending on your Server setup. When you create a connection in the Gallery it is not associated to anyone or anything until you assign it. So, if the connection needs to be used on the Gallery itself, that connection will need to be shared with the Run As user or Server Admin. The Designer version on your Server has no relation to the Gallery. So any configuration for the Gallery either needs to be done in the Gallery itself or the System Settings depending on what you are attempting to do. The Designer on Server is there for development of workflows only and not for creating or making changes to your Gallery. In your instance, you will want to share the data connection with either the Run As user or the Server Admin as well as the user. This should then allow the Gallery to run the workflow under the Run As or Admin user which would then have access to the database you have connected to through Gallery. Anyone that needs to run this workflow or app would need to be added to the shared data connection.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Run-As-Settings/ta-p/17830

Please review documentation for the Server here - http://downloads.alteryx.com/documentation.html

You also have specific Server help if you are the Admin of the Server. Go to the Admin section of you Gallery and you will see a Help button at the bottom that will take you to the Server Admin Help page that will explain how the data connection work with your server.

dwalker3rd · ‎06-07-2017

though nothing in the online help or the community (that i could find) specifically states that in-db connections cannot use shared data connections, it seems to be the case. i would like to see that confirmed and, if true, put into the online help. many of my artisans (particularly those whose native tongue is not english) find the docs on this confusing and believe they only need use the same name (as tho these were aliases from 10.x).

am i correct that shared data connections are not available for in-db connections? if not - or if there's a hack - i'll need someone to explain.

thanks,

David

reddy520 · ‎06-26-2017

Hi I am Trying to setup SQL Server connection, any idea how to resolve this below error on gallery in Alteryx Server v 11 ?

Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Please help

Joe

PeterS · ‎08-16-2017

Hi @dwalker3rd, that is correct. In-DB connections cannot take advantage of the Shared Data connections in a Private gallery.

What can be done is to set up a Connect In-DB tool using a Connection File. This will store the connection details in a file that is then included with the workflow or the file can be shared with others to use, just be sure that the appropriate drivers are installed on all machines using running the database connection.

Check out this article on how to set up a Connection File:

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Alteryx-In-DB-Connection-File/ta-p/17574

The first screen shot Referenced is from Alteryx 10.1, for newer versions you will want to look under the Options -- Advanced Options--Manage In-DB Connections menu. The remaining steps should be the same.

This is also discussed on the Connect In-DB help page under 'Add a new InDB Connetion' under step 3.

Hope this helps!

levell_x_dunn · ‎08-28-2017

Hi @PeterS,

I have been using the data connections in the Gallery for about a month now and have found it very useful.

I have been using it sharing and for managing multiple servers with one connection source location, minus in-database connections.

I have run into a bug or issue, I have created 25 connections and seem Alteryx is unable create or display any connections past 25.

It saves the connection but doesn't display them.

Any suggestions

cdgogan · ‎10-26-2017

@PeterS, did you ever get a response / explanation / fix for your reported issue above? I've discovered this same "25 data connection limit" as well. I'm running 11.5.1.31573 and the "issue" still exists. I had to purge a few un-used data connections to get my newly created to appear.

Please let me know.

Cheers,

PeterS · ‎10-26-2017

Hi @levell_x_dunn and @cdgogan

There is a known issue about the Gallery only displaying 25 data connections. I'm checking with our development for when they expect to have this resolved. I'll keep you posted.

PeterS · ‎10-26-2017

Hi @levell_x_dunn and @cdgogan

Here is the latest, the issue of only being able to view 25 Gallery Data Connections will be resolved in our 11.7 release which is scheduled for late November.

levell_x_dunn · ‎10-26-2017

HI @PeterS,

That is good to hear. That definitely will be useful. Do we know when 11.7 is scheduled for release?

Next we just need to get it enhanced so you can update the password without having delete the connection, create a new connection with the updated password and then go to every server to cleanup and refresh the data connection list.

PeterS · ‎10-26-2017

Hi @levell_x_dunn

11.7 is scheduled for late November 2017, I don't have an exact date.

What you are describing about updating the password in the Gallery connection sounds like something we have updated. I would suggest emailing support@alteryx.com to discuss this on more detail.

LisaL · ‎12-01-2017

@PeterS

Thanks for the heads up on the 25 connections limitations. 11.7 is out and the limit has been raised to 100. (Release notes: Alteryx Server and Gallery: Additional Changes http://downloads.alteryx.com/Latest.htm )

dataMack · ‎01-04-2018

I've been starting to use shared data connections via gallery more and I think they are a great (and underappreciated) feature that can help an analytics team better collaborate on workflows. One thing I've run into though is in setting up connections to Hadoop (ex. Impala) where Kerberos is used as the authentication mechanism. With a standard username/password type connection, it is easy to embed the credentials for something like a functional ID. For a connection that leverages kerberos, however, such information is not in the string and the ticket gets picked up at run time. What I've found is that the shared connection tries to use the ID of the user who is using the connection in their Designer. That makes sense, and would be expected- but how can I configure the connection to use a functional ID where the kerberos ticket is stored on the server, and not use the id of the user that is leveraging the shared connection

bryanbumgardner · ‎03-28-2018

The connection file solution will be fine, but without In-DB tools this shared data connection seems kind of pointless. I've worked with teams doing this kind of thing and they have to import the entire database table into their (or the gallery's) memory and then do operations, rather than simplifying the data through queries which are available in the In-DB tools.

Oh well!

brindhan · ‎07-30-2018

On a related note, what do you recommend here?

https://community.alteryx.com/t5/Alteryx-Server-Discussions/What-is-the-best-way-to-manage-connectio...

Michal · ‎11-15-2018

Hi @LisaL,

I got feedback from a client that they can only see 12 connections in version 2018.2, is it something worth raising with support if the limit was supposed to be raised to 100 in 11.7 version?

Thanks,

Michal

LisaL · ‎11-15-2018

@Michal

I think that it is worth opening a case (support@alteryx.com). I knew that the limit on absolute number of workflows has been raised, but I don't immediately know about Data Connection limits. (I'll edit this post when I learn more.)

amti · ‎03-31-2021

@DanM

I've been reading a lot of threads here about shared connections, connections shared or not shared, etc and seems we're feeling a little bit lost here.

I want to use my alias for different environments, one for Dev and one for Prod. I do this, locally, by creating an alias that points to my DSN either with "User" or "System" type it works running in Alteryx Designer (Note: yes, I've created the DSN for System with ODBC Admin and we have it on Server, exactly the same).

Running on the server I get the "Unable to translate alias aka:xxx" because the server doesn't understand my alias. My question is:

1) Is it possible to create a shared connection with an alias and point it to the DSN via Gallery instead of pointing it to the local (System) one?

2) Which connection has to be shared in the server? the DSN or the alias pointing to that DSN? Not sure about this and I wanted to help the helpdesk on the exact steps needed.

The idea is to centralize the connection for each environment, in case user credentials change, etc. Because this would be a headache each time we have to change user credentials, having to go through all INPUT/OUTPUT components and change or pass encrypted pwd (which is really not a good practice, I believe).

I can leave some screenshots of my/our questions so far:

HeatherS · ‎11-18-2021

Is there no way to edit a SQL server credential for an existing Alteryx Gallery data connection? I don't see any option to edit a password.

LisaL · ‎11-18-2021

@HeatherS

I'm sometimes excited to see a question I know the answer to. I'm assuming that you are a Curator (Gallery Administrator) and that your SQL Server is configured to use SQL Server (username + password) Authentication. If so, it's actually really simple (which is why I'm excited).

Step 1. Select the Data Connection you want to edit:Click on the Data Connection you want to edit

Step 2. Click on the Edit button.Click to edit

Step 3 & Step 4. Type over the __EncPwd1__ with the new password, then click on Save.Type the new password in place of the old password, then click Save

That's it. After you click on Save, the text will again be hidden, but the new password is in there. If you restart Designer, that will refresh the GalleryAlias.xml file on your local computer and you'll have the Data Connection with the new name.

HeatherS · ‎11-19-2021

@lisa

Hi! I should have been more specific. Yes I am a Curator but we connect through Windows Auth and the password (_EncPwd1_) wasn't showing. The default connection looks like this:

odbc:DRIVER={ODBC Driver 17 for SQL Server};DATABASE=DBInfo;SERVER=ServerInfo;Trusted_Connection=yes

After reading your response I tried adding the UID and PW to my connection string and it seems to be working:

odbc:DRIVER={ODBC Driver 17 for SQL Server};UID=Username;PWD=Password;DATABASE=DBInfo;SERVER=ServerInfo;Trusted_Connection=yes

It ended up being very simple but I just didn't think of it yesterday 🙂

Thanks,

Heather

LisaL · ‎11-20-2021

@HeatherS

I see. Trusted_Connection=yes is used when the SQL Server instance is using Windows Authentication. How that is validated depends on how you have your Gallery configured. If you have configured your gallery to Use Default Credentials, then it will connect using the Run As User's credentials. If that password has changed, you'll need to update it in the System Settings and restart the AlteryxService with the new settings. If you use one of the other options, Require User Credentials or Allow User to Select (which means that Artisan who authored the workflow decided on a workflow-by-workflow basis), then the authentication will use the credentials belonging to the user who is running the workflow (or the Run As User's credentials on a workflow-by-workflow basis).

This setting is what determines where Windows Authentication finds the credentials to use.

What you have done by adding UID and PWD to a Trusted_Connection is you've specified that any time that connection is used, it will authenticate using the UID and PWD you've entered rather than using the credentials of the user who clicks on Run (or the credentials of the Run As User)*. That's fine if it was your intention to do that. Otherwise, you might want to leave the connection as it was and make the user enter the credentials at runtime.

To clarify a little further, when creating the connection from Gallery, you'll likely see something like this:
Win Auth usually generates a string with Trusted_Connection=yesThe Win Auth will only work if the SQL Server instance is also Win Auth

What you select when you create the connection will determine what fields are included. If the SQL Server instance uses Windows Authentication, then you can use Windows Authentication when you create the connection. If the SQL Server instance uses SQL Server Authentication, then you should use the Use SQL Server Authentication option when you create the connection.

*Using an explicit UID and PWD with a Trusted_Connection=yes will prevent user-specific authentication, making the authentication always use the UID and PWD you've specified (at least I think that's what will happen; I haven't tried this because it's manually overriding the two systems in a way that defeats the purpose of using Windows Authentication with a Trusted Connection.)