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

Developers and SSIS Packages settings in Visual Studio Expand / Collapse
Author
Message
Posted Wednesday, February 05, 2014 7:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:36 PM
Points: 304, Visits: 308
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:

Warning 1 Warning 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.dtproj 0 0

Warning 2 Warning 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.dtproj 0 0





Post #1538185
Posted Wednesday, February 05, 2014 8:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,890, Visits: 1,190
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
Post #1538204
Posted Wednesday, February 05, 2014 8:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 4,832, Visits: 11,197
+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.


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 #1538209
Posted Wednesday, February 05, 2014 10:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 5,986, Visits: 6,932
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1538289
Posted Wednesday, February 05, 2014 10:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 PM
Points: 4,832, Visits: 11,197
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.


Haha, sorry sir

Could be worse - someone, somewhere will be migrating to Vista from XP ...



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 #1538294
Posted Wednesday, February 05, 2014 9:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 547, Visits: 1,880
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.
Post #1538460
Posted Thursday, February 06, 2014 10:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:36 PM
Points: 304, Visits: 308
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





Post #1538748
Posted Thursday, February 06, 2014 10:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 547, Visits: 1,880
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.
Post #1538764
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse