I have issue connection SQL Server to Alteryx Server. I tried adding SQL Connection through DCM Alteryx Server and installing driver on ODBC Data Source Admin on laptop. I faced 2 issues:
When testing on Designer, the data source supposes to have a table called Company_Mapping but it is not available in the Input connection.
When I choose a random available table and pushed the workflow to Server, I got "Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data Source name not found and not default driver specified"
When working the database connections between designer and server, there are some important things to keep in mind
Both Designer and Server have the same driver version installed. This is more of a best practice for some cases, and mandatory for others (for example, working with DSN-less connections)
Best to use a dsn-less connection - your image (connection setup) shows a DSN name configured and that means your server must have the same DSN name configured there, which can cause some issues or delays if you don`t have access to the machine the server is installed
By using a DSN-less connection, you only need to have the same ODBC driver installed there and it becomes your job to create the connection on DCM
Agree with the previous response. We've also found that the workflow run-as credentials override any credentials embedded in the Input/Output tools for SQL Server. The only way to avoid that is to:
1. Use a DSN-less connection string as Fernando mentions, AND
2. Add your desired ID to the SQL Server database as user type "SQL user with login", NOT "Windows user"
Thanks so much! I follow your provided link and it worked like a charm.
About not able to access certain table, it turns out there are multiple databases under my schema. With DSN connection, I can't specify my desired database (it keeps hitting "master"). With DSN-less connection, I can specify with DATABASE={Databasename}.
