Data Connections

Version:
2021.1
Last modified: February 11, 2021

On the Data Connections page of the Gallery Admin interface, Curators (Gallery admins) can create Gallery data connections to allow Gallery users to connect to databases. Once configured, these data sources are available for users who have added the Gallery to select from a list and use.

Data connections in Gallery are different than those in Designer. Go to Manage Data Connections and Manage In-Database Connections for more info about data connections in Designer.

Gallery data connections aren't available for in-DB connections

Add a Data Connection

  1.  On the Data Connections page, select Add New Data Connection.
  2. Select the connection type in the Connection drop-down. 
Microsoft SQL Server Setup Steps
  1. In Name, enter a connection name.
  2. In Host, enter the SQL database host name
  3. 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 aren't supported in passwords.
  4. (Optional) Select Test Connection. A connection verification or failure message displays. You can save a connection if the test fails.
  5. If you selected Use Windows authentication, select an option, then select Test.
    • Default: Use the default credentials for data connection testing. 
    • Use specific test credentials: Enter specific credentials for the data connection.
  6. (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.
  7. Select Save.
Oracle Setup Steps

All fields are required. After you complete required information, test the connection.

  1. In Name, enter a connection name.
  2. In Host, enter the Oracle server host name or IP address.
  3. In Service Name, enter the name of the TNS-based application on the network. This information is optional if only 1 service exists.
  4. In Port, enter the network listening port for the Oracle database. The default is 1521.
  5. In Username and Password, enter the username and password for the data connection. Pipe characters aren't supported in passwords.
  6. (Optional) Select Test Connection. A connection verification or failure message displays. You can save a connection if the test fails.
  7. Select Save.
Other Data Connection Setup Steps
  1. In Name, enter a name for the connection.
  2. 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.

  3. Select Save.

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 Gallery 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 to use in Designer.

  1. On the Data Connections page, select the pencil icon for the data connection you want to share.
  2. On the Edit Data Connections screen, select Users and Studios.
  3. Enter the name of a user or subscription. 
  4. Select the user or subscription.

The user or studio you share the connection with is listed under Edit Data Connections > Users and Studios. Go to Input Data Tool and Manage Data Connections in the Designer help to see how users access and manage shared connections in Designer.

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 the "x" icon next to the user's name.

Edit or Delete a Data Connection

  1. On the Data Connections page, select the pencil icon for the data connection.
  2. On the Edit Data Connections screen, edit information in Name or Connection String.
  3. Select Save.

Delete a Data Connection

To delete a connection, select the trash icon next to the connection name.

Troubleshooting

Do I have to test every data connection?

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.

Why did my connection test fail?

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.

What are my default credentials for data connection testing?

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 Gallery 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.

Why can't a user see the data connection I created and shared?

There are many reasons why a user can't see a Gallery 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 Users and Studios list.
  • Confirm that the email address the user uses to log in to the Gallery is the same address you shared the connection with.
  • 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.
What does: "Error [IM002] [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified," mean?

A user might 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 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.

Why can't a user run a workflow in Gallery that uses a shared data connection?

The user who created the workflow in Designer and saved it to the Gallery might not have permission to use the shared data connection. To troubleshoot, follow these steps.

  1. 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. 
  2. Search for the time stamp when the error occurred and review the error description.
  3. If the description reads "Unable to find permission for data connection: [data connection name]" the user doesn't have access to the data connection.
  4. Edit the data connection and share it with the user.

 

Was This Page Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support. Can't submit this form? Email us.