SQL Centre of Excellence

We have been deploying office 2013 on some customer servers for POC’s. One new feature in 2013 is that when it starts up it looks for templates online. This is fine for a connected desktop, but does not work so well behind firewalls.

A typical Error message may look as below “Connecting to proxy Server officeimg.vo.msecnd.net”

image

Assuming that you to want to enable online content you can follow this KB article
http://support.microsoft.com/kb/891158/en-us

The dword to add with a zero value is below

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Internet\useOnlineContent

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.

image

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.

image

 

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!

Page List

Page List