Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How to format common ODBC DSN-less connection strings

EricWe
Alteryx
Alteryx
Created

Many people need to make ODBC connections to external data, but they may be unsure of how to format their connection strings, particularly with DSN-less connections. Here is a reference guide with examples of common DSN-less ODBC connection string formats. Here is a reference guide with examples of common DSN-less ODBC connection string formats. For connection strings not covered below, information can be found through the database or driver documentation or through online resources such as connectionstrings.org.

 

DSN-less connection strings help make workflows easier to export to other computers, as the importing computer does not need a matching DSN configured in the ODBC Data Source Administrator.

 

Prerequisites

 

  • Alteryx Designer
    • All versions
  • Alteryx Server
    • All versions
  • ODBC driver for external data sources such as SQL Server, Oracle, Cloudera, or Hadoop

 

Procedure

 

DSN-less connection strings typically include:
 

  1. The driver name
  2. Hostname/IP address with the port number
  3. Database name
  4. Depending on the authentication type: User ID and Password.

Note: DRIVER and DSN should always be capitalized. Alteryx looks for the driver's name and then hands the rest over to the driver. Other parameters should match what the driver expects so it can parse the parameters correctly. 
 

Standard ODBC DSN Connection string format

 

odbc:DSN={DSN_Name};UID={Username};PWD={Password};

 

DSN-less example

 

odbc:DRIVER={SQL Server Native Client 11.0};UID={Username};PWD={Password};DATABASE={Database_name};SERVER={Database_Host}

 

DSN-less trusted connection, the id and password are taken from the driver configuration

 

odbc:DRIVER={SQL Server Native Client 11.0};DATABASE={Database_name};SERVER={Database_Host};Trusted_Connection=yes

 

ODBC Connection string formats for Hadoop and Cloudera platforms

 

There are separate Hadoop and Cloudera platforms and one where both are combined into one. These platforms use the same ODBC driver types: Hive (data warehousing), Impala (massively parallel processing), and Spark (performance-based processing).

 

For DSN connections there is only parameter: DSN=[DataSourceName] (that's it).

 

In DSN-less connections for Cloudera and Hadoop, the driver, host, and port are all included in the connection string along with the authentication mechanism.

 

The authentication mechanisms for these driver types are the same (AuthMech in the connection string).

0 = No Authentication

1 = Kerberos

2 = User Name - the UID may be omitted with anonymous login enabled

3 = User Name And Password

 

No Authentication

 

DRIVER=Simba Hive|Impala|Spark ODBC Driver;Host=[Server];Port=[PortNumber];AuthMech=0;

 

Kerberos

 

DRIVER=Simba Hive|Impala|Spark ODBC Driver;Host=[Server];Port=[PortNumber];AuthMech=1;KrbRealm=[Realm];KrbHostFQDN=[DomainName];KrbServiceName=[ServiceName];

 

User Name

 

DRIVER=Simba Hive|Impala|Spark ODBC Driver;Host=[Server];Port=[PortNumber];AuthMech=2;UID=[YourUserName];

 

User Name and Password

 

DRIVER=Simba Hive|Impala|Spark ODBC Driver;Host=[Server];Port=[PortNumber];AuthMech=3;UID=[YourUserName];PWD=[YourPassword];

 

Optional parameters that may need to be added to a connection string if configured are ServerType, SSL, and ThriftTransport.

 

ODBC Connection string formats for Oracle platforms

 

Simba (validated driver) 
Driver=Simba Oracle ODBC Driver;Host=[Server];Port=[PortNumber];SVC=[DBService];UID=[YourUserName];PWD=[YourPassword];

Oracle driver
odbc:DRIVER={Oracle in OraDB12Home1};Dbq=TNSaliasName;UID=username;PWD=password

 

OCI Connection string formats for Oracle platforms (aka EZCONNECT - No TNSnames.ora file required)

 

oci:username/password@hostname:port_number/service_name

 

Method of using the TNS Entry Syntax as a DSN-less connection string

 

oci:username/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=service_name)))

 

Same TNS Entry Format for Oracle using SSL/TLS

(This is a good workaround if needing to use the TCPS protocol as a DSN-less connection string)
 
oci:username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=hostname1.xyz)(PORT=9999))(ADDRESS=(PROTOCOL=TCPS)(HOST=hostname2.xyz)(PORT=9999))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name.xyz))(SECURITY=(MY_WALLET_DIRECTORY=C:\app\client\Admin\product\12.1.0\client_1\Network\Admin\Wallet)(SSL_VERSION=1.2)(SSL_CLIENT_AUTHENTICATION=FALSE)))
 

 

Common Issues

 

Spoiler (Highlight to read)

If all the correct values for a connection string are not known, errors with the connection occur. In Designer, check the Results window and Engine log for details. For scheduled workflows, the Service log may be helpful, as well as the Gallery log if the workflow is running from the Gallery. A local system administrator may be needed to verify correct the parameters for the connection string.
 

Here are the default locations for Alteryx logs, check your System Settings if the logs are not found.

%ProgramData%\Alteryx\Engine logs

%ProgramData%\Alteryx\Gallery\Logs

%ProgramData%\Alteryx\Service

If all the correct values for a connection string are not known, errors with the connection occur. In Designer, check the Results window and Engine log for details. For scheduled workflows, the Service log may be helpful, as well as the Gallery log if the workflow is running from the Gallery. A local system administrator may be needed to verify correct the parameters for the connection string.


Additional Resources

 
Comments
sduraisamy
5 - Atom

Do we have DSN-less example for connecting to Oracle database?

EricWe
Alteryx
Alteryx

Hi @sduraisamy

 

My apologies for the delay in replying. I recently learned that I have to subscribe to an article after posting it to see when someone responds.

 

Here is a DNS-less Oracle connection string example from https://www.connectionstrings.com/oracle/.

 

Omiting tnsnames.ora

 

This is another type of Oracle connection string that doesn't rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have one of these files on the client pc.

 

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;

francis_oy
8 - Asteroid

@EricWe, Thanks a lot and this is really helpful. Would like to know if we have DSN less connection string for Redshift as well?

EricWe
Alteryx
Alteryx

Hi @francis_oy

 

Yes, there is a DNS-less option when connecting to Redshift. Here is an example of the syntax to use.

 

odbc:Driver={Amazon Redshift (x64)}; UID=database_id;PWD=database_password;Server=fully_qualified_database_name.redshift.amazonaws.com; Database=example_database;Port=5439;

Jberus
5 - Atom

Hi,

 

I'm hoping you can help me with an AS/400 iSeries DSN-less connection. The driver we use is iSeries Access ODBS and I'm trying to set up a DSN-less connection. I've looked at the following website for the connection string: https://www.connectionstrings.com/ibm-iseries-access-odbc-driver/; however, I'm not able to get it to work in Alteryx Designer.

 

What am I doing wrong? Here's the connection string I'm using:

 

ODBC:Driver={iSeries Access ODBC Driver};System=Test;Uid=Test;Pwd=__EncPwd1__;

 

I've tried putting in our password instead of EncPwd1 and it doesn't work either.

 

Thank you for any help you can provide!

EricWe
Alteryx
Alteryx

Hi @Jberus

 

Try this format with the database address and port included in connection string. It is from https://www.connectionstrings.com/ibm-db2/.

 

Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

Jberus
5 - Atom

Hi .@EricWe,

 

Thank you for replying! This doesn't work either; however, I've since learned that our organization has been able to set up AS/400 on the Alteryx Gallery, so we're looking into how they were able to get it to work. It wasn't working on the Gallery, which is why I was trying to go DSN-less.

ananthtony
6 - Meteoroid

Thanks for this information, I was able to follow and create DSN less connections for various databases. 

We have an AWS RDS Postgresql and I am trying to construct DSN less ODBC string to perform Bulk Load and I am not able to make it work. Can anyone help me with this?

 

Below is the string that I am using:

 

pgbl:Driver={Simba PostgreSQL ODBC Driver};Server=<server>;Port=5432;Database=<database>;UID=<user>;PWD=<Password>;SSLMode=prefer;

 

Thanks.

EricWe
Alteryx
Alteryx

Hi @ananthtony , 

 

Are you using the validated 1.4.45 version of the Simba PostgreSQL driver? It is available from downloads.alteryx.com on the Driver tab.

This article in our help documentation may be useful: PostgreSQL Bulk. It includes a link to PostgreSQL's driver configuration requirements. 

 

There are example PostgreSQL connection strings available here. If issues persist, please contact our Support Team and send an e-mail to support@alteryx.com.

ananthtony
6 - Meteoroid

Hi @EricWe 

 

Thanks for your reply and it is very useful. I was able to resolve the issue using the below connection string. 

 

pgbl:Driver={Simba PostgreSQL ODBC Driver};Server=<server>;Port=5432;Database=<database>;UID=<user>;PWD=<Password>;SSLMode=prefer;Database=<database>;

 

Thanks

eddiestuder
6 - Meteoroid

Hi @EricWe ,

 

Anychance you can give me a hand with an Athena connection. I am having a heck of at time getting the ODBC connection setup for this. I have searched all over and cant find an example of the DSN or DSN-less string anywhere

 

Thank you for this great post

EricWe
Alteryx
Alteryx

Hi @eddiestuder

 

What error is occurring? Be sure to use the Simba driver from downloads.alteryx.com. In the driver configuration, the logging option called log trace may be helpful. Also, Wireshark traces are good for debugging connections.

 

 

eddiestuder
6 - Meteoroid

Hello @EricWe ,

 

We had a session with the client and it looks like the issue is how they are trying to authenticate with the connection. I will post an update here if and when we find a solution

 

Thank you for your reply though just the same

ManasaPrabhu
5 - Atom

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 canvas error.

TestUser
5 - Atom

Do We have DSN-less connection string for Postgre SQL with certificate and Key ? 

EricWe
Alteryx
Alteryx

Hi @ManasaPrabhu

 

Please contact support@alteryx.com. Our Support team can help with researching for the root cause and finding a resolution. 

EricWe
Alteryx
Alteryx

Hi @TestUser

 

The SSL examples for PostgreSQL on connectionstrings.com may be helpful. 

zzy98y
7 - Meteor

Hi, 

 

I am trying to set up a Dremio DSN-Less connection string. 

 

In my DSN set up, I have use Encryption unchecked. 

 

What is the parameter for that in generic DSN-less connection string? Is it Encrypted=0?  

 

I tried that, it still failed. 

 

 

EricWe
Alteryx
Alteryx

Hi @zzy98y,

 

I checked on connectionstrings.com and in Dremio's documentation, but couldn't find a dns-less example. Dremio is not currently on our supported data source list.

 

This guide from Dremio seems to be the only thing available. Dremio client for Designer.

 

Here are our guides for generic odbc use: ODBC connections and Unsupported data sources. 

 

 

Also, there is an Ideas board, if you'd like to ask for Dremio to be added as a supported source.

patrick_digan
17 - Castor
17 - Castor

@zzy98y perhaps try UseEncryption=0 based on the Dremio document that @EricWe pointed to.

 

patrick_digan_0-1683577278035.png

 

zzy98y
7 - Meteor

It worked, Thank you so much for your help!!!