SQL Server Connection to Server Issue

Question

I have issue trying to connect SQL Server (Window Authentication) on Alteryx Server. I use below string to connect.

odbc:Driver=SQL Server;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

It worked fine on Alteryx desktop but have error when I published the workflow to Server. I added the connection to Server but still didn't work.

"Server does not exist or access denied".

Alteryx Error.png

fmvizcaino · Answer

Hi @thaodinh9 ,

Since the driver you are using is a bit old, maybe this new server is throwing an error because of that. I would look for other SQL drivers that are also available on the server.

Remotely access your server and check the ODBC admin 64-bits for the driver names.

Another option, if you are a curator on your server is to create the DSN-less string from the data connections page. You can copy the connection string created and paste on your designer. You only have to update the password after pasting it.

https://help.alteryx.com/current/en/server/administer-alteryx-server/data-connections.html#idp417556

It should look similar to this: DRIVER={ODBC Driver 17 for SQL Server};UID=username;PWD=__EncPwd1__;DATABASE=databaseA;SERVER=servernameA.database.windows.net

Another driver that is common to be used is DRIVER={SQL Server Native Client 11.0};UID=username;PWD=__EncPwd1__;DATABASE=databaseA;SERVER=servernameA.database.windows.net

Best,

Fernando Vizcaino

thaodinh9 · Answer

Hi Fernando,

Thanks so much for your help. I was able to change from Windows Authentication to SQL Authentication.

On a different thread, you has helped me setting up DSN-less on Alteryx Server and it worked previously (database A). I tried to do the same for a different database (B) however I got error "SQL Server does not exist or access denied - Driver's SQLSetConnectAttr failed" when running on Alteryx Server. The only different I can see is that they have different server ending - windows.net and company.com. I don't know what to do from here to resolve this problem. Of course, ODBC driver is installed.

Here is the DSN-less string for datababse B. This works fine on Designer but not Server

odbc:DRIVER=SQL Server;UID=username;PWD=__EncPwd1__;DATABASE=databaseB;SERVER=servernameB.company.com

Here is the DSN-less string for previous database A that worked well on Server

odbc:DRIVER=SQL Server;UID=username;PWD=__EncPwd1__;DATABASE=databaseA;SERVER=servernameA.database.windows.net

https://community.alteryx.com/t5/Alteryx-Server-Discussions/SQL-Server-ODBC-Connection-Issue-on-Alteryx-Server/td-p/1292999

SQL Error.png

fmvizcaino · Answer

Hi @thaodinh9 ,

I`m sorry to hear it is still not working on your Server, but I promise we are almost there.

Our personal accounts are usually blocked for these kinds of things, so if you are an admin on your server, you can just follow the tutorial below and enable your account to  Log on as a batch job and also Verify the Secondary Logon service is running to enable alternative users to run other services.

https://help.alteryx.com/20214/en/server/administer-alteryx-server/credentials/configure-required-run-as-user-permissions.html#configure-required-run-as-user-permissions

Best,

Fernando Vizcaino