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

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

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

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

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

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

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

Hi,

You probably need to drop the Symmetric key Mediachase_ECF50_MDP_Key first.

/Q

Thanks Quan, just figured it out :)!

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

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

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

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

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

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! :)

Glad I could help :)

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

Lars

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

@Lars Smeby, thanks a lot ! :)