none
Deploying a SSIS package with the Protection Level to “EncryptSensitiveWithPassword” RRS feed

  • Question

  • Hi,
    I'm trying to deploy a SSIS package that has an Oracle connection to retreive data, modify it, and then insert it into the target SQL Server DB.  In Visual Studio, I run it in debug mode and it works find.  I then set the package with the Protection Level to “EncryptSensitiveWithPassword”, and enter a password (it tests good too). 

    However, when I deploy the package (using the *.SSISDeploymentManifest file) to the SSIS MSDB databases and then schedule it in SQL Agent on the target DB instance, I get errors when running it in SQL Agent (entering the password each time for the MSDB & SQL Agent).  The error looks like:

       ...SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER...

    The error seems to be related to the Oracle connection I'm trying to established.  However, the password for the package was enter correctly, as well as the OLE Source password.  I'm following MS Article ID#918760. 

    Is there something I'm missing??

    By-the-way; manually running the package from SSIS\MSDB, it doesn't throw an error.

    Monday, December 29, 2008 9:32 PM

Answers

  • I finally found a solution!....

    When run the Package.SSISDeploymentManifest in the Package Installation Wizard, when you get to the “Specify Target SQL Server” display, check the “Rely on server storage for encryption” checkbox.  Apparently it does encryption on database roles and gets around the other protectionlevel settings. 

    Now all I have to do is create a new job and it works!

    Comment: Boy this has been an extremely painful process!  MSDN BOL has a lot of information, but no continuity and it many cases, a whole lot of information without giving you the information you need to get the job done!  I'm still not certain what the ServerStorage  protection level gives me even though I read the Setting the Protection Level of Packages BOL (http://msdn.microsoft.com/en-us/library/ms141747(SQL.90).aspx) a dozen times. 
    Tuesday, December 30, 2008 9:14 PM

All replies

  • lechkung said:

    Hi,
    I'm trying to deploy a SSIS package that has an Oracle connection to retreive data, modify it, and then insert it into the target SQL Server DB.  In Visual Studio, I run it in debug mode and it works find.  I then set the package with the Protection Level to “EncryptSensitiveWithPassword”, and enter a password (it tests good too). 

    However, when I deploy the package (using the *.SSISDeploymentManifest file) to the SSIS MSDB databases and then schedule it in SQL Agent on the target DB instance, I get errors when running it in SQL Agent (entering the password each time for the MSDB & SQL Agent).  The error looks like:

       ...SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER...

    The error seems to be related to the Oracle connection I'm trying to established.  However, the password for the package was enter correctly, as well as the OLE Source password.  I'm following MS Article ID#918760. 

    Is there something I'm missing??

    By-the-way; manually running the package from SSIS\MSDB, it doesn't throw an error.


    You have to setup your SSIS SQL Job Agent step thru "Execute Process" step. Then you have to specify the package decryption password on the DTEXEC command line. Another problem you might be having is that if you are using the default SQL Job Agent account, this account must have permissions for accessing your Oracle database.

    For a very good description of the options available for running SSIS packages check here.

    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    Monday, December 29, 2008 9:58 PM
  •  OK, so I went out and edit the job on the "comand line" tab (I had to enter the password to open the tab) and modified the command as follows:
    /SQL "\OraclePrgnDb-To-Sql2005PrgnDb" /SERVER "DCA-DEV291\dvsql01" /DECRYPT  "password"   /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    I saved it, and ran the job with the same results.

    By-the-way: after reopening the "comand line" tab, I saw that the command line reverted to:
    /SQL "\OraclePrgnDb-To-Sql2005PrgnDb" /SERVER "DCA-DEV291\dvsql01" /DECRYPT  /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    I suspect that the job step editor removing the password from clear text.

    I tried creating a manually step with the type: OS (CmdExec) using the following command:
    dtexec /SQL "\OraclePrgnDb-To-Sql2005PrgnDb" /SERVER "DCA-DEV291\dvsql01" /DECRYPT  "password"   /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

    Again, when run, the job produced the same results.

    Tuesday, December 30, 2008 4:23 PM
  • well why dont you create a batch file for your command and then schedule it with Windows Task manager.

    Its kind of work around but it can solve your problem.


    Gaurav
    Tuesday, December 30, 2008 6:52 PM
  • I finally found a solution!....

    When run the Package.SSISDeploymentManifest in the Package Installation Wizard, when you get to the “Specify Target SQL Server” display, check the “Rely on server storage for encryption” checkbox.  Apparently it does encryption on database roles and gets around the other protectionlevel settings. 

    Now all I have to do is create a new job and it works!

    Comment: Boy this has been an extremely painful process!  MSDN BOL has a lot of information, but no continuity and it many cases, a whole lot of information without giving you the information you need to get the job done!  I'm still not certain what the ServerStorage  protection level gives me even though I read the Setting the Protection Level of Packages BOL (http://msdn.microsoft.com/en-us/library/ms141747(SQL.90).aspx) a dozen times. 
    Tuesday, December 30, 2008 9:14 PM