SQL Agent Man needs a Perm

No, not Ted Malone. And no, not curly hair. But yes, I’m serious.

If you’re using SQL Server Agent jobs to execute your SSIS packages on a schedule (and this is something many of us do) then you need to ensure that SQL Server Agent has the necessary permissions, or else you’ll get an error like this:

“Executed as user: server\SYSTEM. The package execution failed. The step
failed.”

I’ve seen this problem popping up several times lately on the MSDN SSIS Forums, so I thought I’d add a quick note here. There are two main things that you can do to get around this:

  1. Configure the SQL Server Agent service to run in the context of a Windows account that has the necessary permissions. This is simple enough, but doesn’t give very granular control, as this will apply to all jobs that run.
  2. Create a SQL Server Agent Proxy and grant that proxy account the necessary permissions.

SQL Server Agent Proxies are new in SQL Server 2005. so not everyone knows about them yet. In my opinion they are the right tool for the job here. They’re easy to set up, provide granular access and let you easily follow the principle of least privilege when configuring all of your scheduled SSIS packages.

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

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