Sqlclient: “Internal connection fatal error” from TdsParser when calling native compiled stored procedure in SQL Server 2019

[Genmutant]

Describe the bug

I am having huge problems with the current SQL Server 2019 (15.0.2070.41) and native compiled stored procedures when calling them from the Microsoft.Data.SqlClient (1.1.0 and lower).

It is working when either using SQL Server 2017 or System.Data.SqlClient, or just not using a native compiled procedure.

Apparently the TdsParser is receiving data it doesn't expect from the SQL Server connection and is throwing an exception during the parsing.

Exception message: System.InvalidOperationException: Internal connection fatal error.
Stack trace: at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader()

To reproduce

```c#
using System;
using System.Data;
using Microsoft.Data.SqlClient;

namespace SqlDbTest
{
class Program
{
static void Main()
{
const string connString = "Server=localhost;Database=TestDb;Trusted_Connection=True";
const string prod = @"CREATE OR ALTER PROCEDURE dbo.NativeProdForTesting WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT NULL AS testcol
RETURN 0
END";

        using (var con = new SqlConnection(connString))
        {
            using var createSp = con.CreateCommand();
            createSp.CommandText = prod;
            con.Open();
            createSp.ExecuteNonQuery();
        }

        try
        {
            using var con = new SqlConnection(connString);
            using var cmd = con.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[dbo].[NativeProdForTesting]";
            cmd.Connection.Open();
            using var reader = cmd.ExecuteReader();
            Console.WriteLine("NO ERROR!");
        }
        catch (InvalidOperationException ex)
        {
            Console.WriteLine(ex);
        }
    }
}

}
```

Expected behavior

No exception and the reader should provide the result of the query.

Further technical details

Microsoft.Data.SqlClient version: 1.1.0 and lower
.NET target: Core 3.1, Framework 4.7.2
SQL Server version: SQL Server 2019 (15.0.2070.41)
Operating system: Windows 10, Windows Server 2016 Standard

Additional context
I have the same problem with JDBC and it's TDSParser (7.4.1.jre8), see here. I also posted the problem on Stackoverflow

[cheenamalhotra]

Hi @Genmutant

I'm able to reproduce issue with Microsoft.Data.SqlClient and am looking into it.
Thanks for reporting this with us.

[Genmutant]

The token which can't be parsed seems to be the same as the one which JDBC has a problem with, and that also doesn't get recogniced by Wireshark: The 0xA3 token.

[Genmutant]

Introduced in TDS 7.4, the DATACLASSIFICATION token SHOULD<39> describe the data
classification of the query result set.
Token Stream Comments:
 The token value is 0xA3.
 This token only be sent by the server if the client sends a DATACLASSIFICATION FeatureExt in the
Login message and the server responds with a DATACLASSIFICATION FeatureExtAck. When this
token is used, the token is sent by the server in response to every SQLBatch request.

[cheenamalhotra]

Hi @Genmutant

Yes you're right, this token is received because both JDBC and Microsoft.Data.SqlClient drivers support Data Classification and we send DATACLASSIFICATION FeatureExt token during login. System.Data.SqlClient does not support this feature hence is not impacted.

The glitch is this token is not expected to be in the stream at a location where it's currently being received for this use-case, as per feature design. We're following up with product team internally to gather more details on the same. Will keep you posted!

[cheenamalhotra]

Client sends a 0x09 (DATACLASSIFICATION) as FeatureExt during Login (link), server sends acknowledgement with 0x09 (DATACLASSIFICATION) as FeatureExtAck.

Wireshark may not translate TDS login packets from client correctly.

[cheenamalhotra]

Hi @Genmutant

I'd like to confirm this was identified as a bug in the SQL Server 2019 and we have received confirmation on the fix made to address the same.

The upcoming SQL Server 2019 CU2 release will address this issue.
There will be no changes required in SqlClient and JDBC drivers.

[DaEkstrim]

I know this is not the right place to ask this kind of question, but I'm experiencing the same issue once I migrated from System.Data.SqlClient to Microsoft.Data.SqlClient while trying to execute a native compiled stored procedure on an SQL 2019 compatiblity Azure SQL Database through a linux-based dotNet Core 2.2 application on AKS, but I couldn't find any other information on the internet.

The question is: Do you have an information when this fix (CU2) would be released for Azure SQL Database (PAAS)?

[Genmutant]

Fixed in CU2: https://support.microsoft.com/en-us/help/4538342/fix-latest-drivers-fail-to-work-with-in-memory-compiled-queries-in-sql

[cheenamalhotra]

@Genmutant thanks for linking the release update, we'll close the issue!

[augustolp]

Hello @cheenamalhotra! How are you?
Do you know if this issue was also fixed in Azure SQL service?
Best,
Augusto.

[cheenamalhotra]

Hi @augustolp

The fix is currently not yet deployed in Azure instances.
Estimate of deployment is in April.

[simader]

Hello,
@cheenamalhotra:
Can anybody confirm that this is roled out on every azure DB instance? I have a strange behaviour that the same code is running on nearly all DBs, but on one database i get this excecption.

Or can be there an ohter issue?

[dougquidd]

@cheenamalhotra Can you confirm if the fix has been deployed to Azure SQL Service, and if not when it is scheduled?

[cheenamalhotra]

@dougquidd @simader

Do you have a repro app for Azure DB to reproduce errors you are facing?
As of the above code, "With Native Compilation" is not supported in Azure DB, so the above example is not applicable to Azure.

But the fix was deployed to Azure in April 2020 as per our information.

[tunechr]

I'm also getting this error, I have an Azure SQL, and running a stored procedure through .net core 3.1 / EF Core (3.1.2)
Works locally but not in Azure.

The code is simple, something like this:
var result = context.Database .ExecuteSqlInterpolated($"EXEC [dbo].[ConvertUser] {convertUserId}"); ;

[cheenamalhotra]

Hi @tunechr

Can you confirm you're able to run the stored procedure from SSMS?
And if yes, can you attach your scripts/repro here to look into it? (Ideally definition of "ConvertUser" stored proc in your case)

[tunechr]

Hi @tunechr

Can you confirm you're able to run the stored procedure from SSMS?
And if yes, can you attach your scripts/repro here to look into it? (Ideally definition of "ConvertUser" stored proc in your case)

@cheenamalhotra Sorry I didn't see the reply.

Yes I just tested from SSMS and it ran fine using:

DECLARE @return_value int
EXEC @return_value = [dbo].[ConvertUser]
@Id = 32261
SELECT "Return Value" = @return_value
GO

The code is very simple:
CREATE PROCEDURE[dbo].[ConvertUser]
@Id bigint
AS
BEGIN
SET NOCOUNT ON;
UPDATE Users
SET Discriminator = "Participant"
WHERE id = @Id;
END;
GO

Hope this helps?
Chris

[jumpingjackson]

Still having issues on Azure SQL+ Data Classification + TdsParser as of 8/18/2020:

If i remove all the data classification tags on that database the problem goes away.

System.InvalidOperationException: Internal connection fatal error. at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler) at Microsoft.Data.SqlClient.SqlBulkCopy.CreateAndExecuteInitialQueryAsync(BulkCopySimpleResultSet& result) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at CallSite.Target(Closure , CallSite , Object , IDataReader ) at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2T0,T1 at .( , DbConnection , DbTransaction ) at .( ) at .( ) at .Execute(List1 actions) at .(List1 ) at Z.BulkOperations.BulkOperation.Execute() at Z.BulkOperations.BulkOperation.BulkInsert() at .BulkInsertT at .BulkInsertT at .(DbContext this, StateManager , IReadOnlyList1 , Action1 ) at .(DbContext this, StateManager , IReadOnlyList1 , Action1 ) at .(DbContext this, Action1 , DbContext ) at DbContextExtensions.BulkSaveChanges(DbContext this, Action1 options) at DbContextExtensions..() at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj) at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location where exception was thrown --- at DbContextExtensions.BulkSaveChangesAsync(DbContext this, Action1 options, CancellationToken cancellationToken) at CafComplianceWebLib.Controllers.CensusController.CensusFileUpload(CensusFileUploadViewModel model) in D:\Users\jakeh\Source\NBS2\CafCompliance\CafCompliance\CafComplianceWebLib\Controllers\CensusController.cs:line 224 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Serilog.AspNetCore.RequestLoggingMiddleware.Invoke(HttpContext httpContext) at NSwag.AspNetCore.Middlewares.SwaggerUiIndexMiddleware.Invoke(HttpContext context) at NSwag.AspNetCore.Middlewares.RedirectToIndexMiddleware.Invoke(HttpContext context) at NSwag.AspNetCore.Middlewares.OpenApiDocumentMiddleware.Invoke(HttpContext context) at NSwag.AspNetCore.Middlewares.OpenApiDocumentMiddleware.Invoke(HttpContext context) at Joonasw.AspNetCore.SecurityHeaders.Csp.CspMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.<Invoke>g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)

[dougquidd]

@jumpingjackson That is due to a bug in SQLClient when using Data Classification with Bulk Copy. I believe that the bug was fixed in SQLCLient V2.

[jumpingjackson]

@jumpingjackson That is due to a bug in SQLClient when using Data Classification with Bulk Copy. I believe that the bug was fixed in SQLCLient V2.

Thanks! that fixed it. Its not obvious that you need to install 2.0.0 because if you install EFCore stuff sqlClient doesnt get referenced directly in your project so you dont get prompted to upgrade like most packagews.

[cheenamalhotra]

Ref: https://github.com/dotnet/SqlClient/pull/568 (available since v2.0)