Error: Data source name not found and no default driver specified

[PeterP]

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:
 

Or when uploading a workflow during validation:

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:
 

Environment

• Alteryx Designer
• Alteryx Server
  • Version ≥11.0

• A workflow that contains an ODBC connection

Diagnosis

Confirm if your database connection is a DSN connection or a DSN-less connection.

1. If you are using a DSN connection, your connection string looks like:

odbc:DSN=SQLServerODBC

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

2. If you are using a DSN-less connection, your connection string looks like:

odbc:Driver={SQL Server Native Client 11.0};DATABASE={Database_name};SERVER={Database_Host}

 

Cause

The workflow is not able to successfully run because the 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 exact DSN or Driver/driver version on their computer.
• The workflow gets sent to the Alteryx server to run on a scheduled frequency where the DSN or Driver/driver version does not exist on the Server machine.

Solution

1. Install the driver. For both DSN and DSN-less connections, you must have the driver installed on the machine. The download links to many of the drivers can be found here.
2. Additionally, for DSN connections, ensure the DSN is also set up.
   1. In ODBC Data Sources (64 bit) window 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 DSNs here. If you are setting this up on a Server, you must use a System DSN.
   2. 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.
   3. Once you've established the DSN on your machine, you will now be able to successfully re-run the workflow!

Additional Resources
 

• How to format common ODBC DSN-less connection strings  

[jon1]

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

[nbrenner]

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?

[MRapp]

Facing the same questions as @nbrenner 

[jdm5650]

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

[nbrenner]

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

[jdm5650]

@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:

[PeterP]

@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

[DMURPHY]

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.

[LisaL]

@nbrenner 

If you are connecting with a DSN, the server must have a System DSN and the name must exactly match the contents of the connection string.  If you are publishing from your own Designer to a gallery and you're using a DSN, then the DSN name on your computer (regardless of whether it is User or System DSN) must exactly match the one on the server.

If you are using a DNS-less connection string, then only the driver name must match (and that's standard, so you probably won't ever run into a problem there).  This article is a good overview of how to set everything up optimally.  You may also want to refer to this resource on all kinds of connection strings, linked from the aforementioned article.

@DMURPHY Depending on how credentials are set up on the gallery, there may be subtleties.  For example, if you are using Workflow Credentials, you may need to make sure that the test on the server includes the user credentials rather than the server's default credentials.  I've also seen situations where proxy configurations are different on servers than they are on users' machines.  If you've tried everything mentioned, you can enlist the help of your IT group and DBA to make sure that ports are communicating properly and that permissions are set appropriately for your network.

[smysnbrg]

This was very helpful in guiding me towards a solution that was solved simply by ensuring that the driver versions were the same on the designer side and the server side.

A designer had a workflow connected to a SQL Database with the SQL native client connection.  However, the gallery connection was ODBC Driver 13 for SQL Server.  Once we installed the ODBC 13 driver on the designer's machine, he was able to update the input tool connection and then make successful use of the Gallery connection.

Thanks,

S

[pcowmanriverside]

Thank you! This solved my issue: If you are setting this up on a Server, you must use a System DSN. I had set it up as User DSN. Now to solve another issue....

[ntobon]

Another thing to check is that the user has the Gallery URL.  If not, go to File > Open workflow > New Gallery. Add the URL and connect. 

[ManasaPrabhu]

I am trying to connect my Oracle DB through ODBC using DSN-less connection. However, I am getting this error on canvas = "Error Data source name not found and no default driver specified" while trying to input the tablename or query space. I have used the below: 

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword.

Request your help on this. While running the flow on gallery, I get the error as "No table chosen; Please select a Table from data source." as I am unable to choose any table or query in the tool because of the above error.

[nbrenner]

@ManasaPrabhu , as you mention this is occurring on the canvas, is this a problem you are experiencing before publishing to server?  Did it work on server and was pulled down locally and is failing? 

Note:  It helps to have the same driver locally as is found on the server.  Do the local machine and server both have a current TNSNAMES.ORA file for the connections?