Data Connections
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.
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.