I had to retrieve data from a SharePoint list using a SSIS package running in a SQL Server Agent Job. The security maze came to play with me when I discovered that the account running the job is not even the job’s owner but the SQL Server Agent Service Account. For security purposes that account doesn’t have access to SharePoint, and I would like to keep things as they are, but at the same time I need to read that SharePoint list data.
The workaround is pretty nice and involves creating a Credential object in SQL Server that contains the username and password of the account that can access the SharePoint list and a Proxy object in SQL Server Agent that maps to the credential object created before. Finally we just need to configure the SQL Server Agent Job step that access SharePoint to Run as using the proxy object. Easy and elegant! 🙂
Step by step guide:
Create a new Credential and a Proxy account to give SQL Server Agent Jobs access to SharePoint
Set the credential with the username and password of the user account that can access SharePoint:
Create a new Proxy in SQL Server Agent:
Configure the proxy to map with the credential created in the step before and with the SQL Server Integration Services Package subsystem:
Finally configure the Run as property of the step in a SQL Server Agent Job to use the new proxy: