Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: format common ODBC DSN-less connection strings

Alteryx
Alteryx
Created

How To: format common ODBC DSN-less connection strings

 

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

 

ODBC driver for external data source such as SQL Server, Cloudera, or Hadoop 

 

Products

 

Designer, Gallery

 

Procedure

 

DSN-less connection strings typically include: 1. the driver name, 2. server address, 3. database name or port number, and depending the authentication type, 4. user id and password.

 

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

 

Common Issues

 

Spoiler

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

Additional Resources

 

Comments
5 - Atom

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

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;

7 - Meteor

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

Alteryx
Alteryx

Hi @francis2016

 

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;