community
cancel
Showing results for 
Search instead for 
Did you mean: 

alteryx server Knowledge Base

Definitive answers from Server experts.

How To: Create a Database Connection Share Through Gallery Admin

Community Operations Manager
Community Operations Manager
Created on

How To: Create a Database Connection Share Through Gallery Admin

 

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 ≥ 11.0. 
    • Gallery Admin permissions
  • Alteryx Designer ≥ 11.0.
  • Database
  • Corresponding supported database driver, installed on both Server and users' machines.
  • Database Name and Database Hostname, provided by the database administrator. 

 

Procedure

 

  1.  Log into your admin account on your private Gallery, and proceed to their Admin setup window:

     

    Admin.jpg

  2. You then see a field on the left-hand side called Data Connections. 

     

     

     

     

    2019-01-17_9-42-45.png 

     

      

  3. Once in the Data Connections, you will have the option to create or edit existing connections:

     

    adding.jpg

  4. 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.

  5. Test the database connection:

    AddData.jpg

  6. 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.

     

    edit.jpg

  7. 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 Studio.jpgwhich if the database connection is shared with the Studio, all of the users associated with that Studio will have access to that database connection. If you just want to share with the user, then select the user's account  users.jpg:

     

    Edit Data Connection.jpg

  8. 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 which 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):

     

    Oak.jpg

  9. 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

 

Spoiler
If the user has any difficulty using the connection:

1. Confirm that the test worked on the Gallery.
2. Make sure that the user has the correct driver installed.
3. Confirm that the correct permissions are grated for the user.

  

Additional Resources

 

Comments

@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!

Community Operations Manager
Community Operations Manager

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

Atom

@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)?

Community Operations Manager
Community Operations Manager

@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.

Atom

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

Meteor

@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?

Community Operations Manager
Community Operations Manager

@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.

Meteoroid

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 

Meteor

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

Alteryx
Alteryx

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!

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

 

Atom

@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,

Alteryx
Alteryx

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.

Alteryx
Alteryx

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.

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.

Alteryx
Alteryx

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.

 

 

Alteryx
Alteryx

 @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 )

Quasar
Quasar
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

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! 

 

Comet
Alteryx Certified Partner

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

Alteryx
Alteryx

 @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.)