February 5, 2014 at 7:50 am
We are using SQL Server 2012 EE SP1 and Visual Studio 2010 Shell. We are just getting into creating SSIS Packages. We have 4 SSIS Package Developers. We have the following questions regarding the developers creating packages in Visual Studio:
1) Should developers use their own windows login? Or a Generic Domain User Account (which means they would have to log onto windows with this Domain User Account each time before they use VS)?
2) Should developers use Project Deployment Model (instead of legacy Package Deployment Model)?
3) What should developers set the Package Level Encryption (Protection Level)? Is the best option is to set the ProtectionLevel property of your package to DontSaveSensitive? In one test, I created a package with the Protection Level set to the default (EncryptSensitiveWithUserKey) and had someone else open it and the following warnings were displayed:
Warning1Warning loading Interfaces.dtproj: Warning: Failed to decrypt an encrypted XML node. Verify that the project was created by the same user. Project load will attempt to continue without the encrypted information. Interfaces.dtproj00
Warning2Warning loading Interfaces.dtproj: Warning: Failed to decrypt sensitive data in project with a user key. You may not be the user who encrypted this project, or you are not using the same machine that was used to save the project. If the sensitive data is a parameter value, the value may be required to run the package on the Integration Services server. Interfaces.dtproj00
February 5, 2014 at 8:11 am
Kevin,
1) Should developers use their own windows login? Or a Generic Domain User Account (which means they would have to log onto windows with this Domain User Account each time before they use VS)?
I suspect you may be asking this question in relation to your later question about package protection, am I right? The thinking being that if all the developers use the same account, they will be able to open and run packages that contain sensitive information.
I would allow them to use their own individual accounts and handle sensitive information in a different way, which I'll come to later.
2) Should developers use Project Deployment Model (instead of legacy Package Deployment Model)?
If you want to be able to take advantage of the new features of SSIS 2012 - Shared Connection Managers, Environments, Environment Variables and Parameters - then you should opt for Project Deployment. It is possible to switch between deployment modes, but there are certain restrictions when moving from Project to Package deployment mode.
3) What should developers set the Package Level Encryption (Protection Level)? Is the best option is to set the ProtectionLevel property of your package to DontSaveSensitive?
I recommend setting this property to DontSaveSensitive and then handle sensitive information such as connection strings for Shared Connection Managers using Environment Variables and Parameters (Project Deployment Mode) or Package Configurations (Package Deployment Mode).
Regards
Lempster
February 5, 2014 at 8:17 am
+1 to Lempster's answers, I agree with them all. Though I would more strongly recommend using the project model in 2012 - been using it for about a year and no reason to consider going back.
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.
February 5, 2014 at 10:44 am
Phil Parkin (2/5/2014)
+1 to Lempster's answers, I agree with them all. Though I would more strongly recommend using the project model in 2012 - been using it for about a year and no reason to consider going back.
Stop making me cry that I'm still migrating the old 2k5 SSIS up to 2k8... in an ongoing project. :crazy:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 5, 2014 at 10:50 am
Evil Kraig F (2/5/2014)
Phil Parkin (2/5/2014)
+1 to Lempster's answers, I agree with them all. Though I would more strongly recommend using the project model in 2012 - been using it for about a year and no reason to consider going back.Stop making me cry that I'm still migrating the old 2k5 SSIS up to 2k8... in an ongoing project. :crazy:
Haha, sorry sir 😀
Could be worse - someone, somewhere will be migrating to Vista from XP ...
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.
February 5, 2014 at 9:48 pm
1) Develop using own accounts. One service account would be a major headache.
2) I am liking project deployment model so far. The catalog and environments make deployments easier. And the reporting capability built in to the catalog is very handy, too.
3) Most people recommend DontSaveSensitive, but thus far I am not a fan of it for 2012 if you are using project deployment mode. It made sense in earlier versions because you were using configurations anyway, and EncryptSensitiveWithPassword would prompt you 10 times for a project with 10 packages. But with the project deployment mode, EncryptSensitiveWithPassword prompts once per project (not annoying at all). And parameters/environments are great when you are deploying. But while you are debugging a password in a parameter is just like one in a connection string. It goes into a file, or if you have DontSaveSensitive, nothing goes in, or it goes in in plain text.
So for our connections that can't use Windows Authentication I like using EncryptWithPassword.
February 6, 2014 at 10:04 am
Thanks everyone for the very helpful responses.
We got rid of the Warning messages by selecting DontSaveSensitive Protection Level at the Project Level and Package Level. Now, when a developer opens a package (on their machine) created by a dba on another machine, the Warning messages are gone. However, the developer received an error in the Database Connection Manager. We believe this error is because in the Connection Manager we are using SQL Server Authentication and because of the DontSaveSensitive Protection Level setting, the password is not carried with the package when the developer opens it.
Possible Solution:
I am getting ready to try the following as soon as I can get a generic windows login ([TESTNETWORK\SSIS_PKG_Login]) created:
A dba creates a package with their own windows login (john_doe). In the Database Connection Manager use Windows Authentication with the generic windows login ([TESTNETWORK\SSIS_PKG_Login]) with the appropriate permissions to the database and schema. Get a developer to open the package on their own machine and execute it. I think this will eliminate the need to retype the password in the Database Connection Manager when DontSaveSensitive Protection Level is set. I will attempt this soon.
Other Solution suggested above by Lempster:
"I recommend setting this property to DontSaveSensitive and then handle sensitive information such as connection strings for Shared Connection Managers using Environment Variables and Parameters (Project Deployment Mode) or Package Configurations (Package Deployment Mode)."
I guess this is another option that will work also. I will have to read up on Shared Connection Managers using Environment Variables and Parameters.
What logins/passwords does the DontSaveSensitive Property affects? The Windows O/S Login (package creator) or Database Connection Manager logins/password (database logins/passwords). I have an idea but still researching.
Thanks, Kevin
February 6, 2014 at 10:48 am
I would not mess around with logins at all.
The easiest for you would be to agree with your team on a package password (it is seperate from any connections/users).
Change the protection level of the project to EncryptSensitiveWithPassword and use that password. Now the connection information will stay saved, but when you open the project you will need to enter the password. But any user can open it if they know the password.
Environments come into play when you deploy projects to a catalog, and thats a more involved conversation.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply