Thursday, December 20, 2007

SSIS and SQL Server Agent

SQL Server Integration Services is great. But SSIS deployment can sometimes be painful. One of the things that makes it painful is that SSIS is built on a relatively complex set of technologies (like SQL Server, Windows Server and Active Directory[1], for starters) and when you go to deploy your ETL solution, you need to have a pretty good understanding of how those technologies work - and how they work together - or else you could well be in for a rough ride.

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 [2]:
  • 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?
  1. You need to create (or work with the people in charge of your network to get created for you) a Windows user account[3] that has all of the permissions necessary to access the resources your package uses, databases, file system folders, shared folders and so on.
  2. Then you create a SQL Server Agent Proxy based on that Windows user account.
  3. And then you tell SQL Server Agent to run your job step in the context of this proxy.
One, two, three, Go!

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.
Are you ready to go? I sure hope so. Hopefully the next time someone runs into this problem I can simply point them to this URL instead of having to type up this information each time. And maybe, just maybe, someone will search and find this article before they cry for help on the forums.[4] And if that happens, it will all be worth it, and these blisters on my fingers won't seem to hurt at all anymore... ;-)

[1] No, SSIS isn't really "built on" AD, but read on and you'll see what I mean.
[2] Yes, I know I'm simplifying things quite a bit here, but that's intentional.
[3] 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.
[4] If you are that person, please post a comment to let me know.

46 comments:

Eric said...

[4], I'm that dude!!

Thanks for this post. I really do hate posting questions on forums so I usually spend a couple of hours trying to find the answer myself.

After many worthless and vague articles from Microsoft (et al.) suggesting that the answer was changing the package ProtectionLevel, setting a PackagePassword, etc., I found your solution.

Thank you. It worked perfectly. Now, if I could just get detailed information about why a package failed in the first place...

Matthew Roche said...

Thanks, Eric!

It's always a huge pleasure to know that these posts are actually helping people. The forums are great (without them I'd have no ideas for blog posts ;-) but they're not the same thing as being able to post an article that addresses many people's problems proactively instead of just responding to individual problems as they're posted.

In the context of getting "detailed information about why a package failed in the first place" when running a package from SQL Server Agent, there are two simple things to do:

1) Do not use the Integration Services job step type. Use a CmdExec job step to execute DTEXEC.EXE. You get more detailed information this way if something does go wrong, and it's very easy to change the reporting switch to include information messages (or whatever) when you need more detail.
2) On the second tab of the job step editor, specify the path to a text file to which the output from the EXE being executed will be written. This is something of a low-tech hack, and is probably not appropriate for production environments, but it is a simple and effective way to get all of the DTEXEC error and warning output into a single text file for review, analysis and (if necessary) sharing.

Of course, the information you'll get is the same as what appears in the Output window in Visual Studio. I'll leave it up to you to say if this qualifies as "detailed information" but it's the best that SSIS produces.

Good luck!

Leonard said...

Your article is awesome and it addresses most of the issues that we would encounter on SSIS scheduling. I do have one question though, Microsoft recommends a domain account to create the proxy account. Suppose my destination is on a hosted facility where my domain account cant authenticate to? How would proxy handle a SQL account?

Matthew Roche said...

Leonard - The reason that Microsoft recommends using a domain account is that in most real world situations, the processes being run by SQL Server Agent (like our SSIS packages) exist on multiple computers. In a domain environment you can have a single user account that has permissions on any machine in the domain, but in a workgroup (non-domain) environment each server maintains its own usernames, passwords and permissions. This makes things annoying when trying to figure out who has what permissions where, because the whole concept of "who" is now ambiguous.

So to give you a short answer, the simplest thing to do is to create user accounts on each machine involved in your scenario with the same user name and password. (Yes, you must then manually keep all this in sync.) Use this account for your proxy, and assign the matching account on each computer the permission it needs to do whatever your package needs it to do.

The long answer is that this workaround technique doesn't always work. With Windows 2003 Microsoft tightened up security to make things like this less reliable, so cross your fingers. (Please note that at this point I'm getting away from my technical comfort zone, so if I'm missing some vital point, let me know.)

If I were you, and had to make this work in a hosted environment, I would not try to do anything myself. I would call up my support representative for the hosting provider, explain what I needed to do, and ask him how he was going to help me get it done. Your hosting provider knows their setup, and neither you nor I can do more than guess, so this is really the only "sane" way to proceed.

Good luck!

Jay said...

Nice article, the only issue I have is when you create a package by default the "sensitive" information is encrypted with your credentials. This becomes an issue when you try to run a package under a different user context, likd the server agent you refered to.
The only solution to this I found was to save that packages to the database server with a security of "Depend on Database security".

Matthew Roche said...

Thanks for the feedback, Jay.

The ProtectionLevel property of the Packages can certainly trip you up. You can, however, use the DontSaveSensitive setting, use Package Configurations to drive your connectionstrings from config files and still deploy to the file system. But you are certainly correct in that the default setting can make deployment complicated.

meahl1997 said...

Just wanted to let you know that someone did read this blog before posting questions. Thank you!

Matthew Roche said...

Update: I've just posted aa follow-up blog post that discusses the different job step types for SSIS packages in Agent:

http://bi-polar23.blogspot.com/2008/06/ssis-and-sql-server-agent-choosing.html

Derek said...

Your blogs are awesome.

When you setup a proxy account, does that account have to have the proper permissions on all databases the SSIS package utilizes even if the SSIS package is using an XML configuration file that has a username/password hardcoded in the xml config file? Shoudln't the SSIS package use that connection when it is executed?

Bernardo said...

Hello! I'm totally new to this stuff and love to read this kind of straight-forward explanations.

However there's something I still don't understand (I'm sure it will sound silly to many of you): You suggest the creation of a domain account for running the SQL Server Agent. I asked my support team and they do not create this kind of "system accounts". My user account has all the access I need. Should I have the Agent run under my account? Is this a total non-sense from a security perspective?

Thanks!

s98ssr said...

I have a Stored Procedure that calls an SSIS package. It works great. Along the same lines, I have created another SSIS package to ftp a file using WinSCP. The package by itself runs great and transfers the file. However, when I run the package through the stored procedure, it fails. I tried different ways but with no resolution yet. I had the SP display the command it is executing and ran that from the command line and it works. Just can't get it to work from the Stored Procedure. Any ideas or help is greatly appreciated.

Matthew Roche said...

@Bernardo - Yeah, that's pretty much crazy talk. ;D

Having a batch job run in the context of your user account is undesirable for several reasons, including

1) When you change your account password, the batch job will break until you update it too.
2) Anyone who can change the job definition can put in ANYTHING and it will RUN AS YOU. Imagine getting called into your manager's office and having him show you a security log of things your account did...

With this said, I would recommend talking to your server team and ask how they currently handle services and/or scheduled/unattended jobs that require access to resources on other machines. If your organization is large enough to have a server team and well-defined policies, they are sure to have seen this scenario before. Whatever solution they use should be able to be applied here as well...

Nalangkilli said...

Really good ...It exactly solved my problem...Thanks

shamilton said...

I like your style of writing. It's very matter of fact. Thanks for the post.

dghnfgj said...
This comment has been removed by a blog administrator.
RRave said...
This comment has been removed by a blog administrator.
Dan said...

It took 4 hours but your blog post is exactly what I needed. Thanks much. Also kudos for keeping it simple. Stuff would be so much easier if all people stuck to explaining things this way.

The 4 hours to find you is the same old story. What magic words do I type in google that gets me to your info.

thanks,
Dan...

ps. Is it just me or do most error messages just not get the job done?

Matthew Roche said...

Hey Dan - I'm glad this information could help you out. After 15 years as a trainer, my writing style very closely follows my presentation style, and people either love it or hate it.

And yes, error messages are often not what they could be. My belief is that a platform like SSIS is built on so many layers and integrates with so many external systems that having a 1:1 mapping between an error cause and an error message is pretty much impossible. Thank goodness for the internet...

Vanessa said...
This comment has been removed by the author.
Vanessa said...

Hey Matthew! Thanks for the incredibly easy-to-read post for a SQL newbie like myself. I admit that I also posted to a forum before seeing this, but was glad when I found it. I know Oracle and Unix like the back of my hand, but SQL/Windows is killing me. I found all the Microsoft articles you detailed but as Eric said, those articles are pretty vague (at least for someone like myself who is new and doesn't apparently understand the basics yet). Unfortunately your solution didn't fix my particular problem. Go figure! I must be missing something else. Nevertheless, I've already bookmarked this to come back to later and hopefully it'll help me put all the pieces together.

Tripp Southern, DBA said...

Matthew;

Thanks for the clear and concise post. I've been banging my head against the wall for 4 days, off and on, trying to get this to work with no luck. I must have read and re-read all of the BOL chapters and most of the MS articles on the subject, but it never clicked until I read your post.

Turns out I was making it more complicated than it needed to be. Your post cleared it up for me after the first read through. All it really took was you listing the steps followed by "That's really how simple it is". It really was just that simple.

Keep up the good work!
Tripp Southern, MSSQL/Oracle DBA

Matthew Roche said...

@Tripp - Thanks a ton for the positive feedback. It's a great way to start the day to find that someone has gotten some value out of this blog.

dev3001 said...

This is a great post. However, after following through with credentials, proxies, and job step configuration, I was STILL getting Access Denied on a CmdExec job step. After a few hours of struggling, I FINALLY figured out that not only do you need all that, but, obviously in retrospect, the user context that the Agent service runs as (NETWORK SERVICE in my case) also need read-access to the exe or bat file you want to kick off.

Checkout my blog http://sql-dotnet.blogspot.com/

George said...

can i do this under sql 2000 ?


thanks

Matthew Roche said...

@George - No. Pretty much everything in this post is specific to SQL Server 2005 and later.

Ryan Williams said...

I was thrilled to find your post. But, I'm still a little stuck on something. If you have a package that's only using SQL authentication (because it's not connected to a domain), would that still need to run under any particular account?

Maybe I have a different problem though. The problem I'm having is that the SQL Agent Job runs fine, but nothing happens, the SQL Job reports it successfully ran, but the package didn't do anything. When I run the package in VS, it works great.

Matthew Roche said...

@Ryan - The answer will depend on what else your package is doing and what errors are being logged, but the short answer is yes. Your Windows security context is used when you access any resource - depending on how locked down your server, this could include local file system resources as well as remote resources.

But... if you package runs without error, this could well be a different problem altogether...

VillageSmithy said...

I'm having a similar problem to Ryan.. I'm checking for a txt file in a network folder, if I find it I import it and archive the file.

The SSIS pkg ran great while in development in Studio, job runs without error in production - but nothing gets done. But, if I copy the text of the jobstep to the Execute Package Utility and run it under my cred or the intended credentials, it works just fine.

I've been using your SSIS deployment methodology with great success since I saw you at the 2008 SqlCon in Vegas, but this has me wondering..

Ideas, comments or snide remarks?

Syne said...

Hi,
I have a SSIS package which is create an excel file to a server folder. When run as SSIS package, it work fine, but when run as sql server agent, it failed.
After read your post, i think is the permission problem. So,I try to create a new sql server agent proxy. And run sql server agent as this proxy, but the job still failed.

Is it possible that,the sql server agent run at a server, and the excel file creat at another server folder with sql server agent? How the set the permission for the sql server agent to access the destination server?

Thanks for help.

Ashwin said...

Matthew,

Great Work on this !! I wish MS would take some lessons from you on how to make documentation / help more useful, and easy to understand, instead of just sticking to documentation formats and making sure all specified formats are included on the page.

Your post was easy to follow, simply written and perfectly to the point for newbies and experts-in-a-temporary-hole !! :D

Keep up the good work !!

James said...

You suggest:-
"3. And then you tell SQL Server Agent to run your job step in the context of this proxy."

But the only user available in the "Run As" dropdown list for my Job step is SQL Agent Service Account. So I seem to be stuffed.

This is after I created a new credential and a new proxy.

VillageSmithy said...

I'm posting this to answer my own question.. The solution was to use UNC paths.. \\ServerName\D$\myfile.txt Using mapped drives worked fine when I ran it manually as myself or under the service account designed for it, but not as a scheduled job.

Matthew Roche said...

@James - When you create a proxy you can specify what subsystems (which relate to the various types of job steps) the proxy can access. This delivers more granular security (which is good) but also adds more complexity (which is not good) to the setup. Based on what you're describing, it looks like you've missed this step, just as I missed it in my post. ;-)

@Smithy - I'm sorry I missed your original comment. This is definitely the right solution, as mapped drives are specific to a user session, which you don't have when running a package as part of a SQL Agent job...

Pramod said...

I have some SSIS packages deployed on server with windows authentication (my user account) using protection level as ServerStorage. Also SQL admin has created jobs to run these packages, admin account is the owner of all the jobs. The jobs are running fine till date but now since my work is over my account will be deleted from AD and I am not sure whether the packages will work or not thru jobs. It would be great if you can help me understanding the impact.

Matthew Roche said...

@Pramod - Your packages should continue to run without any problem. It's the execution context that matters, not the account that deployed them.

David said...

In SQL Server Agent, the "SQL Integration Services" type does not appear. Any idea why?

Matthew Roche said...

@David - My guess would be that SSIS is not installed on the server. This is a separate component from SQL Server agent and the database engine.

carl.benjamin said...

very nice article but didn't quite solve my problems.
i get the error:
Description: The task "FTP Task" cannot run on this edition of Integration Services. It requires a higher level edition.
we are questioning where everything should be running. We deployed the package from one machine (with SSIS only) onto another machine (with SQL DB only) and cannot get the package to run. Does SSIS have to be installed on the SQL DB server?

carl.benjamin said...

very good article but didn't quite answer my questions.
we created a package on serverA (running SSIS only) and deployed to serverB (running SQLServer only). we get error: "The task "FTP Task" cannot run on this edition of Integration Services. It requires a higher level edition."
Does SSIS have to be installed on serverB in order to execute.

Mike said...

Oh my gosh, this totally fixed our issue! Thanks so much, Matthew! Replication can now begin! :D

bumpagab said...

Matthew,

Humanitarian post! I have had this wrong for years. Thanks for the "all you need to know" info.

Tara said...

This was good source and I totally get your pain on having to repeat yourself on this subject. You mentioned that there was decent documentation on the web. Well, not really and what Microsoft writes - well really sucks in terms of being useful and they tend to go around in circles not really coming to a soild logic behind solving the issue.

Also you are kinda a jerk about offering your help here in that people should be looking at all the information out there (which I just mentioned isn't that great) and that you are so sick of answering this issue. You wrote a great overview of the issue (the best I have seen) but no need to be snarky about it.

Anywho - regardless of my impression of your tone. This is a great resource and I thank you for putting it out there.

Matthew Roche said...

@Tara – I’d like to apologize if the tone of my writing offended you. It was certainly not my intent to imply that readers should have solve their problems elsewhere, or should have “gotten it” without needing additional information. The problem described here is complex, and involves factors that developers don’t often have to deal with, and obviously my previous blog post didn’t cover all of the necessary details. If you’ve read other articles on my blog[1], “snarky” summarizes my writing style quite nicely. It’s not my intent to come across as a jerk[2] but at this point in my life it’s unlikely to change. As I mentioned in a comment from June 2009[3], my writing style closely follows my presentation stile, and people either love it or hate it. Hopefully you’ll still get value from the blog even if you fall into the latter camp…

[1] Not that I’m suggesting that you should need to, of course – I think this article covers it effectively. ;-)
[2] This particular post averages around 1,000 views per month, and the comments (both on the content and the writing style) have been pretty positive all in all, so I think we may just be on different wavelengths here.
[3] Just making a reference here, not suggesting that you should have read it already.

Brian said...

Well after a few weeks of trying to figure out whie my SSIS package would not run schedules this little tidbit of info fixed my problems in 5 minutes.

Thank.

Connie said...

I've read lots of blogs posts/tech docs/forums trying to solve the problem addressed by this post, and yours is the best for helping me understand why there's a problem in the first place, which means that the solution (which *is* explained well in a few other places) actually makes sense.

Thank you, thank you, thank you. :-)

jbennett said...

I found this solution and applied it about a month ago but have discovered a new issue not covered in this thread. My package executes under schedule ONLY when the account used in the proxy has an open windows session. We are looking at windows security settings at this point. If anyone has experienced this issue and resolved it, I would be glad to hear about it. The network architecture is fairly simple, SBS, my job runs a SSIS package that simply ftp's a file from a shared folder on a sql server. It worked prior to a network upgrade that included a new router and windows server updates. Other than that, we are stumped. The error message is the same one I used to get before I set up the proxy.