Upgrade: Mediachase_ECF50_MDP_Key error

Vote:
 

We're trying to upgrade a clients Commerce solution from 8.16.1 to 9.0.0. But while migrating we're stumpling upon the following error:

  • 15:38:36: Completed migration step: Migrates published Catalog Content
  • 15:38:36: Migrates published Catalog Content has failed with exception 'System.Data.SqlClient.SqlException (0x80131904): Please create a master key in the database or open the master key in the session before performing this operation. The key 'Mediachase_ECF50_MDP_Key' is not open. Please open the key before using it. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command) at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters) at EPiServer.Commerce.Internal.Migration.Steps.MigratePublishedContentStep.MigratePropertyData(DataTable propertyData, DataTable contentExData, String tableName, DataTable idsToDelete) at EPiServer.Commerce.Internal.Migration.Steps.MigratePublishedContentStep.MigrateCatalogContent(MetaClass metaClass, IList`1 languages) at EPiServer.Commerce.Internal.Migration.Steps.MigratePublishedContentStep.Execute(IProgressMessenger progressMessenger) ClientConnectionId:d7583c3d-4d2b-473c-a23e-92c600573230 Error Number:15581,State:3,Class:16'.
  • 15:38:36: Migrating table 'CatalogEntryEx_CourseOccasionVariation'.
  • 15:38:36: Migrated 0 record(s) for table 'CatalogEntryEx'.
  • 15:38:36: Migrating table 'CatalogEntryEx'.
  • 15:38:36: Migrated 0 record(s) for table 'CatalogNodeEx'.
  • 15:38:36: Migrating table 'CatalogNodeEx'.
  • 15:38:36: Migrating Catalog.
  • 15:38:36: Starting migration step: Migrates published Catalog Content
  • 15:38:36: Completed migration step: Migrates content guid
  • 15:38:36: Removing Catalog Content Guid Mapping tables.
  • 15:38:36: Migrating Catalog Entry Content Guid.
  • 15:38:36: Migrating Catalog Node Content Guid.
  • 15:38:36: Migrating Catalog Content Guid.
  • 15:38:36: Starting migration step: Migrates content guid
  • 15:38:36: Completed migration step: Migrate Legacy Admins Role
  • 15:38:36: Migrating role...
  • 15:38:36: Starting migration step: Migrate Legacy Admins Role

Can't find much on the subject. Anyone that has encountered the same issue? What to do?

#141617
Nov 16, 2015 15:45
Vote:
 

Hi,

Make sure you run EncryptionSupport.sql, located in packages\EPiServer.Commerce.Core<version>\tools\OptionalFeatures (You probably need to drop to drop the Master key first)

Regards,

/Q

#141623
Nov 16, 2015 16:46
Vote:
 

Thanks Quan Mai! That solved it, and as you expected I had to drop the master key first.

#141624
Nov 16, 2015 17:18
Vote:
 

Hi,

How do I drop the master key? I tried to run the following:

ALTER DATABASE [OurCommerceDatabase] SET ENCRYPTION OFF
DROP DATABASE ENCRYPTION KEY
DROP CERTIFICATE Mediachase_ECF50_MDP
DROP MASTER KEY

But I got the following errors:

Msg 33106, Level 16, State 1, Line 1
Cannot change database encryption state because no database encryption key is set.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 33104, Level 16, State 1, Line 2
A database encryption key does not exist for this database.
Msg 15352, Level 16, State 1, Line 3
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.
Msg 15580, Level 16, State 1, Line 4
Cannot drop master key because certificate 'Mediachase_ECF50_MDP' is encrypted by it.

Lars

#142459
Dec 11, 2015 9:41
Vote:
 

I think only those are not needed:

ALTER DATABASE [OurCommerceDatabase] SET ENCRYPTION OFF
DROP DATABASE ENCRYPTION KEY

And you need to drop the Symmetric key as well

/Q

#142466
Dec 11, 2015 11:48
Vote:
 

I don't know what did it, but by trial and error suddenly these two lines worked:

DROP CERTIFICATE Mediachase_ECF50_MDP
DROP MASTER KEY

I was then able to run EncryptionSupport.sql, and then the migration worked.

Thanks!

Lars

#142468
Dec 11, 2015 12:58
Vote:
 

We're experiencing the same issue when deploying to our test environment. This is not ideal, and we can't run these SQL queries manually when we eventually deploy to prod.

I'll look into creating a custom IMigrationStep that drops the master key and executes EncryptionSupport.sql and set the order to be lower than the automatic migration steps. Or is there a better solution?

Lars

#142556
Dec 14, 2015 16:37
Vote:
 

Hi, 

Getting the same issue - however I can't run:

DROP CERTIFICATE Mediachase_ECF50_MDP because:

Msg 15352, Level 16, State 1, Line 11
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Lars - don't suppose you remember how you got it to work?

EDIT-------

Did the following to get past it:

(close symmetric key if open)
Drop symmetric key Mediachase_ECF50_MDP_Key

Then the Drop certificate worked.

Thanks,
Dean

#142741
Edited, Dec 21, 2015 16:20
Vote:
 

Hi,

You probably need to drop the Symmetric key Mediachase_ECF50_MDP_Key first.

/Q

#142751
Dec 21, 2015 16:38
Vote:
 

Thanks Quan, just figured it out :)!

#142753
Dec 21, 2015 17:14
Vote:
 

Hi,

I created an IMigrationStep that is run before the Commerce 9 migration steps, so that we don't have to do this manually in each environment:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using EPiServer.Commerce.Internal.Migration.Steps;
using EPiServer.ServiceLocation;
using Mediachase.Commerce.Shared;

namespace EID.Web.Migrations
{
    [ServiceConfiguration(typeof(IMigrationStep))]
    public class _151214_DropMasterKeyAndRunEncryptionScript : IMigrationStep
    {
        public int Order => 99; // Commerce 9 migration scripts start at 100
        public string Name => "Update Commerce database encryption";
        public string Description => "Because of an error in the automatic migration steps when upgrading to Commerce 9.0, we first have to drop the database master key and then run EncryptionSupport.sql";

        public bool Execute(IProgressMessenger processMessenger)
        {
            string scriptPath;
            string[] encryptionScriptBatches;

            try
            {
                scriptPath = Path.Combine(HttpRuntime.AppDomainAppPath, "path\\to\\EncryptionSupport.sql");
                var encryptionScript = File.ReadAllText(scriptPath);

                // 'GO' is not a T-SQL command. Splitting script into batches and removing the 'GO' keyword.
                encryptionScriptBatches = encryptionScript.Split(new[] {"GO\r\n", "GO\n"}, StringSplitOptions.RemoveEmptyEntries);
            }
            catch (Exception e)
            {
                processMessenger.AddProgressMessageText($"Could not read script file. Exception: {e}", true, 0);
                return false;
            }

            SqlTransaction transaction = null;

            try
            {
                using (var sqlConnection = new SqlConnection("CommerceDbConnetionString"))
                {
                    sqlConnection.Open();
                    transaction = sqlConnection.BeginTransaction();

                    using (var dropCommand = sqlConnection.CreateCommand())
                    {
                        dropCommand.Transaction = transaction;
                        dropCommand.CommandText = @"DROP SYMMETRIC KEY Mediachase_ECF50_MDP_Key
                            DROP CERTIFICATE Mediachase_ECF50_MDP
                            DROP MASTER KEY";
                        dropCommand.ExecuteNonQuery();
                        processMessenger.AddProgressMessageText($"Master key dropped, running '{scriptPath}'.", false, 50);
                    }

                    foreach (var encryptionScriptCommandLine in encryptionScriptBatches)
                    {
                        using (var encryptionScriptCommand = sqlConnection.CreateCommand())
                        {
                            encryptionScriptCommand.Transaction = transaction;
                            encryptionScriptCommand.CommandText = encryptionScriptCommandLine;
                            encryptionScriptCommand.ExecuteNonQuery();
                        }
                    }

                    processMessenger.AddProgressMessageText($"'{scriptPath}' has been run, committing transaction.", false, 90);
                    transaction.Commit();
                    return true;
                }
            }
            catch(Exception e)
            {
                processMessenger.AddProgressMessageText($"An exception was thrown: {e}", true, 10);
                transaction?.Rollback();
                return false;
            }
        }
    }
}

Hope this helps someone. Please let me know if EPiServer fixes this.

Lars

#142755
Dec 21, 2015 17:37
Vote:
 

Hi,

I am getting the same issue but when I migrated locally it worked fine but when I try migrating the database in the AZURE SQL server, I ran in to a bunch of issues from which most of it was cleared except one in particular which is the symmetric key missing

("

Cannot find the symmetric key 'Mediachase_ECF50_MDP_Key', because it does not exist or you do not have permission.

")

My question is, is thre anyway we can make the commerce run with out any security? Or is there a way to include this key to azure database?

PS: I tried running 'EncryptionSupport.sql' which ran ok @local SQL server but a bit shy in the cloud :-)

Any help is appreciated.

R

#142991
Jan 08, 2016 10:49
Vote:
 

Hi,

Sql Azure Database does not support the encryption we have in SQL Server yet. They have limited encryption support and I heard they have something in their backlog, but for now that's what we have.

From 8.15 (IIRC), newly created site will not have encryption support by default. Those who want to enable encryption will have to run EncryptionSupport.sql. If you plan to run your website on Azure, you can skip that step.

Regards,

/Q

#142992
Jan 08, 2016 11:20
Vote:
 

Hi Quan,

Thanks for your swift response.

I did skip the step thats when I get this error loading a content from the catalog, as Episerver's dll expecting the symmetric key in the comrece database which does not exist in the AZURE DB. I was wondering is there any way to by pass this error through a config setting of somewhat to avoid encryption?

Thanks,

R

#142993
Jan 08, 2016 11:42
Vote:
 

Hi,

Which version did you upgrade from?

If you created your site from prior 8.15, your database has encryption support setting enabled, so to let it run on Azure, you'll have to install the EPiServer.Commerce.Azure to your site to make its compatible. It will do opposite thing with EncryptionSupport.sql - dropping any encryption support.

You might try to run the sql files found in packages\EPiServer.Commerce.Azure<version>\tools\epiupdates_commerce\sql. They should have been run automatically with update-epidatabase, but it would be harmless if you try to run them again. (Always remember to backup for databases first).

/Q

#143017
Jan 08, 2016 17:14
Vote:
 

Faced with the same deployment isseus as Lars Smeby we decided to use his code to fix the issue, and it worked! So thanks for the code Lars! :)

#145658
Mar 09, 2016 11:11
Vote:
 

Glad I could help :)

But seriously, this shouldn't be an issue! Epi should release a proper fix.

Lars

#145659
Mar 09, 2016 11:21
Vote:
 

@Lars Smeby Your solution is work perfect. generate that class : change connection string and path to sql encrypt. And run. Thank you

#174077
Jan 18, 2017 4:59
Vote:
 

@Lars Smeby, thanks a lot ! :)

#175621
Feb 24, 2017 11:37
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.