This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 04-14-201603:04 PM - edited on 12-29-202106:27 AM by MichaelAd
How To: Connect to an ODBC Data Source
Alteryx can connect to databases using different methods. This guide will focus on setting up an ODBC connection to SQL server, however, the same general process can be followed to connect to any database via ODBC.
Alteryx Designer or Server.
ODBC driver for the database (if it is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found here. If you are unsure of where to get the driver or which driver to use, please contact your IT or database admin for this information). For Designer 2021.3+, a supported 64-bit driver is required; see also here.
User and System DSN (Data Source Name) Before proceeding, it is important to decide whether to use a System or a User DSN. A DSN is a symbolic name that represents the ODBC connection. It stores connection details like database name, directory, User ID, password etc. and is utilized when making a connection to an ODBC data source. Please refer to this Microsoft article for further information.
There are various types of ODBC data source types.
The System DSN is available for all users on the system. To create it, administrative privileges are required on the Windows OS. Often the customer's IT has to be involved in creating a System DSN. The typical use case would be sharing a data connection inside the organization. For a data connection to use a DSN on Server, it needs to be created as a System DSN.
The User DSN on the other hand is created for a specific user. Only the user who created the DSN can use it.
Both types of DSNs can be configured via the ODBC Admin app.
Please note that Alteryx Designer / Server do not support File DSNs.
2019.2 and later
Open Alteryx Designer and drag and drop the “Input Data” tool onto the canvas
Click the drop-down "Connect to a File or Database."
The Single Access Point data connections dialogue will open up. Choose the relevant data connection and select ODBC.
Click the drop-down to "Connect to a File or Database." Then select "Database Connection" > "New ODBC Connection…" The "Data Link Properties" window should appear.
Click the dropdown to “Connect to a File or Database.” Then select “Database Connection”->”New ODBC Connection…” The ODBC connection window should appear
A new window ODBC Connections will open
Click the “ODBC Admin” button to go to the Windows ODBC Administrator app to set-up the connection.
In the ODBC Data Source Administrator, click the “Add…” button to set-up the new connection. You will have the choice between adding a new Data Source using a User DSN or System DSN, see this KB article for more details on the difference.
You will be directed to a window to choose the driver to use for the connection.
Select the driver and click “Finish”
Select a name for this connection
Click the dropdown to find the SQL server you wish to connect to or type in the server name
On the next screen, choose which type of authentication you will be using:
You can choose between Integrated Windows authentication, Integrated Active Directory Authentication or login ID
Choosing Windows Authentication and Active Directory Integration requires no additional information as the user ID you are logged in to windows as will be recognized
Using login ID authentication will require you to enter the user ID and password.
The DBA or IT will be able with further details on the above settings
Once you’ve chosen your login method, click the “Next” button
Click “Next” through the menu screens. Change any optional settings via the menus if you wish to adjust them. Generally the default configuration should provide a successful connection, however in case of doubt feel free to check with your DBA.
After clicking the “Finish” button, click the "Test Data Source..." button
If the “TESTS COMPLETED SUCCESSFULLY!” dialogue appears, you have successfully set up your connection
Click “OK” twice to exit the connection wizard
Click “OK” to exit the ODBC administrator
In the alteryx ODBC connection window, select the Data Source Name you’ve just created. Enter a username and password if required (optional) and push “OK”
The Choose Table or Specify Query window should pop up. If there are tables already in the database, select a table and push “OK”.
2019.2 and later
Upon successful connection, the ODBC connection will now appear under "recent"
Upon successful connection, the ODBC connection will now be stored in the “Input Data” tool’s initial dropdown