Data Connections
On the Data Connections page, create and share data connections with users in your company to use in Designer.
To go to the page, click Data Connections on the navigation bar.
Add a Connection
- On the Data Connections page, click Add New.
- Click Connection and select a connection type:
Microsoft SQL Server
- In Name, type a connection name.
- Type the SQL database host name in Host.
- Select an authentication option:
- Use Windows authentication: Use the default credentials or enter specific credentials for the data connection.
- Use SQL Server authentication: Enter server credentials. Pipe characters are not supported in passwords.
- (Optional) Click Test Connection. A connection verification or failure message displays. A connection can be saved if the test fails.
- If you selected Use Windows authentication, select an option, then click Test:
- Default: Use the default credentials for data connection testing. See Troubleshooting.
- Use specific test credentials: Enter specific credentials for the data connection.
- (Optional) Click Database to view a list of databases on the server. A list displays only if the connection test was successful.
- Click a database to set it as the default database for this connection.
- Click Type a database name and type the name of database on the server. Ensure the name contains a '\' in the server name, such as RM-LT-013\SQLEXPRESS2.
- Click Save.
Oracle
All fields are required. After you complete required information, test the connection.
- In Name, type a connection name.
- In Host, type the Oracle server host name or IP address.
- Type the name of the TNS-based application on the network in Service Name. This information is optional if only one service exists.
- In Port, type the network listening port for the Oracle database. The default is 1521.
- In Username and Password, type the user name and password for the data connection. Pipe characters are not supported in passwords.
- (Optional) Click Test Connection. A connection verification or failure message displays. A connection can be saved if the test fails.
- Click Save.
Other
- In Name, type a name for the connection.
- Paste the connection string in Connection String. Connection strings must be 212 characters or less.
- Click Save.
View Saved Connections
Connection tests on a multi-node configuration of Alteryx Server
A multi-node configuration of Alteryx Server consists of a controller and two or more worker machines. In this configuration, connection testing tests the connection on the controller machine, not the individual worker machines.
Any of the available worker machines could be used to run a workflow. 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
A connection can still be saved if the connection test fails. Tests may fail for various reasons including:
- You do not have access to the server or database. For example, only the connection end user may have authority to access the server or database.
- Network security prevents you from connecting to the server or database.
Share a Connection
After you create a Data Connection, return to the Data Connections page to share the connection with users to use in Designer.
- On the Data Connections page, click the name of the connection you want to share.
- In the Edit Data Connections window, click Users and Studios.
- Type the name of a user or subscription. A list appears as you type.
- Select a user or subscription.
For Microsoft SQL Server connections, confirm that the user has the same, or a more recent version of Microsoft SQL Server Native Client installed on their computer. See Troubleshooting.
To revoke a user's access to a connection, click X next to the user's name.
The user or studio you share the connection with are listed on Edit Data Connections > Users and Studios.
See Input Data Tool and Manage Data Connections in the Designer Help to see how users access and manage shared connections in Designer.
Edit or Delete a Connection
To edit a connection:
- On the Data Connections page, click a data connection.
- In the Edit Data Connections window, edit information in Name or Connection String.
- Click OK.
To delete a connection, select the trash icon next to the connection name.
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.
- Alteryx Server may not be able to reach the database server host. While logged in to the server on which Alteryx Server is installed, try pinging the database server host to ensure that there is network connectivity.
- The credentials for this database may be invalid or may not have the appropriate permissions. Contact the database administrator.
- The database may be offline. Contact the database administrator to confirm that the database is online and working as expected.
- You may be running a multi-node configuration of Alteryx Server. See Connection tests on a multi-node system section in this article.
You can use Window 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. Default credentials can be set in multiple locations. The Gallery uses default credentials, looking for available credentials in this order:
- System Settings > Worker > Run As (See Worker)
- Windows Server Manager > Alteryx Service Properties > Log on as: This account
- Local machine
The connection test fails if credentials are invalid.
There are multiple reasons why a user may not be able to see a Gallery connection in Designer. Try one of these recommended actions to resolve the issue.
- Confirm that the connection was shared with the user. Edit the data connection and look for the user in the Users and Studios list.
- Confirm that the email address the user uses to log in to the Gallery is the same address you are using to share the connection. Ask the user to remove the Gallery 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 may receive this error in Designer or Gallery 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 must have the same, 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 Gallery, may no longer have permission to use the shared data connection.
- Open the Alteryx Service Log (AlteryxServiceLog.log). The Service Log is saved to a location set during configuration. See Controller and Log Files.
- Search for the time stamp when the error occurred and read the error description.
- If the description states "Unable to find permission for data connection: [data connection name]," the user does not have access to the data connection.
- Edit the data connection and share it with the user.
Use the Gallery REST API to Access Gallery Data
Use the Gallery REST API to access data from your private Gallery:
- Data connections created in the Gallery.
- System data connections created on the server on which Server is installed.
- Workflows.
- Package of a workflow or application.