So in my first post we looked at simply enabling the “Always Encryption“, then in the second post we looked at using C# code to insert and retrieve encrypted values.

For this post I am going to walk through enabling it within a SharePoint Content Database. Firstly I would like to say that this is me playing and testing, not officially giving you any guidance whatsoever. As such a quick disclaimer.

DISCLAIMER: DO NOT DO THIS AT ALL IN YOUR SHAREPOINT ENVIRONMENT, THIS ME JUST PLAYING

DISCLAIMER: DO NOT DO THIS AT ALL IN YOUR SHAREPOINT ENVIRONMENT, THIS ME JUST PLAYING

DISCLAIMER: DO NOT DO THIS AT ALL IN YOUR SHAREPOINT ENVIRONMENT, THIS ME JUST PLAYING

So as before we go into the database that we wish to enable it on. In this case I am using my out of the box Portal Site. I am going to back it up first to be safe. Once done we expand the “Security” node within the selected database, and expand the “Always Encrypted Keys” node.

You can then add the new keys you wish to use, in fact we are going to select the existing ones we already have in the creation screens.

Column Master Keys

Column Encryption Key

These keys should now be listed within the SQL Management Studio.

Next we use the wizard to simply enable encryption on the tables and databases we wish to use. For this example I am going to use the “DocStreams” table as that one contains all the content uploaded into SharePoint.

Using the wizard you can select the “DocStreams” table and then select the field or fields you wish to encrypt. The wizard can take some time to load, due to the amount of tables and columns within the SharePoint Content Database.

Once it has loaded, you can expand the list of columns and the wizard will outline which fields are not supported. For this example I am simply going to set the “Content” field as the encrypted field using the key that was created within the content database.

The two different kinds of encryption types that SQL Server 2016 uses when encrypting Always Encrypted columns. The two encryption types are Deterministic and Randomized. When a specific plain text value of a deterministic encryption type column is encrypted it always produces the same encrypted value. Whereas when a randomized encrypted type Always Encrypted column is encrypted it does it in a less predictable manner. Meaning that SQL Server doesn’t always encrypt a single plain text value to the same encrypted value. For fields like “Content” this would make sense to choose “Deterministic” versus “Randomized“, this ensures that the encrypted value is the same for the same value being sent.

If you want to see the actual code that is being used within SQL Server for the Encryption and Decryption you can see it here: http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlAeadAes256CbcHmac256Algorithm.cs

I did notice that while doing this, that SQL Management Studio would often crash during the process. A restart of the management studio would allow me to continue. Once the settings are done, the wizard performed its changes.

On the first run through it failed with the following error:

“[Error] WorkitemExecution: Message:Work item ‘Performing encryption operations’ did not complete. Details: Cannot save package to file. The model has build blocking errors:

Error SQL71501: Error validating element [pjpub].[MSP_ExchangeSync_ReadTasksByAssnIds]: Procedure: [pjpub].[MSP_ExchangeSync_ReadTasksByAssnIds] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [pjpub].[MSP_TVF_ASSIGNMENTS_SAVED].[TASK_NAME], [pjpub].[MSP_TVF_RESOURCES].[TASK_NAME] or [pjpub].[MSP_TVF_TASKS_SAVED].[TASK_NAME].

Error SQL71501: Error validating element [pjpub].[MSP_ADMIN_GetProjectDataForResPlanMigration2]: Procedure: [pjpub].[MSP_ADMIN_GetProjectDataForResPlanMigration2] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [pjpub].[MSP_TVF_PROJECTS].[RP], [pjpub].[MSP_TVF_RESOURCE_PLAN_ASSIGNMENTS].[PROJ_UID], [pjpub].[MSP_TVF_RESOURCE_PLANS].[PROJ_UID] or [pjpub].[MSP_TVF_RESOURCE_PLANS].[RP].

Error SQL71501: Error validating element [pjpub].[MSP_ExchangeSync_ReadTasksWithTransactionComments]: Procedure: [pjpub].[MSP_ExchangeSync_ReadTasksWithTransactionComments] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [pjpub].[MSP_TVF_ASSIGNMENTS_SAVED].[TASK_NAME], [pjpub].[MSP_TVF_RESOURCES].[TASK_NAME] or [pjpub].[MSP_TVF_TASKS_SAVED].[TASK_NAME].”

The issue seems to be related to stored procedures within the content database that are for Project Server components. So hacking the database, WHICH YOU SHOULD NOT DO EVER, I deleted the “MSP” stored procedures and ran it again. This time it failed again as I assumed it would.

“[Error] WorkitemExecution: Message:Work item ‘Performing encryption operations’ did not complete. Details: Add or update objects failed due to the following errors:

Error SQL46010: Incorrect syntax near Column.

Error SQL71001: Cannot find element referenced by the supporting statement.“

So for now, I am unable to enable it using SQL, or any other way due to the content database issue. For a non-SharePoint database then you can enable it column by column as expected. In reality using Transparent Data Encryption (TDE) combined with Always Encryption would ensure that the entire database is encrypted at REST, then the specific data is encrypted in transit. In fact, once data is encrypted, the data appears as an encrypted binary blob at all stages within the SQL Server database, on disk, in memory, during computations, and over the network.

More details to come for SharePoint and Encryption.