Skip to content

Intermittent enclave session error using Always Encrypted w/ secure enclaves. #1422

New issue
New issue
Closed
Closed
Intermittent enclave session error using Always Encrypted w/ secure enclaves.#1422

Description

@dna495
dna495
opened on Dec 6, 2021
Microsoft.Data.SqlClient.EnclaveDelegate+RetryableEnclaveQueryExecutionException: Internal Error. Enclave session is null during query execution. Enclave type is 'SGX' and enclaveAttestationUrl is 'https://xxxxxxxxxxxxx.eus.attest.azure.net'.
 ---> System.ArgumentException: Internal Error. Enclave session is null during query execution. Enclave type is 'SGX' and enclaveAttestationUrl is 'https://xxxxxxxxxxxxx.eus.attest.azure.net'.
   at Microsoft.Data.SqlClient.EnclaveDelegate.GetEnclaveSession(SqlConnectionAttestationProtocol attestationProtocol, String enclaveType, EnclaveSessionParameters enclaveSessionParameters, Boolean generateCustomData, SqlEnclaveSession& sqlEnclaveSession, Int64& counter, Byte[]& customData, Int32& customDataLength, Boolean throwIfNull)
   at Microsoft.Data.SqlClient.EnclaveDelegate.GenerateEnclavePackage(SqlConnectionAttestationProtocol attestationProtocol, ConcurrentDictionary`2 keysToBeSentToEnclave, String queryText, String enclaveType, EnclaveSessionParameters enclaveSessionParameters, SqlConnection connection, SqlCommand command)
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.EnclaveDelegate.GenerateEnclavePackage(SqlConnectionAttestationProtocol attestationProtocol, ConcurrentDictionary`2 keysToBeSentToEnclave, String queryText, String enclaveType, EnclaveSessionParameters enclaveSessionParameters, SqlConnection connection, SqlCommand command)
   at Microsoft.Data.SqlClient.SqlCommand.GenerateEnclavePackage()
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTdsWithTransparentParameterEncryption(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest, Task describeParameterEncryptionTask)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteReaderInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteReaderAsyncCallback(AsyncCallback callback, Object stateObject)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

To Reproduce
Behavior occurs intermittently with any command that references an encrypted column.

Further technical details
Microsoft.Data.SqlClient version: 4.0.0
.NET.Core 3.1
AzureSQL
Attestation Protocol: AAS

Screenshot - 2021-12-06T165845 683

image

Activity

  1. johnnypham commented on Dec 7, 2021

    @johnnypham
    johnnypham
    on Dec 7, 2021
    Contributor

    Can you provide a repro?

  2. dna495 commented on Dec 14, 2021

    @dna495
    dna495
    on Dec 14, 2021
    Author

    I'm pretty sure it happens when the EnclaveSessionCache expires after 8 hours.

    Update the EnclaveSessionCache to 1 minute instead of 8 hours in CreateSession

    Run an async query that requires enclave and then come back after a minute and you'll get it. Let me know what else you need from me

  3. dna495 commented on Dec 14, 2021

    @dna495
    dna495
    on Dec 14, 2021
    Author
    using Azure.Identity;
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;

namespace EncryptionTest
{

    /*  Table Create  (Need to add column encryption key to script (***Need To Add Column Encryption Key Here ***)
        CREATE TABLE [dbo].[EncrytptionTest]
        (
	        [Id] [int] IDENTITY(1,1) NOT NULL,
	        [EncryptedColumn] [char](1)  COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [***Need To Add Column Encryption Key Here ***], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,	
            PRIMARY KEY CLUSTERED (	[Id] ASC)
        )
        GO

        DECLARE @male char(1) = 'M'
        INSERT INTO [EncrytptionTest]([EncryptedColumn])VALUES(@male)
        GO

        DECLARE @female char(1) = 'F'
        INSERT INTO [EncrytptionTest]([EncryptedColumn])VALUES(@female)
        GO
    */

    /* Update EnclaveSessionCache to following or wait 8 hours(update program below to run that long)
     *   
     *  // Creates a new SqlEnclaveSession and adds it to the cache
        internal SqlEnclaveSession CreateSession(EnclaveSessionParameters enclaveSessionParameters, byte[] sharedSecret, long sessionId, out long counter)
        {
            string cacheKey = GenerateCacheKey(enclaveSessionParameters);
            SqlEnclaveSession enclaveSession = null;
            lock (enclaveCacheLock)
            {
                enclaveSession = new SqlEnclaveSession(sharedSecret, sessionId);
                enclaveMemoryCache.Add(cacheKey, enclaveSession, DateTime.UtcNow.AddMinutes(1));
                counter = Interlocked.Increment(ref _counter);
            }

            return enclaveSession;
        }
     */
    class Program
    {
        static readonly string server = "";
        static readonly string user = "";
        static readonly string password = "";
        static readonly string attestationUrl = "";
        static readonly string intialCatalog="";
        static readonly string s_connectionString = $"Server={server};Initial Catalog={intialCatalog};Connection Timeout=30;UID={user};PWD={password}; Column Encryption Setting = Enabled;Attestation Protocol = AAS; Enclave Attestation Url = {attestationUrl};";

        static readonly Random _random = new Random();
        static async Task Main(string[] args)
        {
            SqlColumnEncryptionAzureKeyVaultProvider akvProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new DefaultAzureCredential());
            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(customProviders: new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>(capacity: 1, comparer: StringComparer.OrdinalIgnoreCase)
            {
                    { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, akvProvider}
            });


            for (int i = 0; i < 11; i++)
            {
                try
                {
                    using (SqlConnection sqlConnection = new SqlConnection(s_connectionString))
                    {
                        if (sqlConnection.State != ConnectionState.Open)
                        {
                            sqlConnection.Open();
                        }
                        using (SqlCommand sqlCommand = new SqlCommand($"SELECT ID FROM [EncrytptionTest] WHERE EncryptedColumn=@encryptedValue", sqlConnection))
                        {
                            int age = _random.Next(1, 120);
                            string encryptedValue = age % 2 == 0 ? "M" : "F";
                            SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"encryptedValue", encryptedValue);
                            customerFirstParam.Direction = ParameterDirection.Input;
                            customerFirstParam.DbType = DbType.AnsiStringFixedLength;
                            customerFirstParam.Size = 1;

                            using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
                            {
                                while (sqlDataReader.Read())
                                {
                                    Debug.Print($"Param: {encryptedValue} - Result: {sqlDataReader[0]} - Time: {DateTime.Now}");
                                }
                            }
                        }
                    }
                    Thread.Sleep(6000);
                }
                catch (Exception ex)
                {
                    Debug.Print($"Errored at: {DateTime.Now} - {ex.Message}");
                }
            }
        }
    }
}

  4. dna495 commented on Dec 27, 2021

    @dna495
    dna495
    on Dec 27, 2021
    Author

    @johnnypham thoughts?

    dna495@f492b71

  5. johnnypham commented on Jan 4, 2022

    @johnnypham
    johnnypham
    on Jan 4, 2022
    Contributor

    Sorry, I've been away. Thanks for the repro, I'll give it a try asap. It seems odd that the enclave session doesn't automatically refresh after it expires.

  6. johnnypham commented on Jan 5, 2022

    @johnnypham
    johnnypham
    on Jan 5, 2022
    Contributor

    I still have yet to go through the enclave session caching implementation but I wanted to mention that the repro doesn't work for me, even if I change the enclave cache time to 10 seconds.

  7. dna495 commented on Jan 5, 2022

    @dna495
    dna495
    on Jan 5, 2022
    Author

    Not working as in not getting the exception (after updating the CreateSession method in EnclaveSessionCache)?

  8. johnnypham commented on Jan 5, 2022

    @johnnypham
    johnnypham
    on Jan 5, 2022
    Contributor

    Yes. I'm using my own table but that shouldn't make a difference. Everything else is the same. Tried with both 1 minute and 10 seconds.

  9. dna495 commented on Jan 5, 2022

    @dna495
    dna495
    on Jan 5, 2022
    Author

    Do you mind sending me your table/query, so that I can try?

  10. johnnypham commented on Jan 5, 2022

    @johnnypham
    johnnypham
    on Jan 5, 2022
    Contributor

    It's the table created in this article. You can skip the part where you create the CMK/CEK and just use your existing keys.

    Query is:

    SELECT [SSN], [FirstName], [LastName], [Salary]  
FROM [HR].[Employees] 
WHERE [SSN] LIKE @SSNPattern AND [Salary] > @MinSalary;
``

  11. dna495 commented on Jan 5, 2022

    @dna495
    dna495
    on Jan 5, 2022
    Author

    I think the issue is when you use AzureSQL and AAS. The cache refreshes correctly when I connect via SQLServer and HGS.

  12. johnnypham commented on Jan 5, 2022

    @johnnypham
    johnnypham
    on Jan 5, 2022
    Contributor

    Ok thanks, I'll try that next.

    Update: able to repro now.

  13. johnnypham commented on Jan 10, 2022

    @johnnypham
    johnnypham
    on Jan 10, 2022
    Contributor

    I only see the exception when using ExecuteReaderAsync. ExecuteReader seems to work fine. If possible, can you verify this in your environment?

  14. dna495 commented on Jan 10, 2022

    @dna495
    dna495
    on Jan 10, 2022
    Author

    Verified. RunExecuteReader throws when it's async and retries when it's not. FWIW we have been running dna495@f492b71 mentioned above for a couple weeks now w/ no issues.

                    catch (EnclaveDelegate.RetryableEnclaveQueryExecutionException)
                {
                    if (inRetry || isAsync)
                    {
                        throw;
                    }

                    // Retry if the command failed with appropriate error.
                    // First invalidate the entry from the cache, so that we refresh our encryption MD.
                    SqlQueryMetadataCache.GetInstance().InvalidateCacheEntry(this);

                    if (ShouldUseEnclaveBasedWorkflow && this.enclavePackage != null)
                    {
                    EnclaveSessionParameters enclaveSessionParameters = new EnclaveSessionParameters(this._activeConnection.DataSource, this._activeConnection.EnclaveAttestationUrl, this._activeConnection.Database);
                    EnclaveDelegate.Instance.InvalidateEnclaveSession(this._activeConnection.AttestationProtocol, this._activeConnection.Parser.EnclaveType,
                            enclaveSessionParameters, this.enclavePackage.EnclaveSession);
                    }

                    return RunExecuteReader(cmdBehavior, runBehavior, returnStream, null, TdsParserStaticMethods.GetRemainingTimeout(timeout, firstAttemptStart), out task, out usedCache, isAsync, inRetry: true, method: method);
                }

  15. johnnypham commented on Jan 11, 2022

    @johnnypham
    johnnypham
    on Jan 11, 2022
    Contributor

    Yeah, that seems like the likely solution. But I'm trying to figure out why it only fails with AAS and not HGS. The old async code is making it difficult.

  16. dna495 commented on Jan 11, 2022

    @dna495
    dna495
    on Jan 11, 2022
    Author

    Right right. Let me know if there's anything else I can do to help.

  17. dna495 commented on Mar 22, 2022

    @dna495
    dna495
    on Mar 22, 2022
    Author

    @johnnypham any update, or anything you'd like me to do to help?

  18. johnnypham commented on Mar 23, 2022

    @johnnypham
    johnnypham
    on Mar 23, 2022
    Contributor

    I haven't had the time to work on this. The last thing I figured out is that the client does not request encryption metadata (which would set up a new enclave session) when the current session expires. I think it would be relatively simple to check if the session has expired and get the metadata but it would require some testing but I don't know when I'll have time.

  19. DavoudEshtehari
    added
    🐛 Bug!
    on Mar 25, 2022

  20. tabi786 commented on Mar 4, 2023

    @tabi786
    tabi786
    on Mar 4, 2023

    Hi, I am using it .NET Core API hosted in Azure App Service. My Api failes to search on encrypted columns after almost 8 hours. It remains down almost 5-30 minutes before it automatically comes up. I have included following nuget packages in the project

    1. Microsoft.Data.SqlClient
    2. Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider
    3. Microsoft.IdentityModel.Clients.ActiveDirectory

    I am using this code in Startup.cs file. A quick response in this regard will be highly appreciated.

    `var tenant = "tenant";
    var clientApplicationId = "AADapplicationid";
    var clientSecret = "ClientSecret";

    var clientSecretCredential = new ClientSecretCredential(tenant, clientApplicationId, clientSecret);
    SqlColumnEncryptionAzureKeyVaultProvider akvProvider = new SqlColumnEncryptionAzureKeyVaultProvider(clientSecretCredential);

    SqlColumnEncryptionAzureKeyVaultProvider(defaultCredentials);

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

  21. tabi786 commented on Mar 4, 2023

    @tabi786
    tabi786
    on Mar 4, 2023

    I am using Azure Sql with latest sql compatibility level. I am actually using Core Api with odata with entity Frameworks, https://learn.microsoft.com/en-us/odata/webapi/first-odata-api

    I'll also share in next post. https://learn.microsoft.com/en-us/odata/webapi/first-odata-api

  22. tabi786 commented on Mar 4, 2023

    @tabi786
    tabi786
    on Mar 4, 2023 via email

  23. tabi786 commented on Mar 4, 2023

    @tabi786
    tabi786
    on Mar 4, 2023 via email

  24. dna495 commented on Mar 5, 2023

    @dna495
    dna495
    on Mar 5, 2023
    Author

    @tabi786 looks like same bug. FWIW we have been running the change dna495@f492b71 since 12/2022 w/o issue.

  25. tabi786 commented on Mar 6, 2023

    @tabi786
    tabi786
    on Mar 6, 2023

    Hi @dna495,

    I have got the code but unfortunately my Azure Encryption code has stopped working now. Any Idea.

    image

    Regards.

    Aftab Ahmad

  26. tabi786 commented on Mar 9, 2023

    @tabi786
    tabi786
    on Mar 9, 2023

    Hi @dna495,

    I have got the code but unfortunately my Azure Encryption code has stopped working now. Any Idea.

    image

    Regards.

    Aftab Ahmad

    HI @dna495 @johnnypham @DavoudEshtehari @radical,

    Can anyone of you guys help? We have the issue in Production Environment and it is blocking completely. Is there any other way to get help, for example from Microsoft support by creating a Support Ticket?

    Your quick response in this regard will be highly appreciated.

    Regards.

    Aftab Ahmad

  27. JRahnama commented on Mar 9, 2023

    @JRahnama
    JRahnama
    on Mar 9, 2023
    Contributor

    @tabi786 in case of any emergency support request contact Microsoft support center.

  28. David-Engel commented on Apr 26, 2023

    @David-Engel
    David-Engel
    on Apr 26, 2023
    Contributor

    The fix for this issue has been merged and released in 5.2.0-preview1.

  29. DavoudEshtehari commented on Oct 25, 2023

    @DavoudEshtehari
    DavoudEshtehari
    on Oct 25, 2023
    Contributor

    Fixed by #1988

  30. DavoudEshtehari
    closed this as completedon Oct 25, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions