Protection Level

Question

Protection Level

tim.ffitch 25252

SSCrazy

Points: 2055
More actions
January 30, 2017 at 4:50 am

#334557

So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

tim.ffitch 25252 - Monday, January 30, 2017 4:50 AM
So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.

Change the project to Don't Save Sensitive from the start. From then on, all packages that you add to the project will be Don't Save Sensitive too.

But if you are working in an existing project, the project and package protection levels have to match. And there is no fast way of changing multiple packages' protection levels.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 2

Phil Parkin - Monday, January 30, 2017 5:14 AM
tim.ffitch 25252 - Monday, January 30, 2017 4:50 AM
So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.
Change the project to Don't Save Sensitive from the start. From then on, all packages that you add to the project will be Don't Save Sensitive too.
But if you are working in an existing project, the project and package protection levels have to match. And there is no fast way of changing multiple packages' protection levels.

The problem with Don't Save Sensitive is that it won't run in the IDE, the database connections fail. In SQL 2008/2012 this used to work in the IDE. I am now using Data Tools 2015 and SQL 2016 and it fails every time, you now have to use Encrypt with User Key or Password.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

tim.ffitch 25252 - Monday, January 30, 2017 6:51 AM
Phil Parkin - Monday, January 30, 2017 5:14 AM
tim.ffitch 25252 - Monday, January 30, 2017 4:50 AM
So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.
Change the project to Don't Save Sensitive from the start. From then on, all packages that you add to the project will be Don't Save Sensitive too.
But if you are working in an existing project, the project and package protection levels have to match. And there is no fast way of changing multiple packages' protection levels.
The problem with Don't Save Sensitive is that it won't run in the IDE, the database connections fail. In SQL 2008/2012 this used to work in the IDE. I am now using Data Tools 2015 and SQL 2016 and it fails every time, you now have to use Encrypt with User Key or Password.

Not if you use Windows logins.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 4

Phil Parkin - Monday, January 30, 2017 7:05 AM
tim.ffitch 25252 - Monday, January 30, 2017 6:51 AM
Phil Parkin - Monday, January 30, 2017 5:14 AM
tim.ffitch 25252 - Monday, January 30, 2017 4:50 AM
So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.
Change the project to Don't Save Sensitive from the start. From then on, all packages that you add to the project will be Don't Save Sensitive too.
But if you are working in an existing project, the project and package protection levels have to match. And there is no fast way of changing multiple packages' protection levels.
The problem with Don't Save Sensitive is that it won't run in the IDE, the database connections fail. In SQL 2008/2012 this used to work in the IDE. I am now using Data Tools 2015 and SQL 2016 and it fails every time, you now have to use Encrypt with User Key or Password.
Not if you use Windows logins.

I know what you mean Phil. The trouble is another organisation has control of the security on the production server and I would need to get through several corporate layers to get approval and then try and arrange for Systems and DBA time.

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 5

tim.ffitch 25252 - Monday, January 30, 2017 9:30 AM
Phil Parkin - Monday, January 30, 2017 7:05 AM
tim.ffitch 25252 - Monday, January 30, 2017 6:51 AM
Phil Parkin - Monday, January 30, 2017 5:14 AM
tim.ffitch 25252 - Monday, January 30, 2017 4:50 AM
So I don't have to set up Proxy Accounts on our production servers I swap the protection level from Encrypt Sensitive With User Key when working in the IDE to Don't Save Sensitive when I am ready to build the ipsac file for deployment to the production server. This is a pain as you have to open each package in the project to change the protection level. For this reason I have been trying to use the /Encrypt switch in the dtutil.exe command as follows:
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0
The problem is when I re-open the solution in the IDE it complains that the packages have a different protection level to the project, even if I had already changed the project beforehand. If I open the dtsx file though the setting is correct. I have to change it to something else save it and then swap the setting back gain and save it again, so I end up doing even more work.
Something in the solution does not see the change made by dtutil.exe. Has anyone else had the problem, if so how did you resolve it?
Thanks
Tim.
Change the project to Don't Save Sensitive from the start. From then on, all packages that you add to the project will be Don't Save Sensitive too.
But if you are working in an existing project, the project and package protection levels have to match. And there is no fast way of changing multiple packages' protection levels.
The problem with Don't Save Sensitive is that it won't run in the IDE, the database connections fail. In SQL 2008/2012 this used to work in the IDE. I am now using Data Tools 2015 and SQL 2016 and it fails every time, you now have to use Encrypt with User Key or Password.
Not if you use Windows logins.
I know what you mean Phil. The trouble is another organisation has control of the security on the production server and I would need to get through several corporate layers to get approval and then try and arrange for Systems and DBA time.

We also have other sensitive information that changing to an AD account won't help with.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

tim.ffitch 25252 - Monday, January 30, 2017 9:30 AM
Phil Parkin - Monday, January 30, 2017 7:05 AM
Not if you use Windows logins.
I know what you mean Phil. The trouble is another organisation has control of the security on the production server and I would need to get through several corporate layers to get approval and then try and arrange for Systems and DBA time.

OK, but why not use Windows logins for development work locally and then use SSISDB environments with appropriate 'Sensitive' parameters to make things work in Production (assuming you are using the project deployment model)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 7

Phil Parkin - Monday, January 30, 2017 9:37 AM
tim.ffitch 25252 - Monday, January 30, 2017 9:30 AM
Phil Parkin - Monday, January 30, 2017 7:05 AM
Not if you use Windows logins.
I know what you mean Phil. The trouble is another organisation has control of the security on the production server and I would need to get through several corporate layers to get approval and then try and arrange for Systems and DBA time.
OK, but why not use Windows logins for development work locally and then use SSISDB environments with appropriate 'Sensitive' parameters to make things work in Production (assuming you are using the project deployment model)?

I did work that way initially on this particular project, but then we suffered a login failure on production which was an error on our part. At the time we did not have OAT set up so I decided to change to SQL logins on development as well as production so that we had confidence the logins would work. I suppose now I have an OAT environment to work with I could use AD logins in the dev environment and SQL logins in OAT and production.