Sometimes, after restoring a CRM On-Premises organization (from a database backup), you end up with the Data Encryption inactive (Settings > Data Management > Data Encryption) and that might be a headache for future development on the restored organization (e.g. custom plugins), especially if the original organization Encryption Key cannot be shown as below (Show Encryption Key check is disabled):
One of the errors you might encounter is Cannot open Sql Encryption Symmetric Key because Symmetric Key password does not exist in Config DB.
But there is a solution to decrypt the encryption key! 🙂 All you need is a SQL Server connection to the original organization database. To find out the current encryption (symmetric) key, use the following SQL command:
USE MSCRM_CONFIG
SELECT ColumnName, VarBinaryColumn FROM OrganizationProperties
WHERE Id IN (SELECT Id FROM Organization WHERE UniqueName = '')
AND ColumnName = 'SymmetricKeySource'
We need the whole value from the VarBinaryColumn (something like “0x36373A303…”).
Then .NET comes into help, with the following C# method:
public static string DecryptCRMKey(string key)
{
var sb = new StringBuilder();
for (int i = 2; i < key.Length; i += 2)
{
var b1 = byte.Parse(key.Substring(i, 2), NumberStyles.HexNumber);
var part = ASCIIEncoding.ASCII.GetString(new byte[] { b1 });
sb.Append(part);
}
var parts = sb.ToString().Split(':');
sb.Clear();
for (int i = 0; i < parts.Length; i += 2)
{
var b1 = byte.Parse(parts[i]);
var b2 = byte.Parse(parts[i + 1]);
var part = UnicodeEncoding.Unicode.GetString(new byte[] { b1, b2 });
sb.Append(part);
}
return sb.ToString();
}
In order to decrypt the symmetric key, we'll use the value we've got from SQL:
// usage
string key1 = DecryptCRMKey("0x36373A303..."); // outputs the decrypted key
And we're done! Now we can go back to Settings > Data Management > Data Encryption and activate our Encryption Key:
Happy coding! 🙂
I have tried the above code for finding the encryption key but am getting some Chinese text like “碣衇疞૭蕺퀪鑥旇郷槶⫦쪭벚䮕ᨔ藦ꡩ♜ᐠ켺㕔쨬쀋塹ኢ완홵䵻煌㎮”
did you decode the ‘SymmetricKeySource’ or the ‘SymmetricKey’? first is the correct one for decoding… also, please check if the “chinese text” isn’t the actual decrypted key you need (try to activate it). unicode has its “magic ways” 😉
Thank you very much. Its working fine 🙂
no prob 🙂 glad it’s working