Select from db with AlwaysEncrypt and Server Principal

This is very common scenario without user’s authorization. We assume the Azure AppRegistration with used ClientID and ClientSecret. We also assume the KeyVault with read permissions for this AppRegistration.

Note that even with new Microsoft.Data.SqlClient package the connection string with KeyVault provider is not supported. So use Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider package. If this connection string is still fails, use the approach above:

using Azure.Identity;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using Microsoft.Extensions.Configuration;

// This is a console app, so build the Configuration manually.
var configBuilder = new ConfigurationBuilder()
                    .SetBasePath(AppContext.BaseDirectory)
                    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);

IConfiguration configuration = configBuilder.Build();
string? appClientID = configuration["clientID"];
string? clientSecret = configuration["clientSecret"];
string? tenantId = configuration["tenantId"];

var clientSecretCredential = new ClientSecretCredential(tenantId, appClientID, clientSecret);

SqlConnectionStringBuilder builder = new()
{
    InitialCatalog = "TlvBoxEncr",
    DataSource = "tlvbox.database.windows.net",
    Authentication = SqlAuthenticationMethod.ActiveDirectoryServicePrincipal,
    UserID = appClientID,
    Password = clientSecret,
    Encrypt = true,
    ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled,
};
string connString = builder.ConnectionString;

// Register the Azure Key Vault provider
SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new(clientSecretCredential);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(
    new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
    {
            { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
    });  

using SqlConnection conn = new(connString);

// From here the code is the same notwithstanding the connection  
conn.Open();

using SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from [dbo].[profile]";
SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    string firstName = reader.GetString(1);
    string address = reader.GetString(3);

    Console.WriteLine($"{firstName} {address}");
}

Also note that with AlwaysConnect inserts are possible only with parameters query:

// 'address' field is encrypted
cmd.CommandText = "insert into [dbo].[profile] (firstName, lastName, address) values('Jenifer', 'Smith', @address)";
cmd.Parameters.AddWithValue("@address", "zzzz");
int rowsAffected = cmd.ExecuteNonQuery();

As far as I know, KeyVault-specific parameters (such as , , etc.) are not directly supported in the connection string itself.