Alteryx Server Data Connections - How best to set up an ODBC connection?

[Ollie]

Hi,

Hopefully someone with Alteryx Server experience can help me!

I have been trying out the Data Connections feature on Alteryx Server 11.3.

It works fine for MS SQL Server connections but for ODBC connections (in particular, a connection to Redshift), how do I set up an ODBC connection using the Data Connections feature such that when an Alteryx Designer user connects to this Gallery connection, the user doesn't need to set up the ODBC connection on their own machine?

[SophiaF]

Hi Ollie,

To add a Data Connection on your Gallery to something other than SQL Server or Oracle, you will first need to generate the Connection String. To do this, you can:

1) Create the Connection to Redshift in Alteryx Designer in an Input tool, then copy the connection string that is created.

2) Create the connection string by hand - the website https://www.connectionstrings.com/ is often helpful, but doesn't contain Redshift

For Redshift specifically, you can get the connection string from within the AWS management console: Configuring Connections in Amazon Redshift.

Once you have the connection string, in the Gallery Admin page, go to Data Connections > Add New > Other. There, you can name the connection and paste the connection string.

[Ollie_Power · Accepted solution]

Hi SophiaF,

Thanks for your help. This worked after I added "odbc:" to the start of the connection string and rearranged the string so that the UID and PWD came before the DATABASE, SERVER and PORT. Note sure why the order mattered - it didn't matter when creating a connection in Designer but it did matter when creating the connection in the Gallery.

Adding screenshot below as help for others!

[WillyD99]

Hello,

I am looking for some help.  I need to add a SQL bulk load connection to the gallery for individuals to use on my team.  I currently have the ODBC connection for SQL 13 (Show Below) setup, but this is super slow to load data into our database.  When using the SQL bulk load option the processing time is drastically reduced.  However, I don't know what the connection string is for bulk load to add when I am in Data Connections.

SQL ODBC 13 Connection String:

odbc:DRIVER={ODBC Driver 13 for SQL Server};DATABASE=XXXXX;SERVER=XXXXXXXX;Trusted_Connection=yes

Any assistance would be greatly appreciated.

Thanks in advance