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

KB002368

PowerShell

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.

Change your master key password for extra security

To help prevent encrypted data in the K2 database from being compromised in database backups, we recommend updating the K2 Database Master Key password. The master key password protects all encryption certificates and symmetric keys below it, and if compromised could lead to exposure of sensitive data.

Use the following steps to update the master key password without moving the database:

1. Open a new query against the K2 database in SQL Management studio while logged in with a database administrator account.
2. Run the following command, updating the X's with your chosen password:
   ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'XXXXXXXXXXXXXXXXX'
3. If in an NLB environment, or in an SQL Failover, the new master key must be opened on all nodes using the OPEN MASTER KEY command in the considerations section.

Use the following steps to update the master key password while moving the database to a new SQL server:

1. Add the -MasterKeyPassword 'XXXXXXX' parameter to the Add-K2MasterKey command in the Command Usage Example given in the following PowerShell Commands section, changing the X's to your chosen password. 
   This password must not be the same as the password that was set in the first step of the example.

Note:  You must keep this master key password in a secure location. Losing the master key password is irrecoverable, even by K2 Support staff, and could lead to loss of data. Updating your database master key password does not update it in previous backups and old backups will remain at risk of exposure.

List of K2 Database Tables that get Encrypted

PowerShell Commands

Use the following PowerShell command to import the module and make the commands available. The commands need to be run every time a K2 database is migrated. Run Windows PowerShell as the Administrator and enter this command in the folder where you extracted the K2 installation package (for example C:UsersAdministratorDesktopK2 (5.1004.0000.0)Installation):

Note:  The steps must be followed in order to safely move/migrate a K2 database without any issues with encryption. If not done, you will get encryption errors when running the installer against your K2 database.

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. This password is separate from the master key password and is used by K2 in the database for encrypting the sensitive data values usually in the HostServer.Configration table. 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.

Considerations

• 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:
  • FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key
  • Create identical symmetric keys on two servers
     
• 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:
  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
  ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
• 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)
   OPEN SYMMETRIC KEY bSCSSOKey] DECRYPTION BY CERTIFICATE ESCHostServerCert];
   SELECT
     CONVERT(
       NVARCHAR(Max),
       DecryptByKey(Encrypted_Text)
     ) AS 'Decrypted',
     Encrypted_Text AS 'Encrypted'
   FROM
     TSmartBoxData].tEncryptionTest]
   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.