K2 Five (5.1) includes PowerShell commands that allow you decrypt and encrypt database data with your own password. This lets you move data from the K2 database to another SQL instance and use your own encryption password.

On-premises SQL servers use SCSSO key encryption, in which SQL creates a certificate and a master key for that certificate, and then uses it to encrypt the data in the SQL server. SQL Azure does not support this method so you can only use the PowerShell commands in this article with on-premises SQL servers.

Before performing the steps in this article, back up your database.
If you change your encryption password, you take responsibility for your data encryption. If you lose your password, your data cannot be retrieved, even by K2 Support.

List of K2 Database Tables that get Encrypted

  • [Smartbox].[SmartboxObject]
  • [Authorization].[OAuthAppOnlyToken]
  • [Authorization].[OAuthIdentity]
  • [Authorization].[OAuthToken]
  • [CustomUM].[User]
  • [HostServer].[UserCertificate]
  • [HostServer].[Configuration]
  • [SmartBroker].[ServiceInstance]

PowerShell Commands

Use the following PowerShell command to import the module and make the commands available. Run Windows PowerShell as the Administrator and enter this command in the folder where you extracted the K2 installation package (for example C:\Users\Administrator\Desktop\K2 (5.1004.0000.0)\Installation):

Import-Module .\SourceCode.Install.Powershell.dll

Command Usage Example

In this scenario, you change the default K2 encryption password to something you choose. Then you export and restore the database to another SQL instance and remove the old encryption. Lastly, you add the new encryption with your new password. Enter the following command in Windows PowerShell:

  1. To keep your data intact you must re-encrypt the database with a password you choose. Make sure you keep this password for later use:
    Update-K2Encryption -NewPassword “Your password”
  2. You can now export the database and restore it to another SQL instance.
  3. Remove the old K2 Encryption Key from a database you've moved (edit the connection string for your environment):
    Remove-K2EncryptionKey -ConnectionString 'Data Source=localhost;Initial Catalog=K2;integrated security=sspi'
  4. Add the new encryption to the database by adding a master key, certificate and encryption key using the following commands in order (edit the connection string for your environment):
    Add-K2MasterKey -ConnectionString 'Data Source=localhost;Initial Catalog=K2;integrated security=sspi'
    Add-K2Certificate -ConnectionString 'Data Source=localhost;Initial Catalog=K2;integrated security=sspi'
    Add-K2EncryptionKey -p "Your password" -Algorithm “AES_256” -ConnectionString 'Data Source=localhost;Initial Catalog=K2;integrated security=sspi'
    For the Add-K2EncryptionKey command, use the -p parameter to enter the password you used in step one, and the -Algorithm parameter to set the encryption algorithm. See Microsoft's Data Encryption in SQL Server article for more information on SQL encryption.

Test Encryption

This test scenario shows you how to confirm that you can retrieve data before and after exporting it to a new SQL instance. The first section tests data before a database export and the second section tests data after export and restoration in another SQL instance.

Before Export

  1. Open K2 Designer and create a new category called Encryption Test. Open the category and create new SmartObject called EncryptionTest.
  2. Add the following properties:
    Name Type
    Encrypted Text Text
    Encrypted Number Number
  3. Encrypt each property by selecting a property, clicking Edit, and checking the Encrypt Property option.
  4. Finish (save) the SmartObject.
  5. Select Generate a View and generate item and list views.
  6. Run the item view and insert a few values.
  7. Run the list view to confirm you have values.
  8. To check that the values are correctly encrypted in the database, decrypt them in SQL with the following script:
    /****** Script for SelectTopNRows command from SSMS ******/
    USE K2
    DECLARE @Variab1eName NVARCHAR(Max)
    OPEN SYMMETRIC KEY [SCSSOKey] DECRYPTION BY CERTIFICATE [SCHostServerCert];
    SELECT
      CONVERT(
        NVARCHAR(Max),
        DecryptByKey(Encrypted_Text)
      ) AS 'Decrypted',
      Encrypted_Text AS 'Encrypted'
    FROM
      [SmartBoxData].[EncryptionTest]
    SELECT
      CONVERT(
        int,
        CONVERT(
          varbinary(Max),
          DecryptByKey(Encrypted_number)
        )
      ) AS 'Decrypted',
      Encrypted_number AS 'Encrypted'
    FROM [SmartBoxData].[EncryptionTest]
    CLOSE SYMMETRIC KEY SCSSOKey;
  9. Import the PowerShell commands as described in the PowerShell Commands section above and update the K2 Encryption with your chosen password as shown in the first step of the Command Usage Example. Then rerun the SQL script from step 8 above and confirm that values are decrypted and match values you entered.
  10. If the values decrypt and match what you entered, you can now export the database.

After Export

Use the following steps to restore the database to another SQL instance, recreate the keys, and confirm the values decrypt correctly by using the following steps.

  1. Import the PowerShell commands as described in the PowerShell Commands section above and then perform steps three and four of the first Command Usage Example.
  2. Re-run the SQL script to see if values decrypt successfully.
  3. If the values return as expected, the database is ready for use.