K2 Database Move/Migrate

  • 16 February 2021
  • 0 replies
  • 2023 views

Userlevel 5
Badge +20
 

K2 Database Move/Migrate

kbt135736

PRODUCT
K2 Five 5.0
K2 blackpearl 4.6.9 to 4.7
BASED ON
K2 blackpearl 4.6.11
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.

Issue

After a K2 Database has been moved/migrated to another Server, the connection to the database needs to be updated. 

 

 

Symptoms

Some of the possible errors that could occur due to this issue include:

  • The key 'SCSSOKey' is not open. Please open the key before using it.
  • Please create a master key in the database or open the master key in the session before performing this operation.The key 'SCSSOKey' is not open.
  • Please create a master key in the database or open the SCSSO master key in the database.
  • The "SCSSO" key was not updated when the database restore was performed.

Resolution

Please ensure that you do the following in order to align the SCSSO keys in your system:

1. Create a backup of the K2 Database
2. Stop the K2 Blackpearl Service
3. Run the following SQL scripts:
 

* To obtain the master key password you will need to log a support ticket requesting the password, please reference this article.
* Please ensure that you use the applicable script below based on your K2 Version.

* The below scripts only applies to K2 Five 5.0 and lower.
* All newer K2 versions starting at K2 Five 5.1 and higher will require PowerShell Commands to Encrypt and Decrypt Data instead, see this KB article for detailed steps:
https://help.k2.com/kb002368

 

Required Script Alterations Before Script Execution:
* After obtaining the master key password replace it with the sections labelled as ‘'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
* Replace the [DOMAINUser] sections with the user account running the K2 service.

Recreate SCSSO Key (For K2 4.6.11 and Earlier)

 

Print N'1. Open Master key'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

WAITFOR DELAY '00:00:01';

Print N'2. Create Master key'
IF NOT EXISTS (SELECT 1 FROM Sys.Symmetric_Keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
END
IF EXISTS (SELECT 1 FROM Sys.Symmetric_Keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
GRANT CONTROL ON SYMMETRIC KEY::##MS_DatabaseMasterKey## to [DOMAINUser]
END

WAITFOR DELAY '00:00:01';

Print N'3. Drop SSCSO Key'
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
DROP SYMMETRIC KEY SCSSOKey
DROP CERTIFICATE SCHostServerCert
END

WAITFOR DELAY '00:00:01';

Print N'4. Update Master Key'
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SCHostServerCert')
BEGIN
CREATE CERTIFICATE SCHostServerCert
WITH SUBJECT = 'Host Server Certificate', START_DATE = '01/01/2007', EXPIRY_DATE = '01/01/2030'
END
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SCHostServerCert')
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SCHostServerCert
END
END
IF EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
GRANT CONTROL ON SYMMETRIC KEY::scssokey TO [DOMAINUser]
END

 

Recreate SCSSO Key (For K2 4.7 and K2 Five 5.0)

 

Print N'1. Open Master key'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

WAITFOR DELAY '00:00:01';

Print N'2. Create Master key'
IF NOT EXISTS (SELECT 1 FROM Sys.Symmetric_Keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
END
IF EXISTS (SELECT 1 FROM Sys.Symmetric_Keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
GRANT CONTROL ON SYMMETRIC KEY::##MS_DatabaseMasterKey## to [DOMAINUser]
END

WAITFOR DELAY '00:00:01';

Print N'3. Drop SSCSO Key'
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
DROP SYMMETRIC KEY SCSSOKey
DROP CERTIFICATE SCHostServerCert
END

WAITFOR DELAY '00:00:01';

Print N'4. Update Master Key'
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SCHostServerCert')
BEGIN
CREATE CERTIFICATE SCHostServerCert
WITH SUBJECT = 'Host Server Certificate', START_DATE = '01/01/2007', EXPIRY_DATE = '01/01/2030'
END
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SCHostServerCert')
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SCHostServerCert
END
END
IF EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'SCSSOKey')
BEGIN
GRANT CONTROL ON SYMMETRIC KEY::scssokey TO [DOMAINUser]
END

4. Start the K2 Blackpearl Service

Non-consolidated K2 Databases
* In cases where non-consolidated K2 databases are being used scripts mentioned above have to be run against the following K2 databases:
1. HostServer
2. K2SQLUM
3. K2SmartBox
4. K2SmartBroker

References
KB article: http://community.k2.com/t5/K2-blackpearl/Database-Key-error/ta-p/90663

Possible Errors
Should you be faced with an error on K2 Designer whereby it is not loading, please follow this workaround:

Workaround
* Create a local SQL alias (on your K2 server).
* Configure it to bear the name of the “old” SQL Server instance, and make it point to the new SQL Server instance.

This way, everything you configured in the past to address your SQL Server (your custom instances and other bits of configuration for example), will automatically be redirected to the new location.
View the How-to here: https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/


0 replies

Be the first to reply!

Reply