DSN-Less Connections and DCM Connections to Sharepoint and Microsoft SQL Server

[tbuenaflor]

Hi All! Apologies if the answer to this is straight forward as I'm still getting the hang of Server/Gallery. I have two questions about DSN-less connections and using DCM Connections. 

1. My IT department has created a DSN-less connection using a service account that connects to a Sharepoint site/page.  How to I actually output and bring in data from Sharepoint? I don't see the connection when using the Sharepoint tools, and I'm confused as to how to configure an Input tool. 

2. I've created a connection using DCM that connects to a database in Microsoft SQL Server. It connects and runs fine in Designer, however I get an error when trying to upload into Server. Do I need SQL Server installed on the Remote Desktop where our Alteryx Server is actually housed? Does the account that I'm using to connect to the database need admin rights? 

[fmvizcaino]

Hi @tbuenaflor ,

Lots of things to mention here.

1.  My IT department has created a DSN-less connection using a service account that connects to a Sharepoint site/page.  How to I actually output and bring in data from Sharepoint?
   
   1. AFAIK, there is no such thing as a DSN-less connection for Sharepoint. No hate, just wanted to mention so you can double-check with them what was created because admins are usually responsible for the Gallery Data Connections and DCM is more developer-based (but they can definitely create a connection to sharepoint and share with you)
   2. Share DCM connections for developers to use on Designer was included only in 23.2. If you are using a lower version, sharing won`t be possible
   3. Assuming you are on 23.2, they have to share the connection with your user (Go to Share a Connection with Users and Groups - https://help.alteryx.com/20223/en/server/use-alteryx-server-ui/data-connection-manager--server-ui.ht...)
   4. If they shared the connection with you, depending on how they configured DCM, you will have to sync your Designer with your Server manually (https://help.alteryx.com/current/en/designer/tools/dcm---designer.html#idm45001218724240)

      1. Go to File > Manage Connections to open Connection Manager.

      2. Select Synchronize.

      3. For a new Server connection...

         1. Select + New.

         2. Enter the Serverinstance URL and select Connect.

         3. Enter the Email and Password and select Sign In.

      4. Select the Server instance to sync with.

      5. Select Synchronize.

      6. Select Done.

   5. If that doesn`t work, there are some things you can do on your Designer to force DCM to be used and also allowed on the Server (https://knowledge.alteryx.com/index/s/article/How-to-Set-up-DCM-for-Designer-and-Server#:~:text=Shar....)
2. I've created a connection using DCM that connects to a database in Microsoft SQL Server. It connects and runs fine in Designer, however I get an error when trying to upload into Server. Do I need SQL Server installed on the Remote Desktop where our Alteryx Server is actually housed? Does the account that I'm using to connect to the database need admin rights? 
   1. You don`t need SQL Server installed there
   2. You do need to have the ODBC driver installed on your machine and on the server and if you are using DSN-less connection, the driver version must also match, example: Microsoft ODBC Driver 18 for SQL Server 
   3. The user doesn`t need admin rights
   4. If syncing is not configured to be automatic (It is something your Alteryx server admin would probably know), you have to sync by yourself exactly as I mentioned before.

Woof, this took me a while to answer 😁

Let me know if it is all clear or if you have follow-up questions. 

Best,

Fernando Vizcaino

[tbuenaflor]

@fmvizcaino  I cannot thank you enough for responding, and very thoroughly too! I think I might be good now with #2. 

For #1, I'm probably using the wrong terminology lol. IT had created a connection string using the below example that connections to our SharePoint site and has made it available on Server under data connections. 

https://www.connectionstrings.com/sharepoint/

URL=http://sharepointServerName/;User=myUsername;Password=myPassword;SharePoint Edition=SharePoint Online;