Symptoms

We are storing sensitive information in one of our SQL databases for our workflows and are generating smart objects out of those tables. We would like to know how to encrypt and decrypt SQL Server data using a SQL Server Service Object.
 

Diagnoses

There is a way to encrypt smartbox smartobject data as per the following article...

At the moment there is no "Out of the Box" way to encrypt data in a SQL table using a SQL Server Service instance.
 

Resolution

Here are 2 possibilities that one can play with...

1.) Because our OOTB CRUD methods are not going to work here, you can create your DB and use stored procedures to INTERT, UPDATE, DELETE and SELECT the records using encryption directly IN the SPROCS.

You can then use "Cryptographic Functions", a "passphrase", a "symmetric key", a "asymmetric key" or a "certificate" to encrypt and decrypt data in the SPROCS. You can then wrap those SPROCS up in a smartobject that you can use in K2.

Please see the following MSDN article for more information on this: https://msdn.microsoft.com/en-us/library/bb510663.aspx
Check out the "Related Content" for sample SPROCS on how to encrypt and decrypt the data directly in the SPROC.

2.) The "other" way to go about things, will be to write your own custom K2 broker. Although this will be a bit more work, I think this will be a better way to go. Then you can have the broker do all the hard work without "exposing" the way you encrypt the data. You can then register your broker with K2 and use that in your Workflows/Forms.

Additional Information: I have routed the ticket to the LABS support team and requested that they log a feature request in TFS that will allow the end user to configure the service object to encrypt certain columns. If this feature has been deemed viable, this should be included in a future release of K2.