Azure Cosmos DB SQL API-Encryption-Failure on Add New Item

Question

We have been trying to implement the encryption in Cosmos DB as per the below documentation
https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-always-encrypted?tabs=dotnet

We have been able to create the database with the encryption techniques and also the fields. Below is the code used

var databaseClient = cosmosClient.GetDatabase(createSqlIndexRequest.DatabaseName);
await databaseClient.CreateClientEncryptionKeyAsync(
keyName,
DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256,
new EncryptionKeyWrapMetadata(
keyWrapProvider.ProviderName,
keyName,
keyVaultIdentifier));

        var fieldEncryptionPathList = new List<ClientEncryptionIncludedPath>();  

        foreach (var fieldEncryptItem in createSqlIndexRequest.FieldNamesForEncryption)  
        {  
            fieldEncryptionPathList.Add(new ClientEncryptionIncludedPath()  
            {  
                Path = $"/{fieldEncryptItem}",  
                ClientEncryptionKeyId = keyName,  
                EncryptionType = EncryptionType.Deterministic.ToString(),  
                EncryptionAlgorithm = DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256  
            });  
        }  

        ContainerProperties containerProperties = new ContainerProperties  
        {  
            PartitionKeyPath = createSqlIndexRequest.PartitionKey,  
            Id = createSqlIndexRequest.CollectionName,  
            ClientEncryptionPolicy = new ClientEncryptionPolicy(fieldEncryptionPathList),  
            IndexingPolicy = new Microsoft.Azure.Cosmos.IndexingPolicy()  
            {  
                IndexingMode = Microsoft.Azure.Cosmos.IndexingMode.None,  
                Automatic = false  
            }  
        };  

        var containerCheck = await databaseClient.CreateContainerIfNotExistsAsync(containerProperties, ruLevel.HasValue && !ruLevel.Value ? ruValue : null);  

We have been trying to implement the encryption in Cosmos DB as per the below documentation https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-always-encrypted?tabs=dotnet

We have been able to create the database with the encryption techniques and also the fields. Below is the code used

var databaseClient = cosmosClient.GetDatabase(createSqlIndexRequest.DatabaseName);
await databaseClient.CreateClientEncryptionKeyAsync(
keyName,
DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256,
new EncryptionKeyWrapMetadata(
keyWrapProvider.ProviderName,
keyName,
keyVaultIdentifier));

        var fieldEncryptionPathList = new List<ClientEncryptionIncludedPath>();  

        foreach (var fieldEncryptItem in createSqlIndexRequest.FieldNamesForEncryption)  
        {  
            fieldEncryptionPathList.Add(new ClientEncryptionIncludedPath()  
            {  
                Path = $"/{fieldEncryptItem}",  
                ClientEncryptionKeyId = keyName,  
                EncryptionType = EncryptionType.Deterministic.ToString(),  
                EncryptionAlgorithm = DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256  
            });  
        }  

        ContainerProperties containerProperties = new ContainerProperties  
        {  
            PartitionKeyPath = createSqlIndexRequest.PartitionKey,  
            Id = createSqlIndexRequest.CollectionName,  
            ClientEncryptionPolicy = new ClientEncryptionPolicy(fieldEncryptionPathList),  
            IndexingPolicy = new Microsoft.Azure.Cosmos.IndexingPolicy()  
            {  
                IndexingMode = Microsoft.Azure.Cosmos.IndexingMode.None,  
                Automatic = false  
            }  
        };  

        var containerCheck = await databaseClient.CreateContainerIfNotExistsAsync(containerProperties, ruLevel.HasValue && !ruLevel.Value ? ruValue : null);  

Once the above code runs, we can see the database and container created successfully. We are trying to insert Documents using the DATA Explorer available in Azure Portal, a sample one

{
"id": "1234",
"job_id": 122,
"job_name": "mongo_cosmos_job",
"job_type": "onetimeDataOffload",
"source_connector_id": 98.0,
"source_connector_name": "mongo_src",
"source_type": "mongo",
"source_database": "company",
"destination_connector_id": 99.0,
"destination_connector_name": "cosmosmongo_serverless_dest",
"destination_type": "cosmos",
"destination_database": "mongo_cosmos_db",
"dag_run_id": "manual__2021-10-06T09:56:07.175777+00:00",
"task_id": "extract_and_load_company.test_collection",
"task_name": "extract_and_load_company.test_collection",
"table_name": "test_collection",
"stage": "load",
"record_count": 0,
"size_in_mb": 0.0,
"duration": 0.0,
"is_aggregated": false,
"is_test_dag": false,
"chunk_size": 5000.0,
"ru_level": "",
"ru": 0.0,
"job_execution_datetime": "2021-10-06 09:56:07.177",
"task_execution_datetime": "2021-10-06 09:56:07.177",
"job_status": "success",
"task_execution_endtime": "2021-10-06 09:56:42.650"
}

on click of Save, we get the following error
"the collection has ClientEncryptionPolicy set, but the document to be written isn't encrypted."

Answer 1

Hi @Seema Sam ,

As per my understanding you were trying to upload the document on an encrypted container using Data Explorer from Azure Portal.
I was able to reproduce your issue and as per internal discussions with the products team this looks like an expected behavior as portal's data explorer isn't currently "Always Encrypted" aware and you would need to use the supported SDKs to upload the documents.

Please refer to the code sample to insert documents in Azure Cosmos DB.

Please let me know if you have any questions.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

Hi Saurabh,

We are using Microsoft.Azure.CosmosDB.BulkExecutor SDK to upload the documents into the Encrypted container, since we needed a bulk insert. This also results in the same exception as we received on the Data Explorer in Portal.

IBulkExecutor bulkExecutor = new BulkExecutor(documentClient, collectionCheck);
await bulkExecutor.InitializeAsync();
var bulkImportResponse = await bulkExecutor.BulkImportAsync(
documents: batchDocuments,
enableUpsert: true);

Thanks and Regards
Seema

Answer 3

Hi @Seema Sam ,

I have tested Bulk Import on an Encrypted Container using V3 as per the documentation shared above and tweaking this Bulk Insert sample and it worked as expected. I am adding my code below for your reference if this helps.

namespace Microsoft.Azure.Cosmos.Samples.Bulk  
{  
    using global::Azure.Identity;  
    using Microsoft.Azure.Cosmos.Encryption;  
    using System;  
    using System.Collections.Generic;  
    using System.Diagnostics;  
    using System.Linq;  
    using System.Threading.Tasks;  
  
    public class Program  
    {  
        private const string EndpointUrl = "https://{CosmosDB}.documents.azure.com:443/";  
        private const string AuthorizationKey = "{AuthorizationKey}";  
        private const string DatabaseName = "samples";  
        private const string ContainerName = "encryptedData";  
        private const int AmountToInsert = 300000;  
        private static string MasterKeyUrl = "{MasterKeyUrl}";  
  
  
        static async Task Main(string[] args)  
        {  
             
            CosmosClientOptions clientOptions = new CosmosClientOptions()  
            {  
                TokenCredentialBackgroundRefreshInterval = TimeSpan.FromSeconds(30)  
            };  
  
            var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions  
            {  
                ExcludeVisualStudioCredential = true,  
                ExcludeVisualStudioCodeCredential = true,  
                ExcludeAzureCliCredential = true  
            });  
  
            AzureKeyVaultKeyWrapProvider azureKeyVaultKeyWrapProvider = new AzureKeyVaultKeyWrapProvider(credential);  
  
            // <CreateClient>  
            CosmosClient cosmosClient = new CosmosClient(EndpointUrl, AuthorizationKey, new CosmosClientOptions() { AllowBulkExecution = true }).WithEncryption(azureKeyVaultKeyWrapProvider);  
            // </CreateClient>  
  
            // <Initialize>  
            Database database = await cosmosClient.CreateDatabaseIfNotExistsAsync(Program.DatabaseName);  
  
            // Create the Client Encryption Keys for Encrypting the configured Paths.  
            await database.CreateClientEncryptionKeyAsync(  
                    "key1",  
                    DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256,  
                    new EncryptionKeyWrapMetadata(azureKeyVaultKeyWrapProvider.ProviderName, "akvMasterKey", MasterKeyUrl));  
  
            var path1 = new ClientEncryptionIncludedPath  
            {  
                Path = "/Property1",  
                ClientEncryptionKeyId = "key1",  
                EncryptionType = EncryptionType.Deterministic.ToString(),  
                EncryptionAlgorithm = DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256  
            };  
            var path2 = new ClientEncryptionIncludedPath  
            {  
                Path = "/Property2",  
                ClientEncryptionKeyId = "key1",  
                EncryptionType = EncryptionType.Randomized.ToString(),  
                EncryptionAlgorithm = DataEncryptionKeyAlgorithm.AeadAes256CbcHmacSha256  
            };             
              
            await database.DefineContainer(Program.ContainerName, "/pk")  
                            .WithClientEncryptionPolicy()  
                            .WithIncludedPath(path1)  
                            .WithIncludedPath(path2)  
                            .Attach()  
                            .CreateAsync();  
  
            // </Initialize>  
  
            try  
            {  
                // Prepare items for insertion  
                Console.WriteLine($"Preparing {AmountToInsert} items to insert...");  
                // <Operations>  
                IReadOnlyCollection<Item> itemsToInsert = Program.GetItemsToInsert();  
                // </Operations>  
  
                // Create the list of Tasks  
                Console.WriteLine($"Starting...");  
                Stopwatch stopwatch = Stopwatch.StartNew();  
                // <ConcurrentTasks>  
                Container container = database.GetContainer(ContainerName);  
                List<Task> tasks = new List<Task>(AmountToInsert);  
                foreach (Item item in itemsToInsert)  
                {  
                    tasks.Add(container.CreateItemAsync(item, new PartitionKey(item.pk))  
                        .ContinueWith(itemResponse =>  
                        {  
                            if (!itemResponse.IsCompletedSuccessfully)  
                            {  
                                AggregateException innerExceptions = itemResponse.Exception.Flatten();  
                                if (innerExceptions.InnerExceptions.FirstOrDefault(innerEx => innerEx is CosmosException) is CosmosException cosmosException)  
                                {  
                                    Console.WriteLine($"Received {cosmosException.StatusCode} ({cosmosException.Message}).");  
                                }  
                                else  
                                {  
                                    Console.WriteLine($"Exception {innerExceptions.InnerExceptions.FirstOrDefault()}.");  
                                }  
                            }  
                        }));  
                }  
  
                // Wait until all are done  
                await Task.WhenAll(tasks);  
                // </ConcurrentTasks>  
                stopwatch.Stop();  
  
                Console.WriteLine($"Finished in writing {AmountToInsert} items in {stopwatch.Elapsed}.");  
            }  
            catch (Exception ex)  
            {  
                Console.WriteLine(ex);  
            }  
            finally  
            {  
                Console.WriteLine("Cleaning up resources...");  
                await database.DeleteAsync();  
            }  
        }  
  
        // <Bogus>  
        private static IReadOnlyCollection<Item> GetItemsToInsert()  
        {  
            return new Bogus.Faker<Item>()  
            .StrictMode(true)  
            //Generate item  
            .RuleFor(o => o.id, f => Guid.NewGuid().ToString()) //id  
            .RuleFor(o => o.username, f => f.Internet.UserName())  
            .RuleFor(o => o.pk, (f, o) => o.id) //partitionkey  
            .RuleFor(o => o.Property1, f => f.Internet.UserName())  
            .RuleFor(o => o.Property2, f => f.Internet.UserName())  
            .Generate(AmountToInsert);  
        }  
        // </Bogus>  
  
        // <Model>  
        public class Item  
        {  
            public string id {get;set;}  
            public string pk {get;set;}  
  
            public string username{get;set;}  
  
            public string Property1 { get; set; }  
  
            public string Property2 { get; set; }  
        }  
  
        // </Model>  
    }  
}  

Here is the output for your reference.

Please let me know if you have any questions.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 4

Hi @Saurabh Sharma
Thank you for the detailed answer, my one question at this point would be if I have dynamic data to be inserted how will I be able to do that using V3.
That's where I am stuck and get an error as follows:

One or more errors occurred. (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ac983699-70c0-492b-8977-183a7aa4e17e; Reason: ();) (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ac983699-70c0-492b-8977-183a7aa4e17e; Reason: ();)

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Threading.Tasks;

class Program
{
private const string EndpointUrl = "https://{}:443/";
private const string AuthorizationKey = "{}";
private const string DatabaseName = "bulk-tutorial";
private const string ContainerName = "items";
private const int AmountToInsert = 300000;

    static async Task Main(string[] args)  
    {  
        Console.WriteLine("Hello World!");  
        CosmosClient cosmosClient = new CosmosClient(EndpointUrl, AuthorizationKey, new CosmosClientOptions() { AllowBulkExecution = true });  

        var databaseCheck = await cosmosClient.CreateDatabaseIfNotExistsAsync(Program.DatabaseName);  
        if (databaseCheck.StatusCode == HttpStatusCode.Created)  
            Console.WriteLine("Success");  
        else  
        {  
            if (databaseCheck.StatusCode == HttpStatusCode.OK)  
                Console.WriteLine("Success database Exists");  
        }  

        var databaseClient = cosmosClient.GetDatabase(Program.DatabaseName);  

        await databaseClient.DefineContainer(Program.ContainerName, "/job_id")  
                .WithIndexingPolicy()  
                    .WithIndexingMode(IndexingMode.Consistent)  
                    .WithIncludedPaths()  
                        .Attach()  
                    .WithExcludedPaths()  
                        .Path("/*")  
                        .Attach()  
                .Attach()  
            .CreateAsync(50000);  

        var jsonStringData = "[{\"job_id\": 122, \"job_name\": \"mongo_cosmos_job\", \"job_type\": \"onetimeDataOffload\", \"source_connector_id\": 98.0, \"source_connector_name\": \"mongo_src\", \"source_type\": \"mongo\", \"source_database\": \"company\", \"destination_connector_id\": 99.0, \"destination_connector_name\": \"cosmosmongo_serverless_dest\", \"destination_type\": \"cosmos\", \"destination_database\": \"mongo_cosmos_db\", \"dag_run_id\": \"manual__2021-10-06T09:56:07.175777+00:00\", \"task_id\": \"extract_and_load_company.test_collection\", \"task_name\": \"extract_and_load_company.test_collection\", \"table_name\": \"test_collection\", \"stage\": \"load\", \"record_count\": 0, \"size_in_mb\": 0.0, \"duration\": 0.0, \"is_aggregated\": false, \"is_test_dag\": false, \"chunk_size\": 5000.0, \"ru_level\": \"\", \"ru\": 0.0, \"job_execution_datetime\": \"2021-10-06 09:56:07.177\", \"task_execution_datetime\": \"2021-10-06 09:56:07.177\", \"job_status\": \"success\", \"task_execution_endtime\": \"2021-10-06 09:56:42.650\", \"id\": 2743}, {\"job_id\": 122, \"job_name\": \"mongo_cosmos_job\", \"job_type\": \"onetimeDataOffload\", \"source_connector_id\": 98.0, \"source_connector_name\": \"mongo_src\", \"source_type\": \"mongo\", \"source_database\": \"company\", \"destination_connector_id\": 99.0, \"destination_connector_name\": \"cosmosmongo_serverless_dest\", \"destination_type\": \"cosmos\", \"destination_database\": \"mongo_cosmos_db\", \"dag_run_id\": \"manual__2021-10-06T09:56:07.175777+00:00\", \"task_id\": \"extract_and_load_company.test_collection\", \"task_name\": \"extract_and_load_company.test_collection\", \"table_name\": \"test_collection\", \"stage\": \"transform\", \"record_count\": 0, \"size_in_mb\": 0.0, \"duration\": 0.0, \"is_aggregated\": false, \"is_test_dag\": false, \"chunk_size\": 5000.0, \"ru_level\": \"\", \"ru\": 0.0, \"job_execution_datetime\": \"2021-10-06 09:56:07.177\", \"task_execution_datetime\": \"2021-10-06 09:56:07.177\", \"job_status\": \"success\", \"task_execution_endtime\": \"2021-10-06 09:56:42.597\", \"id\": 2742}]";  
        var binaryDownloadData = JsonConvert.DeserializeObject<List<object>>(jsonStringData);  


        Microsoft.Azure.Cosmos.Container container = databaseClient.GetContainer(ContainerName);  
        List<Task> tasks = new List<Task>(AmountToInsert);  
        foreach (var item in binaryDownloadData)  
        {  
           tasks.Add(container.CreateItemAsync(item)  
                .ContinueWith(itemResponse =>  
                {  
                    if (!itemResponse.IsCompletedSuccessfully)  
                    {  
                        AggregateException innerExceptions = itemResponse.Exception.Flatten();  
                        if (innerExceptions.InnerExceptions.FirstOrDefault(innerEx => innerEx is CosmosException) is CosmosException cosmosException)  
                        {  
                            Console.WriteLine($"Received {cosmosException.StatusCode} ({cosmosException.Message}).");  
                        }  
                        else  
                        {  
                            Console.WriteLine($"Exception {innerExceptions.InnerExceptions.FirstOrDefault()}.");  
                        }  
                    }  
                }));  
        }  

        // Wait until all are done  
        await Task.WhenAll(tasks);  
    }         
}