SSIS Secure it after scheduling in SQL Agent

  • Hi,

    Lately I'm working on SSIS package that may be used by our customer on their site to connect to our stand-alone stations around their site and gather data. Once the package will be scheduled on their server I don't want anyone to be able to access it using the decryption password and change it. When I’m scheduling the package I have to enter the decryption password, which is fine, but after that I can right click on the SQL job -> Script Jobs As -> Create To -> New Query Editor. And it gives me nice script to recreate the job but with the Decrypting password revealed:

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MyJobName',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'SSIS',

    @command=N'/FILE "C:\XXXXX\MyPackage.dtsx" /DECRYPT MyRevealedPassword /CONFIGFILE "C:\XXXXX\MyConfigFile.xml" /CHECKPOINTING OFF /REPORTING E',

    @database_name=N'master',

    @flags=0

    is there any other way to secure it?

  • No, not really.

    As you noticed, the "security" can easily be circumvented.

    The only "option" to secure the package so that you only can open it is to use EncryptAllWithUserKey, but then you can't schedule the package on the server.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Shame...

    I might visit customers site, set it up and would like it to stay intact, the only way customer would affect the package is by using provided configuration file.

    I was thinking of using encrypted stored procedure to run the package, scheduled in the agent but i would have to use xp_cmdshell and DTExec, which is not ideal solution IMO.

Viewing 3 posts - 1 through 2 (of 2 total)

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