Sql Always Encrypted 사용하기 Part4

2023.12.22 - [Entity Framework Core] - Sql Always Encrypted 사용하기 Part4

2023.12.20 - [Entity Framework Core] - Sql Always Encrypted 사용하기 Part3

2023.11.22 - [Entity Framework Core] - Sql Always Encrypted 사용하기 Part2

2023.11.20 - [Entity Framework Core] - Sql Always Encrypted 사용하기 Part1

 

간단한 Console App을 이용해서 데이터 조회하는 부분만 우선 다루도록 하겠습니다.

 

1. Nuget packages

앱에서 사용하는 패키지들은 다음과 같습니다.

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider" Version="3.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.25" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.25">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="5.3.0" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.3" />
  </ItemGroup>

. 앱이 .Net 6 기반이라 다른 패키지들도 6.0 최신 버전에 맞추어져있습니다.

. 현재 문제는 아래 패키지에 있습니다.

Microsoft.IdentityModel.Clients.ActiveDirectory 패키지가 더 이상 사용할 수 없는 패키지로 표시되며, Microsoft.Identity.Client로 변경하라는 내용입니다.

그런데, 인터넷에서 검색을 했을 때 대부분 이 Nuget을 사용하는 예제만 있고, Microsoft.Identity.Client를 사용하는 예제가 부족해서 변경을 하는데 시간이 걸릴 듯합니다;;

거기다 더해, 대부분 Ado를 이용하는 예제이고 EntityFramework를 사용하는 예제가 없어서 어려움을 겪고 있습니다.

 

정확하게 필요한 예제는

• EntityFramework 6.0
• Always Encrypted
• Master Key가 Azure Key Vault로 되어 있어야함
• Microsoft.Identity.Client nuget 을 이용
• DbContext 혹은 Connection 정보 등에 AKV를 이용해서 인증 받은 토큰을 넘겨줘야함

2. Azure Key Vault Url 구하기

AKVUrl은 아래 이미지와 같이 Keyw -> 컬럼 암호화에 사용된 마스터 키(여기서는 Always-Encrypted-Auto1)를 선택합니다.

선택하면 상세 페이지로 전환되고 Key Identifier 오른쪽 복사 아이콘을 눌러서 Url을 복사합니다.

Url은 다음과 같은 형식입니다.

예) https://kakisamplekey.vault.azure.net/keys/Always-Encrypted-Auto1/219cxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

3. 전체 콘솔 소스

using Azure.Core;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.IdentityModel.Tokens.Jwt;

public class Program
{
    // ********* Provide details here ***********
    /// <summary>
    /// Azure Key Vault의 Key값에 해당하는 Url을 입력합니다.
    /// 예) https://kakisamplekey.vault.azure.net/keys/Always-Encrypted-Auto1/219cxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    /// </summary>
    private static readonly string s_akvUrl = "AKV Url을 입력하세요";
    /// <summary>
    /// Azure App 등록에 등록된 ClientId를 입력합니다.
    /// </summary>
    private static readonly string s_clientId = "ClientId값을 입력하세요";
    /// <summary>
    /// Azure App 등록에 등록된 Client의 Secret를 입력합니다.
    /// </summary>
    private static readonly string s_clientSecret = "Secret값을 입력하세요";
    /// <summary>
    /// Database 연결 문자열을 입력합니다.
    /// Column Encryption Setting=enabled 를 사용해야 암호화된 컬럼의 내용을 읽을 수 있습니다.
    /// </summary>

    //암호화 컬럼을 읽을 수 있는 연결 문자열
    //private static readonly string s_connectionString = "Data Source=localhost;Database=ContosoUniversity;Integrated Security=true;TrustServerCertificate=true;Column Encryption Setting=enabled";
    //암호화 컬럼을 읽을 수 없는 연결 문자열
    private static readonly string s_connectionString = "Data Source=localhost;Database=ContosoUniversity;Integrated Security=true;TrustServerCertificate=true";
    // ******************************************

    public static void Main()
    {
        // Initialize AKV provider
        SqlColumnEncryptionAzureKeyVaultProvider akvProvider = new(new LegacyAuthCallbackTokenCredential());

        // Register AKV provider
        SqlConnection.RegisterColumnEncryptionKeyStoreProviders(
            customProviders: new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(
                capacity: 1,
                comparer: StringComparer.OrdinalIgnoreCase)
            {
                {
                    SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, akvProvider
                }
            });
        Console.WriteLine("AKV provider Registered");

        // Create connection to database
        using SqlConnection sqlConnection = new(s_connectionString);
        try
        {
            sqlConnection.Open();
            // Read data from table
            SelectData(sqlConnection);
            Console.WriteLine("Data validated successfully.");
        }
        catch (Exception)
        {
            throw;
        }
    }
    /// <summary>
    /// 테이블에서 데이터 읽어 오기
    /// </summary>
    /// <param name="sqlConnection"></param>
    private static void SelectData(SqlConnection sqlConnection)
    {
        // Test INPUT parameter on an encrypted parameter
        using SqlCommand sqlCommand = new($"SELECT * FROM Employees", sqlConnection);
        //SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
        //customerFirstParam.Direction = System.Data.ParameterDirection.Input;
        //customerFirstParam.ForceColumnEncryption = true;

        using SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
        ValidateResultSet(sqlDataReader);
    }
    /// <summary>
    /// 읽어온 데이터 출력하기
    /// </summary>
    /// <param name="sqlDataReader"></param>
    private static void ValidateResultSet(SqlDataReader sqlDataReader)
    {
        Console.WriteLine(" * Row available: " + sqlDataReader.HasRows);
        while (sqlDataReader.Read())
        {
            Console.WriteLine($"{sqlDataReader[0]}  {sqlDataReader[1]}  {sqlDataReader[2]}  {sqlDataReader[3]}  {sqlDataReader[4]}");
        }
    }
    /// <summary>
    /// AKV 인증
    /// </summary>
    private class LegacyAuthCallbackTokenCredential : TokenCredential
    {
        private string _authority = "";
        private string _resource = "";
        private string _akvUrl = "";

        [Obsolete]
        public override AccessToken GetToken(TokenRequestContext requestContext, CancellationToken cancellationToken)
        {
            return AcquireTokenAsync().GetAwaiter().GetResult();
        }

        [Obsolete]
        public override async ValueTask<AccessToken> GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
        {
            return await AcquireTokenAsync();
        }

        [Obsolete]
        private async Task<AccessToken> AcquireTokenAsync()
        {
            // Added to reduce HttpClient calls.
            // For multi-user support, a better design can be implemented as needed.
            if (_akvUrl != s_akvUrl)
            {
                using (HttpClient httpClient = new())
                {
                    HttpResponseMessage response = await httpClient.GetAsync(s_akvUrl);
                    string? challenge = response?.Headers.WwwAuthenticate.FirstOrDefault()?.ToString();
                    string trimmedChallenge = ValidateChallenge(challenge);
                    string[] pairs = trimmedChallenge.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);

                    if (pairs != null && pairs.Length > 0)
                    {
                        for (int i = 0; i < pairs.Length; i++)
                        {
                            string[]? pair = pairs[i]?.Split('=');

                            if (pair.Length == 2)
                            {
                                string? key = pair[0]?.Trim().Trim(new char[] { '\"' });
                                string? value = pair[1]?.Trim().Trim(new char[] { '\"' });

                                if (!string.IsNullOrEmpty(key))
                                {
                                    if (key.Equals("authorization", StringComparison.InvariantCultureIgnoreCase))
                                    {
                                        _authority = value;
                                    }
                                    else if (key.Equals("resource", StringComparison.InvariantCultureIgnoreCase))
                                    {
                                        _resource = value;
                                    }
                                }
                            }
                        }
                    }
                }
                _akvUrl = s_akvUrl;
            }

            string strAccessToken = await AzureActiveDirectoryAuthenticationCallback(_authority, _resource);
            DateTime expiryTime = InterceptAccessTokenForExpiry(strAccessToken);
            return new AccessToken(strAccessToken, new DateTimeOffset(expiryTime));
        }
        /// <summary>
        /// 엑세스토큰 유효기간
        /// </summary>
        /// <param name="accessToken"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="FormatException"></exception>
        private DateTime InterceptAccessTokenForExpiry(string accessToken)
        {
            if (null == accessToken)
            {
                throw new ArgumentNullException(accessToken);
            }

            JwtSecurityTokenHandler jwtHandler = new();
            string jwtOutput = string.Empty;

            // Check Token Format
            if (!jwtHandler.CanReadToken(accessToken))
            {
                throw new FormatException(accessToken);
            }

            JwtSecurityToken token = jwtHandler.ReadJwtToken(accessToken);

            // Re-serialize the Token Headers to just Key and Values
            string jwtHeader = JsonConvert.SerializeObject(token.Header.Select(h => new { h.Key, h.Value }));
            jwtOutput = $"{{\r\n\"Header\":\r\n{JToken.Parse(jwtHeader)},";

            // Re-serialize the Token Claims to just Type and Values
            string jwtPayload = JsonConvert.SerializeObject(token.Claims.Select(c => new { c.Type, c.Value }));
            jwtOutput += $"\r\n\"Payload\":\r\n{JToken.Parse(jwtPayload)}\r\n}}";

            // Output the whole thing to pretty JSON object formatted.
            string jToken = JToken.Parse(jwtOutput).ToString(Newtonsoft.Json.Formatting.Indented);
            JToken payload = JObject.Parse(jToken).GetValue("Payload");

            return new DateTime(1970, 1, 1).AddSeconds((long)payload[4]["Value"]);
        }

        private static string ValidateChallenge(string challenge)
        {
            string Bearer = "Bearer ";
            if (string.IsNullOrEmpty(challenge))
            {
                throw new ArgumentNullException(nameof(challenge));
            }

            string trimmedChallenge = challenge.Trim();

            return !trimmedChallenge.StartsWith(Bearer)
                ? throw new ArgumentException("Challenge is not Bearer", nameof(challenge))
                : trimmedChallenge[Bearer.Length..];
        }

        /// <summary>
        /// Legacy implementation of Authentication Callback, used by Azure Key Vault provider 1.0.
        /// This can be leveraged to support multi-user authentication support in the same Azure Key Vault Provider.
        /// </summary>
        /// <param name="authority">Authorization URL</param>
        /// <param name="resource">Resource</param>
        /// <returns></returns>
        [Obsolete]
        public static async Task<string> AzureActiveDirectoryAuthenticationCallback(string authority, string resource)
        {
            AuthenticationContext authContext = new(authority);
            ClientCredential clientCred = new(s_clientId, s_clientSecret);
            AuthenticationResult result = await authContext.AcquireTokenAsync(resource, clientCred);
            return result == null
                ? throw new InvalidOperationException($"Failed to retrieve an access token for {resource}")
                : result.AccessToken;
        }
    }

}

4. 결과 화면

암호화 컬럼의 데이터를 조회하지 못한 결과 화면

암호화 컬럼의 데이터를 정상 조회한 결과 화면

5. 오류 메시지

아래 오류 메시지는 잘못된 clientId, secret, akvUrl을 입력한 경우 발생할 수 있습니다.

 

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Failed to decrypt column 'SSN'.
Failed to decrypt a column encryption key using key store provider: 'AZURE_KEY_VAULT'. Verify the properties of the column encryption key and its column master key in your database. The last 10 bytes of the encrypted column encryption key are: '3B-06-2E-FC-B4-8A-D6-1F-6A-0A'.
Service request failed.
Status: 403 (Forbidden)

Content:
{"error":{"code":"Forbidden","message":"The user, group or application 'appid=e3d23b40-2c53-4b37-a101-84e7df54f69e;oid=572dd061-ec70-4408-98e3-e1db6e6e136e;iss=https://sts.windows.net/b1ad2c09-785a-4dcd-896c-75de6388ffcd/' does not have keys get permission on key vault 'kakisamplekey;location=eastus'. For help resolving this issue, please see https://go.microsoft.com/fwlink/?linkid=2125287","innererror":{"code":"AccessDenied"}}}

Headers:
Cache-Control: no-cache
Pragma: no-cache
x-ms-keyvault-region: eastus
x-ms-client-request-id: 88191d0c-8f81-432a-acea-94d7ce2a4b58
x-ms-request-id: REDACTED
x-ms-keyvault-service-version: 1.9.1145.1
x-ms-keyvault-network-info: conn_type=Ipv4;addr=112.216.161.106;act_addr_fam=InterNetwork;
X-Content-Type-Options: REDACTED
Strict-Transport-Security: REDACTED
Date: Thu, 21 Dec 2023 09:19:19 GMT
Content-Length: 432
Content-Type: application/json; charset=utf-8
Expires: -1

  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName, SqlCommand command)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly)
   at Microsoft.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at Microsoft.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at Program.ValidateResultSet(SqlDataReader sqlDataReader) in C:\Users\kaki104\source\repos\ContosoPets\KakiEfSampleWithAlwaysEncrypted\Program.cs:line 87
   at Program.SelectData(SqlConnection sqlConnection) in C:\Users\kaki104\source\repos\ContosoPets\KakiEfSampleWithAlwaysEncrypted\Program.cs:line 76
   at Program.Main() in C:\Users\kaki104\source\repos\ContosoPets\KakiEfSampleWithAlwaysEncrypted\Program.cs:line 55

  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
RequestFailedException: Service request failed.
Status: 403 (Forbidden)

Content:
{"error":{"code":"Forbidden","message":"The user, group or application 'appid=e3d23b40-2c53-4b37-a101-84e7df54f69e;oid=572dd061-ec70-4408-98e3-e1db6e6e136e;iss=https://sts.windows.net/b1ad2c09-785a-4dcd-896c-75de6388ffcd/' does not have keys get permission on key vault 'kakisamplekey;location=eastus'. For help resolving this issue, please see https://go.microsoft.com/fwlink/?linkid=2125287","innererror":{"code":"AccessDenied"}}}

Headers:
Cache-Control: no-cache
Pragma: no-cache
x-ms-keyvault-region: eastus
x-ms-client-request-id: 88191d0c-8f81-432a-acea-94d7ce2a4b58
x-ms-request-id: REDACTED
x-ms-keyvault-service-version: 1.9.1145.1
x-ms-keyvault-network-info: conn_type=Ipv4;addr=112.216.161.106;act_addr_fam=InterNetwork;
X-Content-Type-Options: REDACTED
Strict-Transport-Security: REDACTED
Date: Thu, 21 Dec 2023 09:19:19 GMT
Content-Length: 432
Content-Type: application/json; charset=utf-8
Expires: -1

 

6. 소스

kaki104/ContosoPets: Entity Framework Core 시작 sample (github.com)

GitHub - kaki104/ContosoPets: Entity Framework Core 시작 sample