This months T-SQL Tuesday is hosted by Ken Wilson (b|t|l) and the invitation is found following this link.
T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic. This time, the topic is Encryption.
The only requirement I have seen in my work as Business Intelligence Consultant, is that of the platform itself. No customer has ever requested any data encrypted, except for when data is to be exported to a movable device such as a USB stick or the like. And even then, it’s usually handled by some hard- or software solution in place on the stick.
Reporting Services is one of the tools that touches Encryption, and this becomes very relevant when you do migrations, which you will eventually have to do. In the current version of SQL Server, you have the following options to manage encryption keys in Reporting Services:
- Back up a copy of the symmetric key so that you can use it to recover a report server installation or as part of a planned migration.
- Restore a previously saved symmetric key to a report server database, allowing a new report server instance to access existing data that it did not originally encrypt.
- Delete the encrypted data in a report server database in the unlikely event that you can no longer access encrypted data.
- Re-create symmetric keys and re-encrypt data in the unlikely event that the symmetric key is compromised. As a security best practice, you should recreate the symmetric key periodically (for example, every few months) to protect the report server database from cyber attacks that attempt to decipher the key.
- Add or remove a report server instance from a report server scale-out deployment where multiple report servers share both a single report server database and the symmetric key that provides reversible encryption for that database.
If you are running your Reporting Services in Sharepoint Mode, you should not that backup process does not backup encryption keys and credentials for unattended execution accounts (UEA) or windows authentication to the Reporting Services database.
Periodically changing the Reporting Services encryption key is a security best practice. A recommended time to change the key is immediately following a major version upgrade of Reporting Services. Changing the key after an upgrade minimizes additional service interruption caused by changing the Reporting Services encryption key outside of the upgrade cycle. And since every release of Reporting Services over the last decade has all been major, breaking changes, deprecating and almost no backwards compability, this will make good sense.
Luckily I haven’t been challenged on any Reporting Server installation, in terms of Encryption, yet. We are about to kick off a big migration of our environment here in Maersk Line IT, moving from Sharepoint 2010 to Sharepoint 2013, with Reporting Services in Sharepoint Integrated Mode. I have already made sure that keys have been backed up and we know all the password we need to know. Now only the easy part of migrating the platform remains…
Another product of the BI stack that makes use of Encryption is Integration Services. Koen Vereeck (b|t|l) has written on this in another TSQL2SDAY blog post, which can be found here. I am not going to write a copy of what Koen has written, It’ll suffice with a link to his blog post and a note not to use the default setting.
The most frequent issue I have met with this ridiculous default setting, is that when developers deploy their packages into a new environment, such as test, the packages fail – The Developers then yells, screams and pulls their hair, some even cry sobbing “It’s working on my machine”, until someone shows them the fine art of changing the ProtectionLevel property.
The current version supports the following settings (src):
| Protection level | Description |
|---|---|
| DontSaveSensitive | No data on sensitive properties is saved |
| EncryptAllWithPassword | Uses a password to encrypt the whole package. |
| EncryptAllWithUserKey | Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS |
| EncryptSensitiveWithPassword | Uses a password to encrypt only the values of sensitive properties in the package. |
| EncryptSensitiveWithUserKey | Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. |
| ServerStorage | Protects the whole package using SQL Server database roles. This option is supported when a package is saved to the SQL Server msdb database. In addition, the SSISDB catalog uses the ServerStorage protection level. |
This was my 50 cents on the topic of Encryption – Thanks for hosting Ken!
