Impersonate SQL Server Agent Job step

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:

Advertisements
Tagged with: , , ,
Posted in SSIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: