At my organisation, we came across an issue when changing from hosting Alteryx on a single server to instead several machine licenses. This is for Alteryx Designer Desktop.
When trying to use both DCM and connection strings to reach our SQL DB using SQL Server login auth, we were getting an odd error. Error 28000 was the most common as this
When we used 'Test Connection', it would always return successful connection. We could even see the query editor and all column names for our tables.
However, when we went to run a flow, the input tool would error, saying that the login had failed for the SQL Server account.
After weeks of going back and forwards between Alteryx settings and trying different DSN files as well as user settings in SQL Server, we finally found the solution and it may help someone stuck in the same position.
The password for the SQL Server account was generated using a strong password generator. Our generated password happened to contain several pipe characters ' | '. This was causing the connection string in Alteryx to get confused when reading the password - as it would look for a table named by the password string that was after the first pipe character.
For example, the password ABC|DEF would have, for some reason, tried to query for a table in the DB called DEF and use ABC as the password string.
This is an error that bugged us for a while and it should have been easier to diagnose. It is probably in the docs somewhere but maybe a warning in DCM or elsewhere would be useful for users such as ourselves?
Anyway, just posting this here in case anyone finds it useful! Cheers!
