An issue was recently brought to our attention whereby a package was for all intents and purposes in full working order, could be executed by the developer from Visual Studio or Command Line, yet it could not be executed by the SQL Agent.
Unfortunately due to 3rd party components, the error received was a little cryptic but after a bit of troubleshooting we found the issue was due to the Package Protection Level. As we experienced this issue in a real world production environment, it strikes me as a topic that may benefit from a good blogging!
What are Package Protection Levels?
Simply put, package protection levels control who can open and execute SSIS packages. For example, you may want to password protect a package so it cannot be executed without providing a password, or you might want to ensure only certain users can execute the package.
The individual protection levels are:
| DoNotSaveSensitive | Does what it says on the tin, sensitive data is not saved with the package, such as passwords in connection strings. |
| EncryptAllWithPassword | The whole package is encrypted. To open or run the package the user must supply a password. |
| EncryptAllWithUserKey | The whole Package is encrypted using a key that is based on the current user profile. Only the user who created or exported the package can open or run the package. |
| EncryptSensitiveWithPassword | Only sensitive parts of the package are encrypted. Anyone can open the package, but without the password all sensitive data is blank. Password is needed to run the package. |
| EncryptSensitiveWithUserkey | Only sensitive parts of the package are encrypted with a key based on current user profile. Any user can open the package but if the user is not the user who encrypted the package, all sensitive data is blanked, and the package will not run |
| ServerStorage | Security relies on database roles. Only valid if package is deployed to MSDB |
Where is Package Protection Set?
Package protection is set in one of 3 places:
1. At the package level in Package Properties
In the control flow screen, right click on the control flow backgorund and click properties. Package properties appear in the bottom right of Visual Studio.
2. At the project level (2012)
Right click on the project in solution explorer and click properties.
Note, if using multiple packages in a project they must all use the same protection level as the project, otherwise the project cannot be deployed. Note this is not available pre 2012.
3. At the Command Line
dtutil.exe /file “C:\testPackage.dtsx” /encrypt file; “C:\testPackage.dtsx”; 2; YourPassword
Using the Various Protection Levels
So, how can a package be executed under each of the Protection Levels?
Do Not Save Sensitive
As sensitive data is not saved with the package a user has to provide it. This is easily done using either parameters or a configuration file.
EncryptAllWithPassword / EncryptSensitiveWithPassword
A user must provide a password along with the command to execute the package:
DTEXEC.exe /f “C:\testPackage.dtsx” /De YourPassword
EncryptAllWithUserKey / EncryptSensitiveWithUserKey
This mode is quite restrictive and unless you are the person who deployed/created the package the only way to run it is by creating a Proxy Account within SQL Server that’s linked to the credentials of the developer who created/deployed the package. An Agent job step can then be RunAs the proxy account and credentials. Users can then run the package via the agent, under the credentials of the original developer.
Unfortunately there is no way to set a specific account to encrypt the package with other than logging on as the account in question and deploying the package. If anyone wants to correct me on this id be delighted to admit error, as this particular lack of functionality really grinds my gears!
Back to the Original Problem!
I started this blog detailing an issue we had where a package ran fine via commandline or Visual Studio but wouldnt run under the Agent. Seems pretty clear what the issue is now, right?
The protection level was set to EncryptSensitiveWithUserKey.
To quickly get around the issue we encryptedWithPassword, supplied the password in the execute command and the package was executed by the Agent. Long term utilization of a config file or some other method may be more beneficial.
As always, any questions on the content above, observations or corrections just give me a shout in the comments!
Thanks for reading!