Data Connections
Important
To simplify the shift from Data Connections to DCM Connections, starting with the Server 2024.2 release it is possible to migrate all Data Connections and respective workflows to be replaced by DCM Connections.
First, it is necessary to migrate each data connection and then trigger the migration of workflows. Check our Maveryx Community blog post How to migrate Server Data Connections to DCM for more information.
Note
Server-FIPS
We removed the legacy Server Data Connections. You can use the Data Connection Manager (DCM) functionality instead. The DCM Enforced mode is enabled and can't be changed in the FIPS capable version of Server. DCM benefits include securely stored user credentials for data connections outside the workflow, increased security, improved password credential manageability, and synchronization of credentials between Server and Designer. Visit the DCM - Server help page for more information.
On the Data Connections page of the Server Admin interface, Curators (Server admins) can create Server data connections to allow Server users to connect to databases. Once configured, these data sources are available for users who have added the Server to select from a list and use.
Note
Data connections in Server are different than those in Designer. Go to Manage Data Connections and Manage In-Database Connections for more info about data connections in Designer.
Server data connections aren't available for in-DB connections.
Add a Data Connection
On the Data Connections page, select + New.
Select the connection type in the Technology dropdown.
In Name, enter a connection name.
In Host, enter the SQL database host name.
Select an authentication option.
Windows: Use the default credentials or enter specific credentials for the data connection.
SQL Server: Enter server credentials. Pipe characters aren't supported in passwords.
(Optional) Select Test Connection. A connection verification or failure message displays. You can save a connection if the test fails.
(Optional) Select Database to view a list of databases on the server. A list displays only if the connection test was successful.
Select a database to set it as the default database for this connection.
Select Type a database name and enter the name of database on the server. Ensure the name contains a forward slash (\) in the server name, such as RM-LT-013\SQLEXPRESS2.
Select Create.
All fields are required. After you complete required information, test the connection.
In Name, enter a connection name.
In Host, enter the Oracle server host name or IP address.
(Optional) In Service Name, enter the name of the TNS-based application on the network. This information is optional if only 1 service exists.
In Port, enter the network listening port for the Oracle database. The default is 1521.
In Username and Password, enter the username and password for the data connection. Pipe characters aren't supported in passwords.
(Optional) Select Test Connection. A connection verification or failure message displays. You can save a connection if the test fails.
Select Create.
In Name, enter a name for the connection.
Enter a connection string in Connection String.
Connection strings have to be 212 characters or less.
Connections that don't use a data source name (DSN) are recommended. This is also known as a DSN-less connection.
Copy Connection Strings from Designer
You can copy and paste connection strings from data connections you created in Designer. To do so, log into Designer as an Admin. Go to Options > Advanced Options > Manage Data Connections.
Select Create.
Testing Data Connections
Connection Tests on a Multi-Node Server
A multi-node configuration of Server has a controller and 2 or more worker machines. In this configuration, the test functionality tests the connection on the controller machine, not the individual worker machines. To ensure that the connection will work on any of the machines, confirm that the same database drivers and driver versions are installed on each machine.
Failed Connection Tests
Connection tests can fail for many reasons. These are the most common reasons for connection tests to fail.
You don't have access to the server or database. For example, in some cases, only the connection end user has the authority to access the server or database.
Network security can also prevent you from connecting to the server or database.
Server isn't able to reach the database server host. To troubleshoot, ping the database server host to ensure that there is network connectivity while logged in to the server where Server is installed,
The credentials for the database are invalid or don't have the appropriate access permissions. Contact the database administrator.
The database is offline. Contact the database administrator to confirm that the database is online and working as expected.
You are running a multi-node configuration of Server.
You can still save data connections that fail the connection test.
Default Credentials Used in Connections Tests
You can use Windows Authentication or SQL Server authentication to create a Microsoft SQL Server connection. For Windows Authentication, you can use default credentials or specific credentials for the data connection. You can set default credentials in multiple locations. The Server looks for available credentials in these locations. and selects the default credential in this order:
System Settings > Worker > Run As. Go to the Worker help page for more information on run-as users.
Windows Server Manager > Tools > Services. Locate the AlteryxService, and right-click and select Properties. The credentials on the Log On tab, under This account.
Local machine.
The connection test fails if credentials are invalid.
Share a Data Connection
After you create a data connection, return to the Data Connections page to share the connection with users or custom groups to use in Designer.
Important
To enable the workflow to be run by specific users or groups, you need to contact a Curator (Server admin) to ensure the needed data connections are shared with them.
On the Data Connections page, select the data connection you want to share.
On the Edit Data Connections screen, select Users or Custom Groups.
Select + Add.
Enter the name of a user or group.
Select the user or group.
Select Add.
The user, group, or studio you share the connection with is listed on the corresponding tab. Go to Input Data Tool and Manage Data Connections in the Designer help to see how users access and manage shared connections in Designer.
Important
For Microsoft SQL Server connections, confirm that the user has the same or more recent version of Microsoft SQL Server Native Client installed on their computer. Go to Troubleshooting.
Revoke Access to a Data Connection
To revoke a user's access to a connection, select Delete in the 3-dot menu for this user. To confirm, select Delete.
Edit a Data Connection
On the Data Connections page, select the data connection you want to edit.
On the Edit Data Connection screen, select Edit.
Edit information in Name or Connection String.
Select Save.
On the Users or Custom Groups tabs you can share the connection with users and groups.
Delete a Data Connection
To delete a connection, select Delete in the 3-dot menu for this connection. Then select Delete.
Migrate Data Connections and Workflows to DCM
To shift from Data Connections to DCM Connections, migrate all Data Connections and respective workflows.
Migrate a Data Connection to DCM
As the first step in Data Connection migration, it is necessary to link the Data Connection to a DCM Connection, to be used as a replacement in workflows. Alteryx will assist you in finding the right DCM Connection and share the DCM Connection with the same set of users and user groups.
On the Data Connection detail page, select Migrate to DCM.
On the Migrate Connection to DCM screen, select the DCM Connection you want to associate the Data Connection with from the list of all DCM Connections present on Server.
Alteryx tries to find the best matching DCM Connection and loads it into the dropdown. It also groups all available DCM Connections into categories, depending on the type of match with the Server Data Connection. The groups are Perfect Match, Partial Match, Unresolved Vault, or Unmatched. For more information about these groups, go to Migration Tool.
Choose whether you want to Share to Existing Users. This shares the DCM Connection for collaboration with the same set of users and user groups as the migrated Data Connection. This option is enabled by default.
Note
The DCM Connection gets shared for collaboration with all associated credentials. This is to reflect the Data Connection sharing behavior, allowing users to see and use the DCM Connection in Alteryx Designer. If you want to change which of the credentials are shared with others, initiate the Sharing for Collaboration first and then proceed with the migration.
You must be the DCM Connection owner to be able to share it with others. You may still proceed with the Data Connection migration, but it’s then on the owner to share the DCM Connection manually.
While workflows using Server Data Connections can be run by any Server user (with permission to the workflow), that’s not the case for DCM Connections. Sharing to Run on Server on the DCM Connection has to be initialized for all users, who would be running this workflow.
DCM Connections can’t be shared with Private Studios. These are currently ignored during this step.
Select Migrate.
Once this process is complete, the Data Connection is in a Migrated state. This means the Data Connection is still available to everyone, but can’t be shared or modified anymore, with all such actions now only available on the DCM Connection instead. No changes are made to the existing workflows at this point.
It is possible to Remigrate the Data Connection, which triggers the migration process described above. It allows you to choose a different DCM Connection to migrate to. Note: This doesn’t affect the sharing of the DCM Connection from the initial migration.
Migrate Workflows to DCM
After linking DCM Connections to your Data Connections, you can proceed with workflow migration. This process scans all workflows on the Server for Data Connections and replaces them with the linked DCM Connections. Workflows not containing any Data Connections are skipped.
On the Data Connections page, select Migrate Workflows to DCM.
To confirm the workflow migration, select Migrate in the Migrate Workflows dialogue.
All workflows stored on the Server are then checked for Data Connections to be automatically replaced by the associated DCM Connections.
Workflow is migrated only when all data connections used in that workflow are migrated to DCM.
A new version is created for each workflow migrated.
While it is possible to initiate the workflow migration repeatedly, only one migration can run on the Server at a time.
The status of the migration is visible on top of the Data Connections page. Refresh the page to update the current status.
Supported Technologies and Limitations
The following workflow file formats are supported:
YXMD (regular workflow)
YXWZ (analytic apps)
YXMC (macros)
Data connections uploaded as a data package (YXZP format) are not migrated because they don't include the metadata for the connections. As a result, they can't be migrated automatically.
Workflow is migrated only when all data connections used in that workflow are migrated to DCM.
All Server aliases in the Input Data and Output Data tools are gathered and replaced during workflow migration. Other tools are unsupported.
On a multi-node Server setup, the migration log will be present only on a node where the migration runs. Such is selected at random.
Troubleshooting
No, you can create and share a data connection without testing it. You can also save a data connection if it fails a connection test.
Connection tests can fail for many reasons. These are the most common reasons for connection tests to fail.
You don't have access to the server or database. For example, in some cases, only the connection end user has the authority to access the server or database.
Network security can also prevent you from connecting to the server or database.
Server isn't able to reach the database server host. To troubleshoot, ping the database server host to ensure that there is network connectivity while logged in to the server where Server is installed,
The credentials for the database are invalid or don't have the appropriate access permissions. Contact the database administrator.
The database is offline. Contact the database administrator to confirm that the database is online and working as expected.
You are running a multi-node configuration of Server. Go to the Testing Connections section in this article for info on connection tests on a multi-node Server.
You can still save data connections that fail the connection test.
You can use Windows Authentication or SQL Server authentication to create a Microsoft SQL Server connection. For Windows Authentication, you can use default credentials or specific credentials for the data connection. You can set default credentials in multiple locations. The Server looks for available credentials in these locations. and selects the default credential in this order:
System Settings > Worker > Run As. Go to the Worker help page for more information on run-as users.
Windows Server Manager > Tools > Services. Locate the AlteryxService, and right-click and select Properties. The credentials on the Log On tab, under This account.
Local machine.
The connection test fails if credentials are invalid.
There are many reasons why a user can't see a Server data connection in Designer. Follow these steps to troubleshoot.
Confirm that you shared the data connection with the user. Edit the data connection and look for the user in the list.
Confirm that the email address the user uses to log in to the Server UI is the same address you shared the connection with.
Ask the user to remove the Server from Designer and re-add it using the correct credentials.
If you are sharing the connection to a studio, confirm that the user has access to that studio.
A user might receive this error in Designer or Server when attempting to use a data connection created on a machine that uses a different version of a Microsoft SQL Server driver than the machine on which the data connection is being used.
The machine has to have the same version or a more recent version of a Microsoft SQL Server driver. For example, the error message displays when the machine on which the data connection is being used uses version 10.0 of a Microsoft SQL Server driver, and the machine on which the data connection was created uses version 11.0.
The user who created the workflow in Designer and saved it to the Server might not have permission to use the shared data connection. To troubleshoot, follow these steps.
Open the AlteryxService log (AlteryxServiceLog.log). The service log is saved to a location set during configuration. Go to Configure and Use Logs for more info.
Search for the time stamp when the error occurred and review the error description.
If the description reads "Unable to find permission for data connection: [data connection name]" the user doesn't have access to the data connection.
Edit the data connection and share it with the user.