Alteryx Server Knowledge Base
Definitive answers from Server experts.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
- 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 specifiedAdditional Resources
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
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:
Step 2. Click on the Edit button.
Step 3 & Step 4. Type over the __EncPwd1__ with the new password, then click on 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
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).
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:
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.)
