No ratings

Commands to update K2 Database encryption


PowerShell Commands to Encrypt and Decrypt Data when Moving and Restoring a K2 Database


If you are using K2 Five (5.3), you must install Fix Pack 38 or later to follow the steps in this article.
See for Fix Pack release notes and download links.

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.
To move and restore your K2 database you must change the encryption password and take responsibility for your data encryption. If you lose your password, your encrypted 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.
    If you are migrating your K2 database from SQL Server 2016 or earlier, to SQL Server 2017 or later, there are additional steps you need to perform on the new SQL Server instance before you can proceed. Refer to the Considerations section for more information.
  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.


  • If you are migrating the K2 database from SQL Server 2016 or earlier, to SQL Server 2017 or later, just following the instructions to run the PowerShell commands may not be enough. SQL Server 2016 uses SHA1 hashing algorithm for its encryption work, while SQL Server 2017 and later uses SHA2, which is more secure. The difference in hashing algorithm means items encrypted on SQL Server 2016 will have problem decrypting on SQL Server 2017 or later.

    You will need to run the following on your new SQL Server instance before you recreate the master key, certificate and symmetric key on the K2 database.
    DBCC TRACEON(4631, -1);

    We highly encourage you take a look at these 2 articles from Microsoft before proceeding:
  • In an NLB environment, or in an SQL Failover cluster, you must copy and open the master key on the second node. Use this script to do that:
  • We recommend your SQL Configuration is similar to that of the previous SQL Instance for a successful migration.


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)
      ) AS 'Decrypted',
      Encrypted_Text AS 'Encrypted'
      ) AS 'Decrypted',
      Encrypted_number AS 'Encrypted'
    FROM [SmartBoxData].[EncryptionTest]
  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.


Labels: (1)
Version history
Last update:
‎05-25-2022 08:30 PM
Updated by: