Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Data source name not found and no default driver specified

Alteryx
Alteryx
Created

Issue

 

The Alteryx server is unable to run a workflow that runs properly on the user’s local machine where the workflow was created.

 

[ODBC Driver Manager] Data source name not found and no default driver specified


This error can be encountered when running an app or workflow on a Gallery:
 

galleryerror.PNG

 

Or when uploading a workflow during validation:

 

savegallery.PNG

Users will also receive the following error when opening a shared workflow where the Data Source Name does not exist on the user’s local machine:
 

resultserror.PNG

 

 

Environment

 

  • Alteryx Designer
  • Alteryx Server
    • Version 11.0

 

Diagnosis

 

Confirm that the data source name (DSN) specified in the workflow is pointing to an existing DSN in your machine’s ODBC Data Source Administrator:

 

***You must have the driver installed on the machine prior to using this guide. The download links to many of the drivers can be found here.
 

  • On your local machine, search for ODBC Data Sources (64 bit)
  • The DSN referenced in the input data tool within the workflow must be listed under either User DSN or System DSN tabs.

odbchighlighted.PNG

 

Cause

 

The workflow is not able to successfully run because the input data tool cannot reach the database in the following scenarios:
 

  • The workflow creator sends a workflow with a database connection to a colleague who does not have the same DSN on their computer.
  • The workflow gets sent to the alteryx server to run on a scheduled frequency where the DSN does not exist on the server machine.

 

Solution

 

  1. You must have the driver installed on the machine prior to using this guide. The download links to many of the drivers can be found here.
  2. Once confirmed that the appropriate driver is installed, select to add from either the user DSN or System DSN tab. Generally, the User DSN is what most users will have access to and what is commonly used. You can learn more about the difference between User and System DSNshere.
  3. In order to resolve the error at the local level or on the Alteryx Server, you will need to set up the corresponding DSN found in the app/workflow. The following Community article will provide you step by step instructions on how to set up an ODBC connection.
  4. Once you've established the DSN on your machine, you will now be able to successfully re-run the workflow!
Comments
5 - Atom

Follow up to this. I had this issue and determined that the data source name within the ODBC configuration needs to match exactly. 

7 - Meteor

I'm confused.  Do we have to create an ODBC connection on the Alteryx Server for Alteryx Gallery to be able to use it (note we can't as users and not IT server admins)?  Why can't Alteryx Gallery use the driver with connection string we are entering?

 

We have been smashing our heads against the wall on this.  We can't test things on the server as end users, we have to rely on IT to do that, and our IT staff are not data staff, they are infrastructure, so they aren't sure what to try.  

And so, for every data connection we need to ask IT to setup for us, we need to send them credentials for each data connection? 

Or, do we need to have them create an account in Active Directory and give it permissions on the Gallery box for run as and always use that account as the one we use for ODBCs?

5 - Atom

Facing the same questions as @nbrenner 

5 - Atom

@nbrenner @MRapp 

 

I'm the Alteryx platform manager at my company, which includes server administration, here's what i've found with this (note that my experience is only with ODBC connections and that i'm far from an expert):

 

From an IT perspective, they'll need to do two things. 

1st is to open the ports in the firewall between your Alteryx server machine and the database server that you are trying to connect to.  This may not be an issue everywhere but it's how it works at my company.  User machines are not firewalled off from database servers, but application servers are, so anytime someone wants to connect to a new database, I need to submit a request to our security team to open the necessary ports between the application server and the database server.

2nd is to create an ODBC connection on the application server.  They will not need a username and password, the users can use their own when setting up the connection.  They will just need to give it a DSN name and enter the server:port information.

 

From a user perspective, they then need to set up an ODBC connection on their machine with the exact same details as the one that was created on the application server.  As long as the name matches when the workflow is published, the Alteryx server will be able to use it's on ODBC connection but the user's credentials to connect to the database.

 

 

7 - Meteor

@jdm5650 

 

So, when setting up the ODBC, safe to assume it should be a System DSN?

 

Also, just leaving the User ID field blank and not testing the connection (on the server end)?  

 

In the case of Oracle I guess this would be a simple as putting the TNS Service Name and...that is all (sans whatever name you want it called), and clicking OK.

 

OracleSystemDSN_ODBC.jpg

5 - Atom

@nbrenner 

 

It has to match between the user's machine and the application server so I'd actually go with the User DSN in case the user doesn't have the admin rights to create a system DSN.

 

Unfortunately, I don't have any Oracle connections so I'm not sure exactly what you should put there.  We use SAP HANA so a completed connection for us looks more like this:

jdm5650_0-1594750542862.png

 

 

 

Alteryx
Alteryx

@nbrenner

 

You can leave the UserID blank, and simply add an alias name for data source name and the server tns as well. I would test the connection here to confirm it works on the odbc level. 

 

When you manage these connections in Alteryx, as long as the DSN is created with the same names on both the user and application server, it should recognize the connection string. You can manage these data connections once created inside of alteryx by going to Options  > Advanced Options > Manage Data Connections.


To help you better understand how things work, when a user creates a workflow with a DB connection, that connection string is unique to the user. When you publish a workflow to the server, you are now sending that workflow to the server to run, which will default run as that service account. 

 

 

What I have found as an easier way to manage data connections, is to create them on the Server via Gallery Connections and then sharing these downstream to users. This article is a great resource to understand gallery connections: https://community.alteryx.com/t5/Alteryx-Server/Gallery-Data-Connections-FAQ/ta-p/452293

 

 

 

6 - Meteoroid

I'm still getting the same error. 

 

I'm using SQL 17 Driver 64 bit locally to match Server

I've tried User DSN and System DSN

Name matches exactly locally and on the server

Connection Test is successful on both server and local

 

I don't know what else to try.