Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Package encryption and Source Code Control Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 4:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.


Post #1424809
Posted Thursday, March 14, 2013 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,125, Visits: 12,723
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
Post #1430804
Posted Thursday, March 14, 2013 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 5,167, Visits: 12,017
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1431018
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse