CEK key is not available and migrations won’t apply after delete the local database created the previously when I was run my application

Question

I implemented the Always Encrypted Concept with entity framework in my application through this below link.

https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/

This is the Up () migration method in my initialSchema.cs file under migrations folder of my current application.

public override void Up()
    {
        CreateTable(
            "dbo.PersonalInfoTables",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Firstname= c.String(),
                    LastName= c.String(),
                    Address= c.String(),
                    SSN = c.String(),
                })
            .PrimaryKey(t => t.ID);

    }

After that I comment the SSN filed and added the below SQL statement for encrypting the SSN column.

 //manually add the encrypted columns
        Sql("ALTER TABLE [dbo].[PersonalInfoTables] ADD [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH(ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK_Auto1) NOT NULL");

And then run the Update-Database command in Package Manger Console.

The above code will work only if the CEK key already available for the encrypted column. but when ever I run my application once again after delete the existing local database, it gives the exception like CEK key is not available and also did not apply the migrations.

How can I resolve the above issue, after added ALTER SQL statement and delete the local database?

The above issue resolved only, changed Up () migration code once again, but I don’t want that scenario.

score 0 · Answer 1 · answered Feb 03 '17 at 18:34

0

Please refer to Code First – Migrations section of this article, you need to Create database Column Master Keys, Column Encryption Keys, schema etc. outside Entity Framework (e.g. by using SQL Server Management Studio). More information about this can be found here

answered Feb 03 '17 at 18:34

Nikhil Vithlani - Microsoft

797
5
13

Thanks, I followed the same link in this blog contains Up() migration code it's executed successfully if the CEK key already exists it works when we are encrypted specified column otherwise it gives the exception. I am working with local database in SSMS. – Pradeep Feb 04 '17 at 04:47
1

Yes it is supposed to give you an exception if the CEK does not exist. You will need to provision the CEK before you execute the Up() method – Nikhil Vithlani - Microsoft Feb 05 '17 at 08:23

CEK key is not available and migrations won’t apply after delete the local database created the previously when I was run my application

1 Answers1