Closed dna495 closed 10 months ago
Can you provide a repro?
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
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}");
}
}
}
}
}
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.
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.
Not working as in not getting the exception (after updating the CreateSession method in EnclaveSessionCache)?
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.
Do you mind sending me your table/query, so that I can try?
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;
``
I think the issue is when you use AzureSQL and AAS. The cache refreshes correctly when I connect via SQLServer and HGS.
Ok thanks, I'll try that next.
Update: able to repro now.
I only see the exception when using ExecuteReaderAsync
. ExecuteReader
seems to work fine. If possible, can you verify this in your environment?
Verified. RunExecuteReader throws when it's async and retries when it's not. FWIW we have been running https://github.com/dna495/issue-1422-EncalveSessionError/commit/f492b71be5f3df67c4bdc6eb51c46fc57052cb6e 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);
}
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.
Right right. Let me know if there's anything else I can do to help.
@johnnypham any update, or anything you'd like me to do to help?
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.
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
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} }); `
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
Hi Dave,
I have put the repos at tabi786/Test.WebAPI (github.com)https://github.com/tabi786/Test.WebAPI
If I restart Azure Web App, then it starts working again.
Regards.
Aftab Ahmad
From: Dave @.> Sent: 4. marts 2023 15:10 To: dotnet/SqlClient @.> Cc: tabi786 @.>; Mention @.> Subject: Re: [dotnet/SqlClient] Intermittent enclave session error using Always Encrypted w/ secure enclaves. (Issue #1422)
@tabi786https://github.com/tabi786 are you using SQL Server or Azure SQL, and can you please provide repro?
— Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/1422#issuecomment-1454752360, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AC3L7HBVBWSVN5Y2TZH3ZTLW2NEMXANCNFSM5JPXCWRQ. You are receiving this because you were mentioned.Message ID: @.**@.>>
Hi again,
I have the exact error message. Now it is happening very quickly, after every second hour.
Microsoft.Data.SqlClient.EnclaveDelegate+RetryableEnclaveQueryExecutionException: Internal Error. Enclave session is null during query execution. Enclave type is 'SGX' and enclaveAttestationUrl is 'https://xxxxxxxx.neu.attest.azure.net'.
---> System.ArgumentException: Internal Error. Enclave session is null during query execution. Enclave type is 'SGX' and enclaveAttestationUrl is 'https://xxxxxxxxxxx.neu.attest.azure.net'.
at Microsoft.Data.SqlClient.EnclaveDelegate.GenerateEnclavePackage(SqlConnectionAttestationProtocol attestationProtocol, ConcurrentDictionary2 keysToBeSentToEnclave, String queryText, String enclaveType, EnclaveSessionParameters enclaveSessionParameters, SqlConnection connection, SqlCommand command) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task
1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable
1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func
4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at System.Text.Json.Serialization.Converters.IAsyncEnumerableOfTConverter
2.OnWriteResume(Utf8JsonWriter writer, TAsyncEnumerable value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonCollectionConverter2.OnTryWrite(Utf8JsonWriter writer, TCollection value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter
1.TryWrite(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.Serialization.JsonConverter1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state) at System.Text.Json.Serialization.JsonConverter
1.WriteCoreAsObject(Utf8JsonWriter writer, Object value, JsonSerializerOptions options, WriteStack& state)
at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, JsonTypeInfo jsonTypeInfo, WriteStack& state)
at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)
at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)
at System.Text.Json.JsonSerializer.WriteStreamAsync[TValue](Stream utf8Json, TValue value, JsonTypeInfo jsonTypeInfo, CancellationToken cancellationToken)
at Microsoft.AspNetCore.Mvc.Formatters.SystemTextJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.
Accept: / Accept-Encoding: gzip, deflate, br Authorization: Bearer XXXX Cache-Control: no-cache Content-Length: 0 Host: api.test.ltd Max-Forwards: 10 User-Agent: PostmanRuntime/7.31.1 Postman-Token: 984262a3-6fe1-4e52-b751-513ebb2aeb5b X-ARR-LOG-ID: b3af3b85-cba7-4fea-aba4-1f5dda4b46bb CLIENT-IP: 54.86.50.139:10241 DISGUISED-HOST: api.test.ltd X-SITE-DEPLOYMENT-ID: testprodapi WAS-DEFAULT-HOSTNAME: testprodapi.azurewebsites.net X-Forwarded-Proto: https X-AppService-Proto: https X-ARR-SSL: 8192|256|CN=Sectigo RSA Domain Validation Secure Server CA, O=Sectigo Limited, L=Salford, S=Greater Manchester, C=GB|CN=*.test.ltd X-Forwarded-TlsVersion: 1.2 X-Forwarded-For: 54.86.50.139:10241 X-Original-URL: @.**@*.**@*.**@.%27%20and%20Usertype%20eq%202&$select=Id&$top=1&$orderby=CreatedOn%20desc> X-WAWS-Unencoded-URL: @.**@*.**@*.**@.%27%20and%20Usertype%20eq%202&$select=Id&$top=1&$orderby=CreatedOn%20desc> X-MS-CLIENT-PRINCIPAL-NAME: Thomas Peter X-MS-CLIENT-PRINCIPAL-ID: d679531a-be60-424c-a3bc-11bb1dd42c72 X-MS-CLIENT-PRINCIPAL-IDP: aad X-MS-CLIENT-PRINCIPAL: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Regards. Aftab Ahmad From: Aftab Ahmad Sent: 4. marts 2023 22:44 To: dotnet/SqlClient @.>; dotnet/SqlClient @.> Cc: Mention @.>; Dennis Rohde @.> Subject: RE: [dotnet/SqlClient] Intermittent enclave session error using Always Encrypted w/ secure enclaves. (Issue #1422)
Hi Dave,
I have put the repos at tabi786/Test.WebAPI (github.com)https://github.com/tabi786/Test.WebAPI
Regards.
Aftab Ahmad
From: Dave @.**@.>> Sent: 4. marts 2023 15:10 To: dotnet/SqlClient @.**@.>> Cc: tabi786 @.**@.>>; Mention @.**@.>> Subject: Re: [dotnet/SqlClient] Intermittent enclave session error using Always Encrypted w/ secure enclaves. (Issue #1422)
@tabi786https://github.com/tabi786 are you using SQL Server or Azure SQL, and can you please provide repro?
— Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/1422#issuecomment-1454752360, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AC3L7HBVBWSVN5Y2TZH3ZTLW2NEMXANCNFSM5JPXCWRQ. You are receiving this because you were mentioned.Message ID: @.**@.>>
@tabi786 looks like same bug. FWIW we have been running the change https://github.com/dna495/issue-1422-EncalveSessionError/commit/f492b71be5f3df67c4bdc6eb51c46fc57052cb6e since 12/2022 w/o issue.
Hi @dna495,
I have got the code but unfortunately my Azure Encryption code has stopped working now. Any Idea.
Regards.
Aftab Ahmad
Hi @dna495,
I have got the code but unfortunately my Azure Encryption code has stopped working now. Any Idea.
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
@tabi786 in case of any emergency support request contact Microsoft support center.
The fix for this issue has been merged and released in 5.2.0-preview1.
Fixed by #1988
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