Alteryx Server provides a scalable server-based analytics solution that lets you create, publish, and share analytic applications, schedule and automate workflow jobs with increased processing power, create, manage, and share data connections, and control data access.
Data Connections is a feature available on Alteryx Server, also known as Alteryx Gallery, that allows environment administrators to create, grant permission, and share connections with users in a single location.
Understanding how to create and manage these connections is essential to improving:
The users’ experience, as it will be easier for them to connect to a database even without knowing all the details associated with it in a completely transparent way.
The governance of the Alteryx Server regarding existing connections, as it is a centralized place to manage connections, the permissions of each one of them, and who has access to them.
In this blog, we will be covering the main points of attention when using the Alteryx Server Data Connections feature, such as:
DSN and DSN-less connections
How to Configure a Data Connection
Governance
Good practices around publishing workflows with this feature
Before we begin, it’s important to highlight that you will only be able to use the Data Connections feature in Alteryx Designer if:
The company you work for has an Alteryx Server (Alteryx Gallery)
There are Data Connections previously configured
The ODBC driver must be installed locally
Your user has access permission to the specified Data Connection
You have logged into the Alteryx Gallery from your Alteryx Designer
The Concept of DSN and DSN-less Connections
DSN Connections
First of all, it is important to know what the acronym DSN stands for: Data Source Name. This is just a name that applications use to make connections to an ODBC (Open Database Connectivity) Data Source. In simple words, it is a generic name that represents an ODBC connection.
Basically, there are 3 types of DSN:
System DSN: This is the system-wide DSN used, which means it will be available to any user on the system who can be authenticated.
User DSN: This DSN is created for a specific user. Only the user who created the DSN can see this and use it.
File DSN: This is a DSN that is stored in a text file with a .DSN extension. Unlike the previous 2 types, the connection information is not stored in the Windows registry.
Users developing Alteryx workflows typically need to configure at least one ODBC connection to connect to a given data source. This is configured on the user’s local machine.
In some cases, these users may want to publish these workflows to the Alteryx Gallery in order to take advantage of its benefits, such as increased processing power and recurring scheduling functionality for workflows to run.
When these users publish a workflow containing a DSN connection to the Alteryx Gallery, it is common to see some errors at runtime:
Data source name not found, and no default driver specified
Unable to Translate Alias
Errors caused by permissions on the data source
The root cause of the first 2 commonly cited errors is the lack of DSN configuration on the Server side. Additionally, even if there is a DSN connection to the same data source but configured with a different name (it could be just one letter!) from the one used locally by the user, it will not work.
So, the first thing to do is make sure the DSN connection names are exactly the same on both the Alteryx Server and the user’s local machine.
The second thing to check is the access credential used in the DSN connection. Typically, users use their personal credentials in the local connection (which is perfectly normal), but when we talk about the server side, this can generate permissions errors like the third error listed.
This happens because, normally, DSN connections configured on the server use service accounts instead of personal credentials. Additionally, using personal credentials on the server is not good practice and can also pose a governance risk.
Now that we understand all the risks and common mistakes associated with DSN configurations, let’s understand how DSN-less connections work.
DSN-less Connections
DSN-less connections use a connection string. With this string, it is possible to configure a connection between the server and the data source that will not require the system administrator to create an ODBC DSN.
The string contains information about the provider driver data source name, such as driver name, server hostname/IP address, port, username/password, security configuration (SSL/TLS), and database name, among other things.
This means that no connection information is stored in files or the Windows registry. All the necessary information for the connection is informed solely and exclusively in the string.
Additionally, the password used in the Connection String is encrypted by Alteryx, which makes it not possible for a user to retrieve the password from the Connection String.
When creating a string, pay attention to the very beginning, where you need to specify the proper connection method, such as odbc, odb, or oci.
Here are some examples of DSN-less connection strings:
odbc:DRIVER={SQL Server Native Client 12.0};SERVER={Server_Name_or_IP_Address};DATABASE={Database_name];UID={Username};PWD={Password};
oci:username/password@servername_or_IP_address:port_number/service_name
oci:username/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=servername_or_IP_address)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=service_name)))
oci:username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=servername.domain)(PORT=9999))(SECURITY=(MY_WALLET_DIRECTORY=C:\app\client\Admin\product\12.1.0\client_1\Network\Admin\Wallet)(SSL_VERSION=1.2)(SSL_CLIENT_AUTHENTICATION=TRUE)))
The string configuration format will vary according to the manufacturer of the database you want to access. These are some of the most used examples. However, it is always important to consult the specific documentation of each manufacturer. Even because the order of the elements in the Connection String may cause the connection to fail.
How to Configure a Data Connection
Only users with the curator permission can create Data Connections in the Alteryx Gallery. To view existing Data Connections or create a new one, you need to access the Gallery from the Admin console and navigate to the Data Connections menu.
If you want to edit an existing connection, just click on its name, and if you want to create a new one, just click on the New button.
First of all, don’t forget that the ODBC driver must be installed locally.
When creating a new connection, it is necessary to inform which technology is used. Alteryx Server offers 3 options: Microsoft SQL Server, Oracle, and Other.
The first two already have some registered options that are peculiar to the respective technologies. But from the Other option, you can create the connection strings as you wish, including for the two technologies mentioned above.
Remember that, if necessary, it is possible to create a Data Connection that references a local ODBC DSN on the server. It is only important to ensure that it is already created and functional.
It is also very important to verify all information so that typing errors do not result in the connection not working. Additionally, testing this on the server machine is important and very useful to identify connection problems, such as firewall rules.
Here is an example of the Standard ODBC DSN Connection string format:
odbc:DSN={DSN_NAME};UID:{UserID};PWD={Password};
Here is a real example of a connection between Alteryx Server and Snowflake:
The first important thing to note is that the password is always displayed in encrypted form, which represents an important security aspect.
Second, below the String configuration screen, we have control over which Alteryx Gallery users will have access to this connection. Note that you can add users individually or as a group.
Always remember that all users with access to this connection will have access to the data source with the credential registered in the String. It means that if user “Joel” builds a workflow and uses this Data Connection, all operations on the data source will be performed with the credential configured in the String.
One last tip, which is extremely valuable, is since the sections within the String are separated by a semicolon if the specified password has this character, this will result in a connectivity error, as the String will understand that a session ends there and starts another, even if this is the last character of the password!
And what is the solution for this case?
Always enclose the password in braces! Just like this:
…PASSWORD={pass;word};...
Now that all settings have been configured, do not forget to perform the connection test to ensure the Connection String is working correctly. Don’t forget that firewall restrictions can prevent connectivity even if the Connection String is correct!
Once everything is configured correctly on the server side, these are the steps the user needs to perform:
Ensure the Alteryx Gallery has already been added as a mapped server within the Alteryx Designer
(Open Designer, File > Open Workflow > Add New Server, paste Alteryx Gallery URL, and authenticate)
Ensure you are logged into Alteryx Gallery from your Alteryx Designer! Remember that Data Connections is a Server resource. Therefore, you must authenticate to be able to see the connections you have access to.
You can easily force login by clicking File > Open Workflow and selecting the already mapped Alteryx Gallery. After authenticating, you can close this pop-up window and go back to the Canvas.
Once you are authenticated, go to Options > Advanced Options > Manage Data Connections. All connections you have access to will be listed here:
Governance and Good Practices
One of the biggest benefits of using Data Connections is governance management. Controlling which user is used to make the connection, who has access to the Data Connection, and for how long is essential to avoid headaches.
Permissions
For each existing connection, it is possible to control who has access and who does not. This control can be done individually or in groups.
Password Encryption
As we can see in the Snowflake_dev connection we created (Figure 3), the password is encrypted and not available for viewing. The section will always be the one with the following value: "__EncPwd1__".
Password Section Delimiter in String
As mentioned in the section on how to set up a Data Connection, the delimiter character of each string component is separated by a semicolon. A good practice to avoid problems with passwords that have this character as part of them is always to use braces delimiting the beginning and end of the password.
Avoid ByPass
Use extreme care when assigning permissions to a Data Connection, as the string is typically set to a service account. It is very important to prevent a user who does not have access to a data source with his personal credentials from gaining access to a Data Connection.
Audit the Data Connections
Establish an audit cycle for all Data Connections. Ensure that only users who really need this connection have the respective permission.
Use Groups to Assign Permissions
When you use groups to assign permissions to a Data Connection, you are saving valuable time. Imagine a scenario where a new employee is hired and needs access to the connection. Adding it would be very simple, but what if there are 50 new users in this condition? Or even if the employee has moved to another area in the company and no longer needs access to the Data Connection.
Always prefer to manage in a group rather than individually.
Password Rotation
As a good practice, we use a service account in the configuration of a string for several reasons, one of which is the longer lifetime compared to personal credentials. Always pay attention to the password rotation period, and don’t forget to update the respective section in the Data Connection string.
Control Data Access
It is very important to carry out a cyclical process of reviewing access to Data Connections. Some users who need this access today may not need it in the future. Remember that we usually use service accounts and that they are extremely powerful. Therefore, not just any user should have access and not indefinitely.
Additionally, sometimes admins need to create DSN connections in the server instead of the DSN-less. As a best practice, a service account should be used for connections, but it is important to pay attention in cases where the user comes back with the service account to be used in the ODBC connection.
If the user creates the ODBC in his own machine using that account or even sends the information to another user, they will be able to bypass the connection tracking because the ODBC exists in the server, even though they are not using the Gallery Data Connection. If that happens, the admin will lose track of the accesses happening in the environment and who is using it.
Closing
The administration routine of an environment with Alteryx Server is not an easy task. However, using the available resources in a correct and efficient way, it is possible to facilitate the administration tasks and, at the same time, maintain a high standard of security and governance.
If you’re looking for professional help on how to manage your Alteryx Server environment or simply how to get the most value out of the platform and its features, phData would love to help! Reach out to us today for any questions, advice, and best practices.
