How to schedule to run Integration Packages using SQL Agent/Job?

Andy Leonard wrote:

Hi Garynkill23, There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Andy,

Why do you think that makes most sense To me, it makes more sense to set ProtectionLevel=DontSaveSensitive and then store all your connection strings in configurations. Personally I think this should be considered best practice - its a matter of opinion of course.

-Jamie

Hi Jamie,

I think the method you advocate (DontSaveSensitive) is great, and perhaps best.

My experience with this question drives my copy-and-paste answer about using a password. Here's what I've seen: Someone starts using SSIS and works through some issues as they scale the learning curve (reading good books and a great blog over at Conchango for help as they go).

Then they move it to a test or integration server, experiment with the job step type until they get a job to run when they right-click the job and click Start Job at Step... So they schedule it to run and wait. The SQL Agent scheduler fires the job and it fails with a "cannot acquire connection" (or similar) error. We know why but it seems completely baffling to them. (It certainly did to me when it first happened!)

What's worse is there's little or nothing in the log (the package cannot make that connection either).

While Encrypt___WithPassword method works, it also requires folks to keep track of a(nother) password.

I start here mainly because it's the quickest way I can think of to get that package up and running. Everyone understands passwords. I don't claim to be right or even advocating a best practice (at least not yet) - I'm just trying to help SSIS developers get that package running.

I welcome any feedback - from anyone. I'm still learning too! :)

Thanks,

Andy

Jamie Thomson wrote:

I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations.

I cannot argue with you. I advocate 2-pass (minimum) configurations to consulting clients. But I'm also right there to either implement it myself or show them how to implement it.

Jamie Thomson wrote:

Good discussion.

I couldn't agree more!

Thanks,

Andy

Hi garynkill23,

This is complicated. It touches a lot of moving parts in your SSIS package, SQL Servers, and enterprise domain. There's just no simple and easy explanation. But it is this way for good reason and that reason is to provide security.

I wrote a blog entry that talks about connections between SSIS and SQL Server - if you use a SQL Login to connect to SQL Server. This should also apply to storing credentials for any provider that requires a username and password.

Windows Authentication is simpler and safer provided your SQL Servers use one or more domain accounts for the SQL Agent service. (It also offloads SQL Server connectivity account maintenance to the Help Desk - at no extra charge.) If you use a domain account for the SQL Agent service, your SSIS connection managers can all be configured to use Windows Authentication to connect, and you simply grant the SQL Agent service domain account the access it needs in SQL Server. Does this make sense

For example: I have a domain account named MyDomain\Andy. I log in as MyDomain\Andy and write an SSIS package that connects to a Dev SQL Server. I use the default ProtectionLevel: EncryptSensitiveWithUserKey. MyDomain\Andy has sufficient privileges on the Dev SQL Server. When I'm done, I can manually execute the package and it succeeds in connecting to the Dev SQL Server.

Next, I deploy the SSIS package to a Prod SQL Server. I create a SQL Agent job with an SSIS step that calls the SSIS package. I schedule the job to run at 2:00 AM. Again, MyDomain\Andy has sufficient privileges in the Prod SQL Server. When I right-click the SQL Agent job I just created, I can select "Start Job at Step..." and the job executes, calls the SSIS package, and both succeed.

What just happened From the security context point-of-view, MyDomain\Andy just executed this job. This is important. The SQL Agent scheduler did not execute the job. In this scenario, that won't happen until 2:00 AM. Although this was a good test, it was incomplete.

To continue the example, let's assume the SQL Agent service on the Prod SQL Server runs under a domain account named MyDomain\SQLAgentService. When the scheduler fires the job at 2:00 AM, MyDomain\SQLAgentService will try to log into the Prod SQL Server and perform the operations specified in your SSIS package. If it lacks permissions to connect, the SSIS package will fail, causing the SQL Agent job to fail. You will get an error message similar to "failed to acquire connection". The error varies because the permissions granted MyDomain\SQLAgentService vary along with the configuration of the SSIS package:

• If Logging is enabled in the SSIS package, it will probably attempt to connect to the logging provider before attempting to connect to a SQL Server - unless you're using a SQL Server logging provider. If it cannot connect to the Logging provider, it cannot log the fact that it encountered an error connecting to the Logging provider.
• In addition, MyDomain\SQLAgentService may have sufficient permissions to write to Text Logging Provider but may also lack sufficient permissions to connect to a SQL Server specified in your SSIS package. In this case, you at least get a log entry telling you the SSIS package could not acquire a connection.
• Similarly, if Package Configurations are enabled, the error may be raised when a Package Configuration File connection is attempted. And the above applies - if the MyDomain\SQLAgentService has sufficient permissions to connect to the Logging provider, you get a "failed to acquire connection" log entry. If not...

All connections from the SSIS package are affected by the security context - not the just the connections residing in the Connection Managers section.

You will not be able to reproduce the error as long as you are logged in as you and MyDomain\You has enough privileges to do any of this on any SQL Server (or file system) in your enterprise. The only way to reproduce this error is to log in as MyDomain\SQLAgentService. What's more, MyDomain\SQLAgentService may not be allowed to log on interactively - and for good reason. So you may have to change this to reproduce the error - and then change it back to comply with domain service account security policy.

(Jamie, Phil, MS, others - please correct any mis-statements contained herein. This is my understanding of the SSIS Package ProtectionLevel in practice. I welcome your feedback.)

Hope this helps,

Andy