SQL Server ODBC Connection Issue on Alteryx Server

[thaodinh9]

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"

[fmvizcaino · Accepted solution]

Hi @thaodinh9 ,

When working the database connections between designer and server, there are some important things to keep in mind

1. 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)
2. 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
   1. 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 
   2. https://knowledge.alteryx.com/index/s/article/How-To-format-common-ODBC-DSN-less-connection-strings-...

Related to having access to the table, this could be a permission issue or maybe you are connected to the wrong database (master)

Best,

Fernando Vizcaino

[CMichaelBNSF]

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"

[thaodinh9]

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}.