Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
dna495 opened this issue Dec 6, 2021 · 28 comments
Closed

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

dna495 opened this issue Dec 6, 2021 · 28 comments
Labels
🐛 Bug! Something isn't right !
Projects
SqlClient v5.1.2 hotfix SqlClient v4.0.4 hotfix SqlClient v3.1.4 hotfix

Comments

@dna495
Copy link

dna495 commented Dec 6, 2021 •
edited 

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
@johnnypham
Copy link
Contributor

Can you provide a repro?

@dna495
Copy link
Author

dna495 commented Dec 14, 2021 •
edited

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

@dna495
Copy link
Author

dna495 commented Dec 14, 2021 •
edited 

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}");
                }
            }
        }
    }
}

@dna495
Copy link
Author

dna495 commented Dec 27, 2021

@johnnypham thoughts?

dna495@f492b71

@dna495 dna495 mentioned this issue Dec 28, 2021
Closed
@johnnypham
Copy link
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.

@johnnypham
Copy link
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.

@dna495
Copy link
Author

dna495 commented Jan 5, 2022 •
edited

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

@johnnypham
Copy link
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.

@dna495
Copy link
Author

dna495 commented Jan 5, 2022

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

@johnnypham
Copy link
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;
``

@dna495
Copy link
Author

dna495 commented Jan 5, 2022

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

@johnnypham
Copy link
Contributor

johnnypham commented Jan 5, 2022 •
edited

Ok thanks, I'll try that next.

Update: able to repro now.

@johnnypham
Copy link
Contributor

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

@dna495
Copy link
Author

dna495 commented Jan 10, 2022 •
edited

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);
                }

@johnnypham
Copy link
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.

@dna495
Copy link
Author

dna495 commented Jan 11, 2022

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

@dna495
Copy link
Author

dna495 commented Mar 22, 2022

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

@johnnypham
Copy link
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.

@DavoudEshtehari DavoudEshtehari added the 🐛 Bug! Something isn't right ! label Mar 25, 2022
@tabi786
Copy link

tabi786 commented 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}
});
`

@tabi786
Copy link

tabi786 commented Mar 4, 2023 •
edited

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

@tabi786
Copy link

tabi786 commented Mar 4, 2023 via email •
edited

@tabi786
Copy link

tabi786 commented Mar 4, 2023 via email

@dna495
Copy link
Author

dna495 commented Mar 5, 2023

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

@tabi786
Copy link

tabi786 commented Mar 6, 2023 •
edited

Hi @dna495,

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

image

Regards.

Aftab Ahmad

@tabi786
Copy link

tabi786 commented 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

@JRahnama
Copy link
Member

JRahnama commented Mar 9, 2023

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

@David-Engel David-Engel mentioned this issue Apr 10, 2023
Merged
@David-Engel
Copy link
Contributor

David-Engel commented Apr 26, 2023 •
edited

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

@fepettersen fepettersen mentioned this issue Jun 12, 2023
Open
@DavoudEshtehari
Copy link
Member

Fixed by #1988

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
🐛 Bug! Something isn't right !
Projects
No open projects
SqlClient v3.1.4 hotfix
To do
SqlClient v4.0.4 hotfix
To do
SqlClient v5.1.2 hotfix
To do
Milestone
No milestone
Development

No branches or pull requests
6 participants
@tabi786 @dna495 @johnnypham @David-Engel @JRahnama @DavoudEshtehari