msbuild - Programmatically building SSIS packages with EncryptSensitiveWithPassword and package-level connection managers -

msbuild - Programmatically building SSIS packages with EncryptSensitiveWithPassword and package-level connection managers -

i'm trying automate deployment of ssis project saved password project-level connection manager. packages connect third-party database provides sql login, integrated security isn't alternative in case. using this reference since seems same use-case. i've got msbuild task compiled , working, , msbuild project file. can build , deploy project through command line without problems.

problem

when deploy ispac file generated through visual studio, can run packages no problems. however, when deploy ispac generated msbuild task , seek run, next validation errors:

error: ssis error code dts_e_cannotacquireconnectionfromconnectionmanager. acquireconnection method phone call connection manager "<>" failed error code 0xc0202009. there may error messages posted before more info on why acquireconnection method phone call failed.

error: ssis error code dts_e_oledberror. ole db error has occurred. error code: 0x80040e4d. ole db record available. source: "microsoft sql server native client 11.0" hresult: 0x80040e4d description: "login failed user '<>'."

if alter 1 of packages utilize package-level connection manager same server, password gets carried on , can run particular package.

the connection managers stored in .conmgr files, dts:password node marked sensitive , contains encrypted password. can tell, problem arises in build task code. code build task following:

deserializes .dtproj file obtain list of connection manager file paths in project

creates new microsoft.sqlserver.dts.runtime.project (which, can tell, ties generated .ispac rather .dtproj "project")

for each connection manager file, calls project.connectionmanageritems.add(<connection manager name>, <.conmgr file name>)

loads returned connectionmanageritem via cm.load(null, <stream of conmgr file>)

i'm assuming somewhere in lastly 2 steps, password doesn't deserialized, , new project gets connection manager added without password. i've been scouring integration services developer's guide, seems more focused on programmatically creating connection managers scratch, rather loading existing ones have info needs decrypted.

update per @billinkc's suggestion unzipped generated ispac files compare how connection managers saved. 1 generated msbuild same 1 generated visual studio except missing dts:password element. backs theory code adding connection manager microsoft.sqlserver.dts.runtime.project either not deserializing in or not serializing out password. i'm way out of depth in regards programmability ssis though, don't know go here. related documentation** doesn't offer much insight.

** project, connectionmanageritem, working connection managers programmatically, adding connection managers programmatically

i couldn't allow go , easy (smart) way, armed knowledge of encryption (little none) , 1 line msdn**, set out seek , decrypt encrypted element. after bit of trial , error, i've got working:

class="lang-cs prettyprint-override">private string decryptconnectionmanagerpassword(string connectionmanagerpath) { // load xml , encrypted dts:password node xmldocument cmdocument = new xmldocument(); cmdocument.load(connectionmanagerpath); xmlelement passwordelement = cmdocument.getelementsbytagname("dts:password")[0] xmlelement; // create byte arrays info we'll need byte[] salt = convert.frombase64string(passwordelement.getattribute("p4:salt")); byte[] iv = convert.frombase64string(passwordelement.getattribute("p4:iv")); byte[] ciphertext = convert.frombase64string(passwordelement.innertext); byte[] password = system.text.encoding.ascii.getbytes(projectpassword); // create cipher key passwordderivebytes pdb = new passwordderivebytes(password, salt); byte[] key = pdb.cryptderivekey("tripledes", "sha1", 192, iv); // decrypt cipher text var csp = new tripledescryptoserviceprovider(); csp.mode = ciphermode.cbc; csp.iv = iv; csp.key = key; var plaintextbytes = new byte[512]; var decryptor = csp.createdecryptor(); decryptor.transformblock(ciphertext, 0, ciphertext.length, plaintextbytes, 0); // convert string , extract password var plaintext = new string(system.text.encoding.ascii.getchars(plaintextbytes)); // decrypted text doesn't come out valid xml // utilize regex extract password. dangerous. var regex = new regex(">(.*)<"); var matches = regex.match(plaintext); homecoming matches.captures[1].value; }

and there, it's matter of setting password property on connection manager, , resulting ispac contains connection manager encrypted password element.

** also, protection levels utilize password, integration services uses triple des cipher algorithm key length of 192 bits, available in .net framework class library (fcl).

ssis msbuild sql-server-2012