I have a SharePoint list that is on a SharePoint site accessible with claims-based authentication (or so IT has told me; I'm not 100% conversant with the details).
I am attempting to connect to the list in Excel, and do so in a way that doesn't require authentication every time for automation purposes. (I am used to establishing connections to SQL datasources where a user name and PW can be passed in the connection string, removing the need to manually authenticate at the risk of exposing login details if you know where to look.)
IT gave me a "dummy" login/PW that has read-only access to the list for this purpose, and doesn't tie to anything else (for security reasons). Instead of using Windows authentication, I would use a Microsoft Account in the Excel SharePoint data source wizard and then select "Forms Authentication."
I turned on the Macro Recorder, created a connection to the SharePoint list using the SharePoint list data source wizard in Excel, successfully authenticated with the dummy login/PW, designed the query of interest, and then stepped back into the code to try and reverse engineer what I needed.
Unfortunately, from what I could tell, the UN/PW weren't passed along in any visible way through a connection string or similar in Excel.
So I'm stuck in a situation where I need to manually authenticate for an automated report to run, which defeats the purpose of the automation.
Any suggestions on how to overcome this barrier? I'm not familiar enough with the nitty-gritty of the claims-based authentication system (or authentication in general) to know where I should start to dig in.
I was not able to find a way to automate the access to the SharePoint list. The connection string schema doesn't appear to support passing plaintext UID/PW parameters, and I was not able to successfully tinker with any of the settings in the connection to keep it authenticated indefinitely.
A possible workaround is to have a PowerShell script set up on the SharePoint server itself. This script would delete the old Excel file containing the contents of the list and then export the contents of the list onto a new file with the same filename. Potential code example at this link, but I am not able to test and IT is not interested in implementing this workaround.