Database Key error

  • 7 June 2016
  • 4 replies
  • 375 views
M

Symptoms

 


Receiving Database Key error after restoring K2 database on new SQL Server instance. Detailed error message:

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 open the key before using it.System.Data.SqlClient.SqlException (0x80131904): Please create a master key in the
 

 

Diagnoses

 


Whenever you move/restore K2 database onto new SQL Server instance it is required for you to rebuild SCSSOKey for K2 database.
 

 

Resolution

Resolution:

- Create backup of K2 Database
- Stop the K2 Blackpearl Service
- Run the following SQL scripts:
 

 

--1.

 

ALTER SERVICE MASTER KEY FORCE REGENERATE

 

 

 

--2. 

 

DROP SYMMETRIC KEY SCSSOKey

 

DROP CERTIFICATE SCHostServerCert

 

DROP MASTER KEY

 

 

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '(Contact K2 Support for this detail)'

 

--Create certificate

 

--Modify dates if necessary

 

CREATE CERTIFICATE SCHostServerCert WITH SUBJECT = ’Host Server Certificate’, START_DATE = '01/01/2007', EXPIRY_DATE = '01/01/2020'

 

--Create symmetric key

 

CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SCHostServerCert

 

--Grant control on keys

 

--Replace DomainUser as required

 

GRANT CONTROL ON SYMMETRIC KEY::SCSSOKey TO [DomainUser]

 

GRANT CONTROL ON SYMMETRIC KEY::SCSSOKey TO [DomainUser]

 

GO

 

 

 

--3.

 

OPEN MASTER KEY DECRYPTION BY PASSWORD = '(Contact K2 Support for this detail)'

 

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

 

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

CLOSE MASTER KEY

 

GO

 


- Start the K2 Blackpearl Service

 

 

 

* If you use SQL Server 2016 or newer use AES_256 instead of TRIPLE_DES as starting from SQL Server 2016 TRIPLE_DES encryption algorithm is no longer supported. Just replace "TRIPLE_DES" with "AES_256" in the script 2 above. More details can be found in Microsoft's Documentation.

 

* In case when 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 

4 replies

B

To resolve Error:"Please create a master key in the database or open the master key in the session before performing this operation."


the complete resolution is as follows:
1. Backup the K2 database first and stop the K2 Blackpearl service
2. Run the following script


USE K2
DROP SYMMETRIC KEY SCSSOKey
DROP CERTIFICATE SCHostServerCert
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*personal details removed*'
CREATE CERTIFICATE SCHostServerCert
WITH SUBJECT = 'Host Server Certificate', START_DATE = '01/01/2007', EXPIRY_DATE = '01/01/2029'
CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SCHostServerCert


 


3. But firstly You need to open a k2 support ticket to give the complete  MASTER KEY ENCRYPTION because the one in this example contains *personal details removed* 

B

new info on the  SCSSO key create script when moving the K2 Database to a new SQL server.

 

For K2 4.6.11 and earlier we used the following line: CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = TRIPLE_DES

 

For K2 4.7 and later ; it must  be changed to: CREATE SYMMETRIC KEY SCSSOKey WITH ALGORITHM = AES_256

 

This is due to the fact that for SQL 2016 ; the support for the TRIPLE_DES algorithm has been dropped 

RaymondJVR
Userlevel 4
Badge +13

An existing KB Article for this is available, the KB article provides a step by step guide on restoring or moving a K2 database succsessfully.


 


Restoring a Database KB Article:


https://help.k2.com/support-services/kbt135736?version=published


 


This article is based all K2 version post 4.6.9 and can also be used when experiecing errors like the below:



  • 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

B

one more tip...you may need to run the script under SA account

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings