How to deploy SSIS to server?
-
I'm brand new to these forums, so please let me know if I'm posting this in the wrong group.
We are building a new SSIS environment. We plan to use TFS to deploy solutions to servers we called SSISDEV and SSISPROD. And then use CA-Automic(aka UC4, which is a batch scheduler product) to execute the solutions.
We are not using dtexec. We are running a series of database SQL commands that update parameters, execute the solution in synchronous mode, and query and report the results via UC4.
We have been using this model for quite a while in test mode without TFS. Developers have been deploying manually to SSISDEV from Visual Studio and with database admin rights, and UC4 can execute those deployed solutions.
We are now trying to use TFS to deploy with a service account, so that developers will no longer be required to have database admin rights. But we have run into unexpected issues! Our TFS expert set up the ability to deploy the solution to the proper server and the proper folder, but when UC4 executes the solution we now get this error;
PKG_<packagename>:Error: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is availab (trunkated message)
We believe the root cause of this problem is with the SSIS package protection level settings. We have been experimenting with different configurations to resolve this issue, but are currently floundering around not really knowing or understanding all of the ramifications of this problem. For instance I only recently learned that package protection level settings can render code to be unusable by other developers, if you don't know what you are doing (and I don't think that we do.)
So some of our burning questions include; what should we use for our package protection level when developing our solutions? What should we use as our package protection level when deploying? Can TFS automatically "correct" it for us? Should/could UC4 manipulate package protection level and/or password at runtime?
In our test use-case, the developer, the TFS expert, and the UC4 expert are three different people. (I'm the UC4 expert but I also dabbled in SSIS a few years ago.)
Is our road map even doable?
Surely we aren't the first shop to attempt something like this.....?
Pete
-
First thing I would try is to use "Don't save sensitive", and then configure all your SSIS packages to get their connection strings exclusively from a package configuration database. That's a fair amount of work, but it helps keep the passwords OUTSIDE of the packages, and protected in an SSIS_CONFIG database instead. That might eliminate the need for the packages to get encrypted. You'll also want to work with your DBA to determine the best security model for the SSIS_CONFIG database, given what could end up in there.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Friday, September 14, 2018 10:38 AM
Our connection objects have already exposed the sensitive database password as a parameter, which our batch runtime system modifies to reference an encrypted SSIS environment variable which contains the actual password (we did this to avoid any exposure of passwords). So I'm thinking theoretically we should just be able to deploy with "Don't save sensitive" and we should be good(?) I'll have my developer try a test of this hypothesis and let you know what happens.
- petwir - Friday, September 14, 2018 10:55 AM
My developer set her project to "Don't save sensitive" and had TFS build it and deploy it, which seems to have worked fine.
At runtime however it threw the following error;
Any suggestions?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply