Post reply

SSAS job run SSIS package - setup question

  • November 3, 2009 at 11:51 am

    #213372

    I have scoured the internet looking for examples of SSIS packages being run from SSAS and how to set that up in management studio.

    I am very familiar with the protection level http://support.microsoft.com/kb/918760 article. I have decided to try setting my SSIS package to EncryptWithPassword as the protection level. I've left the password blank in the package properties.

    This time, I built the package under our administrative account. I also deployed the package to our production sql server as the admin account.

    Inside the SSAS job, in the step that runs the package, I attempted to modify the command line code by adding a /DECRYPT parameter with no password defined, but the command line keeps reverting to /SQL "\[package name]" /SERVER [production server] /CHECKPOINTING OFF /REPORTING E

    Am I defining the SSIS package password in the correct location?

    The reason I was having problems running the SSIS package from SSAS in the first place was due to the user permissions. The package stores and grabs data from several servers, but I beleive running the package as the administrative account should handle that.

    Should I just build the package with the admin account using EncryptWithUserKey and use that account to run it in SSAS. I've read that the machine you create it one if different than the machine running it can stop execution with that protection level defined.

    If anyone has a nice step by step for setting up a complex SSIS package to run automatically in SSAS, I would appreciate the info.

    Keith Wiggans

  • November 3, 2009 at 12:29 pm

    #1074445

    I am unable to understand by this

    SSIS packages being run from SSAS

    From my knowledge, SSIS Package can run an SSAS Process but I have not come across what do you mean by the phrase that I quoted from your Post.

    What exactly are you trying to do? I am currently working 2005 SSIS Packages on Windows 2008 and have been exploring different settings and deployment options and has fairly good success in deploying the SSIS Packages.

    I would need some information to understand your main goal of the SSIS Packages, and help whatever I can. ( I am confused a little bit with the description you gave)

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 3, 2009 at 12:49 pm

    #1074461

    You're probably confused because I created a confusing post. 😀

    The purpose: Run an SSIS package on a nightly basis.

    The package: Handles processes like XML file creation on a remote server, running SQL scripts on multiple remote servers, sending smtp email and ftp processes.

    The problem: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

    Originally, I was the author of the SSIS package. I deployed it to SQL Server and it ran under my UserID without a hitch. This application is being deployed as part of a project for my company, so I couldn't leave my ID as the user running the project.

    I tried many different combinations of deployment with protection levels in SSIS and users in SSAS.

    I'm really just looking for a reliable set-up method when deploying SSIS packages to SQL server and running them from SSAS in SQL 2005.

    My current try will be building the package with EncryptWithPassword as the protection level in SSIS, deploying it as our domain administrator sql service account and running it in SSAS as the domain administrator sql service account.

    I'm concerned that the command line in the SSAS job step doesn't reference the password that I set up in the SSIS package. When I set the protection level in SSIS to EncryptWithPassword, I left the password blank, hoping that would be the easiest method to get around the deny permission problem.

    Keith Wiggans

  • November 3, 2009 at 1:23 pm

    #1074478

    I'm really just looking for a reliable set-up method when deploying SSIS packages to SQL server and running them from SSAS in SQL 2005. .

    Again Confusing...

    You would not be running SSIS Packages from SSAS but from SSIS only.

    When I set the protection level in SSIS to EncryptWithPassword, I left the password blank, hoping that would be the easiest method to get around the deny permission problem.

    When you set the protection level in SSIS to EncryptWithPassword, you need to give a password by clicking on the Ellipsis Button (...) (Three Dots) you should not be leaving that empty password.

    Atleast that's what I did currently and has been working perfectly and while deploying the package on to the SSIS Server you need to mention the same password.

    I would ask you to try this and then update here what has happened.

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 3, 2009 at 2:53 pm

    #1074549

    I think we need to get terminology fixed befwe go further:

    DTS = Data Transformation Services (SQL 2000)

    SSAS = SQL Server Analysis Services (SQL 2000+)

    SSIS = SQL Server Integration Services (SQL 2005+)

    SSRS = SQL Server Reporting Services (SQL 2000+)

    SSMS = SQL Server Management Studio (SQL 2005+)

    I see you mixing and misusing acronyms and it is confusing.

    You make the statement: "The problem: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed"

    Are you running DTS or SSIS?

    As I see it you have an SSIS package that contains sensitive information (otherwise the whole discussion about protection mode would be silly). That you want to run every night using SQL Agent.

    I have several questions/thoughts:

    1. What restricted information are you storing in the package? Username/passwords? If so is it possible to use trusted security?

    2. Once you changed the protection level you may have to re-enter the restricted information, if it was you who opened it and changed it probably not, but anybody would have to.

    3. What security context is the SSIS job being run in?

    4. No user/dba should have their security used for a package, nor should a job/package be dependent on a normal users security, system accounts yes, user accounts no..

    CEWII

  • November 3, 2009 at 3:21 pm

    #1074571

    I appreciate the primer on the terminology, it is an alphabet soup sometimes. As you can see from the screenshot, I am running an SSIS package inside SQL Server Agent. The error message Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed was produced from by this job running in SQL Server 2005. We do run compatibility mode 80 however.

    1. You are correct that the package contains sensitive information. Trusted security is a good suggestion, however there the way the application was built prevents that.

    2. The restricted info is static, so luckily I don't have to change it.

    3. The SSIS step is being run under Windows Authentication. I figured that was fine as the owner of the SSAS job is a domain admin service account.

    4. I'm starting to figure that out, that packages should be dependent on system accounts.

    Thanks for your help guys... Another question for you:

    The command line indside the job step keep reverting to it's original state even after I change it to include a /DECRYPT phrase. It's the second screenshot. Any ideas as to why my Editing of the command line does not stick?

    Keith Wiggans

  • November 3, 2009 at 3:31 pm

    #1074579

    The command line indside the job step keep reverting to it's original state even after I change it to include a /DECRYPT phrase. It's the second screenshot. Any ideas as to why my Editing of the command line does not stick?

    I am not 100% sure but wanted to point out, so that you can try, the 2nd Image you have attached, shows Restore the Original Options is selected can you try selecting Edit the Commandline Manually

    Post your reply if it solves, I am eager to know..

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 3, 2009 at 3:40 pm

    #1074586

    That's the thing. I select the Edit the Command Line radio button, click OK to confirm changes and next time I go back to edit the step, it's reset to the original settings.

    Really wierd and disheartening.

    Keith Wiggans

  • November 3, 2009 at 3:50 pm

    #1074592

    Just to clarify.. It is SSIS not SSAS..

    3. The SSIS step is being run under Windows Authentication. I figured that was fine as the owner of the SSAS job is a domain admin service account.

    I think if you change the owner of the job to sa or the user account that SQL runs under on that machine it will resolve that error, I'm not sure what your policies are where you work. ALL jobs run under the service account, it is an exception for anything else.

    Also, have you considered config files to contain your protected information, maybe it is just me, but I hate the protection level setup. I have also used config files to handle the fact that I don't want the package to change at all as I move it from Dev, to test, to prod, the only thing different between them is the config file. I have also seen it handled as parameters in the job, since the job already contains the password anyone who has access to the job has access to the data in the package..

    CEWII

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply