Hi Folks,
I deploying a couple of integration packages which needs to be run sequentially. Currently, I quite puzzled how come i cannot run them as a SQL Agent job. It always fail with "Executed as user: Servername\Administrator. The package execution failed. " But if I had put import these integration packages into the SQL Server 2005 Integration Services FileSystem, it does run but i need promptly as there is a dialog requesting whether to execute the packages. Is there a way to automate the running of these packages through SQL agent. ? Pls help need to get it up and running asap. Thanks first.
Yes there is. Most problems experienced when using SQL Agent to run packages are down to the package being run as a different user. Is that what you are doing?
-Jamie
|||Hi Garynkill23,
This could be due to the ProtectionLevel setting for the individual packages - that's my guess.
By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?
When the job you created executes, it runs under the SQL Agent Service logon credentials.
My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).
One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.
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.
Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.
Hope this helps,
Andy
|||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 EncryptWithPassword 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
|||That all sounds fair enough to me Andy. I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations. As I aluded to, its all subjective.
Good discussion.
-Jamie
|||
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.
Good discussion.
I couldn't agree more!
Thanks,
Andy
|||As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.
Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.
|||
As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.
Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.
As discussed above, if you have Protectionlevel=DontSaveSensitive then you will more than likely have to use configurations.
What do you mean by "put it under maintenance"?
-Jamie
|||
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.
No comments:
Post a Comment