Post reply

Package encryption and Source Code Control

  • February 27, 2013 at 4:12 pm

    #271013

    Hi guys,

    Background:

    We are using Mercurial Hg Source code control for our SSIS solutions. Currently we have a package that connects to a SQL2005 server using SQL authentication whilst in development (the database is on a production server, hence the elevated security) When we deploy the package to test and live it will run from an SQL server agent job and use a service account and Windows authentication. To this end we are configuring the entire connection string in the dtsConfig file.

    The challenge:

    The package needs to be booked out of Hg and the build run without making any changes to the package. SQL server agent jobs require that the package encryption be set to DontSaveSensitive so the SQL user password is not saved with the package and so it won't run from BIDS whilst being developed but if we set it to EncryptWithUserToken it will run under the developers account, but not in SQL Agent.

    Does anyone have any ideas about how to resolve this impass? Unfortunately in this instance we do not have the option of a non production test source as it is a 3rd party application that we are connecting to.

  • March 14, 2013 at 1:59 am

    #1596861

    Have a look into EncryptSensitiveWithPassword the option. You could also look into using a Package Configuration (of some kind) to store the connection string with the password in it but the password would not necessarily be protected in that scenario.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 14, 2013 at 8:42 am

    #1597046

    opc.three (3/14/2013)

    Have a look into EncryptSensitiveWithPassword the option. You could also look into using a Package Configuration (of some kind) to store the connection string with the password in it but the password would not necessarily be protected in that scenario.

    I'll second the package configuration suggestion - that's how I would do it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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