Nowhere is this more evident than when it comes to deploying SSIS packages to run via a scheduled SQL Server Agent job. I wish I had a dollar for every time someone posted this message on the SSIS forums on MSDN:
"I built my package and it works great, but when I deploy it to SQL Server Agent it fails. Is this a bug in SSIS?"
Yes, I blogged on this back in May. But I've seen this same problem in one form or another at least a dozen times this week on the forums, so I wanted to post on the same topic again. It's important. And although no one part of it is really complex or difficult, there are enough parts that come together during deployment that a lot of people are really struggling with it, despite the volume of documentation and other information that's available online. Hopefully this post will help, at least a little.
Here's the deal :
- When you run a program in Windows, that running process has your permissions. This is why Microsoft Word can save files to your user drive, when only you have permissions to access that folder. Each running process has the access permissions of the user account that launched it.
- When you run a program from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.
- By default, the SQL Server Agent service runs in the security context of the Local System account. This means that it has lots of privileges on the local computer, but none whatsoever on the network.
See the problem? Nine SSIS packages out of 10 need to access remote resources. These could be SQL Server databases (remember how we use Windows Authentication whenever we can?) or text files on a file server, or an Access database (MDB file) on a file server, or any number of other things. SSIS packages generally exist to move data from one place to another place, and generally those places aren't all on the same machine, so the packages need to execute in the security context of a user account that can access all of those resources on all of the machines involved.
Please let me repeat/rephrase that, because it's the really important thing in this post.
"SSIS packages need to execute in the security context of a user account that can access all resources on all of the machines involved in the package processing."What does this mean to you?
It means that when you run the package yourself, the package has your permissions. It can do what you can do and access what you can access, for the simple reason that you ran it. And if you didn't have permission to access all of those resources when the project started, you discovered this problem early on, and got the access you needed because otherwise you couldn't have built the package at all in the first place.
So of course it runs for you!
But what about that SQL Server Agent job?
It should be pretty easy to figure out from here, right?
- You need to create (or work with the people in charge of your network to get created for you) a Windows user account that has all of the permissions necessary to access the resources your package uses, databases, file system folders, shared folders and so on.
- Then you create a SQL Server Agent Proxy based on that Windows user account.
- And then you tell SQL Server Agent to run your job step in the context of this proxy.
That's really how simple it is. And the craziest part of all? Microsoft has already documented the heck out of this online. Here are a few key links that will go into much more depth than I have here, so if you're still having problems, why not click on them and read what Microsoft has to say.
- From SQL Server Books Online, "How to Run a Package Using a SQL Server Agent Job": http://technet.microsoft.com/en-us/library/ms139805.aspx. (Please note that this page has links to sub-articles and related articles. Click on them and read those pages too.)
- From the Microsoft Support Knowledge Base, "An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step": http://support.microsoft.com/kb/918760. This article goes into many scenarios that could cause an SSIS package to fail when executed in a SQL Server Agent job, not just the security related stuff in this blog post, but from my experience, it's the security that is tripping up most people.
 No, SSIS isn't really "built on" AD, but read on and you'll see what I mean.
 Yes, I know I'm simplifying things quite a bit here, but that's intentional.
 If you have Active Directory, this needs to be a domain account. If not, then you have a bunch of reading to do on Windows security, because I'm not going there today.
 If you are that person, please post a comment to let me know.