Symptoms
SQL issues with SCSSOKey after database fail-over
Diagnoses
A few months ago we contacted you asking for help solving the SCSSOKey issue. You gave us a SQL script to run to update the Master Key and that solved the issue. We've had this same SCSSOKey error about 4 more times now and our Database Administrators tell us this happens just after our database server fails-over to the second database server (we have 2). I recently learned that instead of running the script the Database Administrators fix this issue by switching the active server back to the original one.
I spoke with our Systems Engineer this afternoon, Ahmad Garakani. He designed our K2 environment. His question is:
When we first installed K2 on the Production domain we only had one database server, so during the installation we connected directly to that one server. Afterwards, though, we added a second database server and set up a listener to connect the two. We re-ran the setup program and changed the database connection setting to use the listener instead of connecting directly to one of the two servers. Is it possible that when we did this update not every setting in every config file was updated? Could there still be a setting in some file that is still pointing directly to the first database?
If not, is there any known issue with K2 or our current environment configuration that would cause an issue like this?
Another symptom of this issue: We have a process that, when a user completes their task, an update will be made to a table in a 2nd SQL database. This works perfectly everytime except just after the database fails-over and the SCSSOKey error is present. When this is occurring the user can complete the task however the table in the 2nd SQL database never gets updated. No error is ever presented to the user or is found in the logs / error profiles.
Resolution
Based on this article: http://help.k2.com/kb001572
We comply with Microsoft SQL technologies and how they encrypt/decrypt those keys.
The KB explains how the "Service Master Key", "Database Master Key" and "Symmetric Key" works. At the very end, there are links to TechNet articles that also explains how to backup and restore the "Service Master Key". In the TechNet article, there should also be a link on how to alter the "Service Master Key"... Just for the sake of "completion"
This "Service Master Key" is the very "heart" of SQL Server encryption. If you make a full backup of the DB on SQL01 and restore it on SQL02, the "Database Master Key" and "Symmetric Key" might be the same, but the "Service Master Key" used to encrypt those keys will differ, and if they are not the same on both SQL machines, we will not be able to decrypt the keys.
