Protection Level settings on MSDB

Last Post 19 Nov 2008 01:59 PM by sschmid4. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sschmid4
New Member
New Member

--
18 Nov 2008 11:42 AM
I am trying to re-trace a previous vendor's work. I want to check what protection level settings are currently employed on the packages that have been deployed to the SSIS instance. Is there a way to do this with system table or views without having to download the pakcages to a file location and opening the package in BIDS?
sschmid4
New Member
New Member

--
19 Nov 2008 01:59 PM
On that note, I guess my next question would be that if I did re-open the packages in BIDS, would the protectionlevel property automatically default to encryptsensitivewithuserkey when opened even if it was a different value when deployed?

I am really at a loss on determining how the packages were executing in the setup. There are no proxies setup on the instance, the are no configuration files in the package store, there are no variables in the Set Values tab of the SQL Agent Job. Yet somehow this package was previously running under the SQL Agent Service Account in the Run As: setting and the password for an FTP setting was encrypted but readable to the login account the SQL Agent was running as.

Is there somewhere other than the Proxies folder under the SQL Server Agent where a proxy account could be established? It is my understanding that after creating a SQL Job Step with the type SSIS Package and Run As: SQL Agent Service Account, you then need to complete the setup by creating a new proxy account and assigning a valid Login. If that Login is sysadmin then it should be able to execute any package that has been uploaded to the MSDB database regardless of who created/edited the package and who deployed the package even if that person(s) were not sysadmin themselves.

Is there a scenario where, if the person editing and uploading the package was not sysadmin and changed a sensitive field value and saved the package with default security settings of encryptsensitivewithuserkey and then deployed the package, that the SQL Agent Service Account could run the package but still not be able to de-crypt the key?


Acceptable Use Policy
---