SQL Driver Error - Trying to connect an SQL data source to a workflow template

[DaThiery]

Hello Alteryx Community

I am having difficulty connecting Alteryx to a template that requires a Database Alias. Here are some of the errors I am receiving, any thoughts on things to check/troubleshoot would be awesome.I am able to connect each input data tool manually to the SQL database via the connection manager, however setting up the automatic connection via a database alias is not connecting. I have tried both running the Driver 17 version and Driver 18 version. They had different errors as listed below:

Driver 18
Internal Error SQLDriverConnect: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted. [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection.]

Driver 17
DbFileInput (1): Unable to transalte alias
DbFileInput (1): Workflow was Cancelled by Properties Settings

Any thoughts on these errors would be much appreciated!

Thanks!

[Rdannayak]

Did you get the issue resolved ? did you find any workaround ? if so can you please share the details 

[dataguyW]

Depending on how your gallery connection string is created on the server, you may need to ensure you have the exact driver version.    If it is called out in the connection string then it will run on your local PC with that same information.  If you have a different version it will fail.     Also, if the connection string is set up to require a DSN using ODBC administrator then you must also have the exact same name on your machine.  I can't remember if it is case-sensitive or not, but I just do it the same.   Maybe that will help as those are the two most common reasons our users can't connect.

[Rdannayak]

Thanks @dataguyW  I will check the details and will update you, I have ODBC 17 in my Desktop and ODBC 18 in my server i think this is something that's messing it up but on top of it our DB Admin mentioned that the Trust certificate has to be checked as well so that this might fix the issue but, as soon as i find some resolution will post it here so that it might help people like me 

[dataguyW]

It took me a few iterations with connections to realize how and why gallery connections work the way they do.  If the connection attribute is given on the server it will attempt to substitute and use that on the runtime environment in use.   Unless you are using DSN-less connections where you have all the information in the connection string, there is some interplay between your local setup of the DSN and what the server may have stored in the connection string.

If you just passed the name of the DSN and a user/pass it would look to see that you have the DSN locally with a matching name and then run with how you setup the connection.   If the connection string states you need a specific version, then you need that version when it runs local.    Each of these methods has their pro/cons from what I've seen.    DSN-less has less headaches when it comes to interaction issues or setup/maintenance on the designer's machines.   Hopefully you get it working.

[Rdannayak]

@dataguyW  I found the fix and its the issue with the ODBC Drivers, Having ODBC 17 on desktop and 18 on Server created the issue so, the workaround which i got from Alteryx team is

Step 1: Install save ODBC in both Server and Desktop 

Step 2: Under your installed desktop machine drive which is default to C:\Users\rdannayak\AppData\Roaming\Alteryx\Engine  , Rename the GalleryAlias.xml to GalleryAlias_OLD.xml

Step 3: Restart the Desktop machine 

Step 4: When you are connecting via Server connect it using Data connections under Admin portal, Image is attached for reference from 2025.1 version

Step 5: When connecting the data source using the Connection string approch, when you hit data source select other and you can add string in it

Step 6: Conecting string can be founnd in your desktop typically something like this

odbc:DRIVER={ODBC Driver 18 for SQL Server};DATABASE=Database;SERVER=Servername;Trusted_Connection=yes;Encrypt=No

Step 7: Add user to the connection and try sync all now and you should get it

else

Try this and it should work as well

I'm wondering if we can use a DSN-less string with optional parameters as a workaround. Please try adding the following in the connection string: "TrustServerCertificate=True"
Example:

odbc:Driver={ODBC Driver 17 for SQL Server};UID=*; PWD=*; DATABASE=*;SERVER=******;TrustServerCertificate=True;Encrypt=True;Integrated Security=False;

These steps should help you get moving or anyone who is having trouble connecting SQL server via Gallery

[dataguyW]

If you reference the DSN name, you don't need to reference the driver version, BUT if you do then they need to be the same on the user's machine and the gallery because it is specifically stating to use that version.    We do have users download/install the exact driver, but in many cases it is easier to just not mention it.   DSN-less will need to be the most specific because it will need all the information and you can't rely on anything being known to the user or server that isn't in that string.

Glad they walked you through it.   I would have been as easy as just downloading and using the driver version 18 and you would have been done.  @Rdannayak    It really depends on how many users you would have to walk through the configuration and most people don't know how to do any of this so I try to pick my solution in terms of # of phone calls and headaches 😁

[Rdannayak]

@dataguyW  True, Most of the people wont get it and since i am in small organization i can do this but, when i think this * 100 its hectic process and need to go through DSN approach