Enable Column Encryption Azure Function App v2 Error

Question

Currently have a number of Azure Functions running on V1 and looking to start to migrate to v2 however i'm running into issues with enable column encryption using via the sqlstringbuilder.

If i comment out the enable column encryption property the function executes fine and returns data that is encrypted.

I need to enable the column encryption property so that it return data that is decrypted.

#load "Serialize.csx"

#r "System.Configuration"
#r "System.Data"
#r "Newtonsoft.Json"

using System.Configuration;
//using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Net;
using Newtonsoft.Json;
using System.Text;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Configuration;
//using Microsoft.Data.SqlClient

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{

 string connectionString = @"Data Source = #####################;

SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(connectionString);


// THIS LINE IS CAUSING THE ERROR
connStringBuilder.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

connectionString = connStringBuilder.ConnectionString;



string json =  " ";


using (SqlConnection conn = new SqlConnection(connectionString))
{
     conn.Open();

     SqlCommand cmd = new SqlCommand();
     SqlDataReader dataReader;

     cmd.CommandText = "SELECT * FROM Encrypted5";
     cmd.Connection = conn;

     dataReader = cmd.ExecuteReader();

     var r = Serialize(dataReader);

     json =  JsonConvert.SerializeObject(r, Formatting.Indented); 


     conn.Close();
}


return new HttpResponseMessage(HttpStatusCode.OK) 
{
    Content = new StringContent(json, Encoding.UTF8, "application/json")
};

}

Resulting Error Messages when enable:

2019-11-02T21:30:29.124 [Error] run.csx(29,19): error CS1061: 'SqlConnectionStringBuilder' does not contain a definition for 'ColumnEncryptionSetting' and no extension method 'ColumnEncryptionSetting' accepting a first argument of type 'SqlConnectionStringBuilder' could be found (are you missing a using directive or an assembly reference?)

2019-11-02T21:30:29.295 [Error] run.csx(29,45): error CS0103: The name 'SqlConnectionColumnEncryptionSetting' does not exist in the current context

According to my research, if you want to use the column encryption with .Net Core, you need to use the sdk ```Microsoft.Data.SqlClient```. For more details, please refer to https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/ — Jim Xu, Nov 05 '19 at 02:13

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

According to my research, if we want to use the setting ColumnEncryptionSetting to enable Always Encrypted with . net core, we should use the SDK Microsoft.Data.SqlClient. For more details, please refer to the blog and the issue. For example My code

 public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            string connectionString = @"";

            SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(connectionString);
            connStringBuilder.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

            string str = "";
            using (SqlConnection connection = new SqlConnection(connStringBuilder.ConnectionString))
            {
                connection.Open();
                string sql = "select * from dbo.Student";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            str += reader.GetString(0) + "  " + reader.GetString(1) + "\n";
                        }
                    }
                }
            }

            return
             (ActionResult)new OkObjectResult(str);
        }       
        
    }

If when you develop the function, you meet the error Microsoft.Data.SqlClient is not supported on this platform. Please add the following script to the csproj file. For more details, please refer to document

<Target Name="PostBuild" AfterTargets="PostBuildEvent">
  <Exec Command="copy $(OutDir)$(ProjectName).deps.json $(OutDir)bin\function.deps.json" />
</Target>
<Target Name="PostPublish" BeforeTargets="Publish">
  <Exec Command="copy $(PublishDir)$(ProjectName).deps.json $(PublishDir)bin\function.deps.json" />
  <Exec Command="move $(PublishDir)\runtimes $(PublishDir)\bin" />
</Target>

Besides, you also can use the ODBC driver. The ODBC driver supports Always Encrypted. For example

 using (var connection = new OdbcConnection(
                "Driver={ODBC Driver 17 for SQL Server};;Server={server name};Trusted_Connection=yes;ColumnEncryption=Enabled;Database={DBname};"))
            {
                using (var cmd = new OdbcCommand("", connection))
                {
                    connection.Open();
                    Console.WriteLine("Connected");
                    Console.WriteLine("SSN: " + Convert.ToString(cmd.ExecuteScalar()));
                    Console.ReadLine();
                    connection.Close();
                }
            }

For futher details, pleae refer to

Using Always Encrypted of SQL Server from .NET Core 2.1

https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=azuresqldb-current

Thanks a ton for this! – Kris Kawaguchi Nov 06 '19 at 17:33 — Kris Kawaguchi, Nov 06 '19 at 17:33

score 0 · Answer 2 · answered Nov 05 '19 at 06:16

I believe you must be using System.Data.SqlClient with version 4.5.1 which doesn't have the property for setting column encryption:

Either you can upgrade the framework for the updated one or you can simply append the setting in connection string itself:

string connectionString = "Data Source=server63; Initial Catalog=Clinic; Integrated Security=true; Column Encryption Setting=enabled";
SqlConnection connection = new SqlConnection(connectionString);

Additionally check the below doc for further reference:

Azure Function - System.Data.SqlClient is not supported on this platform

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.columnencryptionsetting?view=netframework-4.8&viewFallbackFrom=netframework-4.5.1

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-ver15

Hope it helps.