DSN-less Connection Not Working in Gallery

[Csand]

My organization is deprecating DSN-based ODBC connections, and my team doesn't want to depend on the central team for creating new Gallery connections. So, we are attempting to create non-DSN connection strings to connect to Oracle and SQL server databases.

I have created an "Input Data" tool with the following connection string that works perfectly on my local PC, but produces the following error when it runs on the Gallery. The Password option is "Hide (Default)". I have also verified that I can ping the database from the server running Gallery. Does anyone know what might be causing the issue, or have potential fixes to try?

odbc:DRIVER={SQL Server};UID={usernamehere};PWD={__EncPwd1__};DATABASE={databasenamehere};SERVER={serverhostname\serverinstancename,portnumber};

Error: Error SQLDriverConnect: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (Tool Id: 1)

[MarqueeCrew]

The driver likely has not Been installed on the server. Ask your admin to install it and all should be well.

Cheers,

Mark

[rafalolbert · Accepted solution]

Hi @Csand,

Two things i would look at are:

- driver availability and compatibility between local and server

- server reference to be FQDN (Fully Qualified Domain Name) - for example i work from UK, but my server is US based, so every server reference has to be unambiguous like server.uk.company.com

I think your specific problem might be linked with the 2nd point.

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[Csand]

Hi @MarqueeCrew and @rafalolbert  - thanks for your quick responses!

Here's more info:

- Driver Installation: I have confirmed that the driver is installed on the server by the admin team sending me a screenshot of the drivers.

- Driver availability: this is actually a potential issue. The driver on the server is 6.03, but i'm using version 10.00. I'll follow-up to see if we can get the latest driver installed.

- FQDN reference: I am unfortunately already using the FQDN. In fact, I originally tried the "alias" listener name that is resolved by DNS, but had already switched it to the full hostname with domain suffix that it resolved to.

[Csand]

So - we found the problem today. It turned out to not be driver related after all. While we were able to ping the server, a telnet request on the SQL Server port confirmed that the Alteryx gallery server could not connect to the server on that port. So, we'll be opening a firewall port to resolve the issue. Thanks all!

[Csand]

Solution confirmed! Firewall port opened and the connection is now exporting the data successfully. Thanks again, @MarqueeCrew and @rafalolbert !