Can't seem to connect through OleDB on Alteryx Server, works perfect in Alteryx Designer

[Daniel96]

Hi all,

We've always used ODBC connection to connect to our DWH, one day one specific view on a table in the DWH became extremely slow which made the runtime go from 1 minute to 1.5 hours+.

I red that an OleDB connection could fix this issue, so I changed it up in Designer and voila, instant runtimes again. To futureproof our flows I'd like to change all connections to OleDB, starting with this specific DWH connection.

The issue is that when i save the flow to the server, I can't get the server to connect to the DWH through OleDB.

I have tried many different connection strings, some give different errors than others. Also we've installed the following driver in Alteryx server: MSOLEDBSQL19. This hasn't fixed the issue

I will list some connection strings i've tried, with the error they give me on Alteryx server. Most work perfectly in Alteryx Designer, but give the following errors on Alteryx Server:

1.

odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxxxxx;Data Source=xxxxxxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxxxxxx;Use Encryption for Data=False;Tag with column collation when possible=False

0 records read from ...........................................

2.

odb:Provider=MSOLEDBSQL19;Server=tcp:xxxx,1433;Database=xxxxx;Trusted_Connection=Yes;

We've tried so many variations of MSOLEDBSQL and SQLOLEDB that I can't even list them all.. We've also seen the following error: 

DBA have checked if certificates could be the issue, they couldn't find any that could be causing an issue.

TLS is disabled on DWH, could this be the problem?

The connection strings were generated using the following method, using windows authorization in Alteryx Designer:

Like I said, in Designer the connection works perfect, the issue is running the flow in Alteryx Server...

If you have a foolproof connection string which should work on Alteryx server 2022.2, please let me know. We will be upgrading to Alteryx Server 2024.1 soon.

Thank you in advance,

Daniel