K2 Database Move/Migrate
kbt135736
PRODUCTIssue
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:
* 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://community.nintex.com/nintex-automation-k2-57/commands-to-update-k2-database-encryption-782
* 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:
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/