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
- Alteryx Server
- ODBC driver for external data sources such as SQL Server, Oracle, Cloudera, or Hadoop
Procedure
DSN-less connection strings typically include:
- The driver name
- Hostname/IP address with the port number
- Database name
- 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
