SQL Server Connection to Server Issue

[thaodinh9]

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

[fmvizcaino]

Hi @thaodinh9 ,

Since I don`t know exactly what you have done already, let`s start from the beginning.

There are two things (I think) it could be happening.

1. DSN not created

   1. Did you create the DSN on OBDC admin? Did it work when you hit the test button?

      Here you can find how to create the DSN: https://support.microsoft.com/en-us/office/administer-odbc-data-sources-b19f856b-5b9b-48c9-8b93-0748...

2. Access denied for the account running the Alteryx Server
   1. The connection will use the service account or run as credential configured on your server as the login. If you want the workflow to use your credential, you will need to activate the option to allow credentials
   2. https://help.alteryx.com/20214/en/server/administer-alteryx-server/credentials/select-the-workflow-c...

Best,

Fernando Vizcaino

[thaodinh9]

Hi @fmvizcaino ,

I did setting up DSN on OBDC admin and also tried DSN-less as you suggested last month on another thread. Those methods work on Desktop but nothing works on Server with Window authentication. I have "Use Default Credentials" for workflow credentials settings so it could be the reason why the connection work on desktop but not server.

I changed it to "Allow User to Select" and tried to add credential (my Windows log in) on Server but got "Invalid Username and Password". I don't know what I missed.

[fmvizcaino]

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

Best,

Fernando Vizcaino

[thaodinh9]

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

[fmvizcaino]

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