SQL Query with large parameter fails

[afscrome]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the bug

I have a sql query I need to run against aspire which involves sending a 184 MB file through a SQL Parameter, however this large file keeps on failing due to the network request being terminated. I'm guessing I'm hitting some kind of buffer limit which needs to be bumped up

The request goes through fine however if I bypass the aspire proxy.

Expected Behavior

The query should complete successfully.

Steps To Reproduce

Run the following powershell:

$ConnectionString = "Server=127.0.0.1,14431;User ID=sa;Password=$Password;TrustServerCertificate=true"
$stringLength = 193037189
$input = "".PadRight($stringLength,"d") 

$sqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlConnection.Open()
$sqlCmd.CommandText = @'
print 1
'@
$sqlCmd.Parameters.AddWithValue("@Arg", $input) | Out-Null
$sqlCmd.ExecuteNonQuery()

Exceptions (if any)

Line |
  19 |  $sqlCmd.ExecuteNonQuery()
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "ExecuteNonQuery" with "0" argument(s): "A transport-level error has occurred when sending the request to the server. (provider: TCP     
     | Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

.NET Version info

.NET SDK:
 Version:           8.0.303
 Commit:            29ab8e3268
 Workload version:  8.0.300-manifests.d7126b9e
 MSBuild version:   17.10.4+10fbfbf2e

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.22621
 OS Platform: Windows
 RID:         win-x64
 Base Path:   C:\Program Files\dotnet\sdk\8.0.303\

.NET workloads installed:
 [aspire]
   Installation Source: SDK 8.0.300, VS 17.10.35027.167
   Manifest Version:    8.1.0/8.0.100
   Manifest Path:       C:\Program Files\dotnet\sdk-manifests\8.0.100\microsoft.net.sdk.aspire\8.1.0\WorkloadManifest.json
   Install Type:        FileBased

Host:
  Version:      8.0.7
  Architecture: x64
  Commit:       2aade6beb0

.NET SDKs installed:
  6.0.424 [C:\Program Files\dotnet\sdk]
  8.0.300-preview.24203.14 [C:\Program Files\dotnet\sdk]
  8.0.303 [C:\Program Files\dotnet\sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.App 6.0.32 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 7.0.20 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 8.0.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 8.0.7 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 6.0.32 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 7.0.20 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 8.0.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 8.0.7 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 6.0.32 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 7.0.20 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 8.0.2 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 8.0.7 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

Other architectures found:
  x86   [C:\Program Files (x86)\dotnet]
    registered at [HKLM\SOFTWARE\dotnet\Setup\InstalledVersions\x86\InstallLocation]

Environment variables:
  Not set

global.json file:
  C:\src\MyApp.Aspire\global.json

Learn more:
  https://aka.ms/dotnet/info

Download .NET:
  https://aka.ms/dotnet/download

Anything else?

If I go to the sql container w/out the aspire proxy, or disable proxying on the sql database resource, then this query succeeds w/out issue

        var endpoint= sql.Resource.Annotations.OfType<EndpointAnnotation>()
            .Single(x => x.Name == sql.Resource.PrimaryEndpoint.EndpointName);
        endpoint.IsProxied = false;

[afscrome]

@karolz-ms Any thoughts

[karolz-ms]

@karolz-ms Any thoughts

Not good. Should not happen. Will take a look.

[danegsta]

Latest DCP insertion has several updates that should resolve this issue. First we've increased the default timeout on read/write. Second, the timeout on read and write were being set at the same time, which meant that a long read could use up most of the allowed time for a write; now we're setting the write timeout only once read is complete, meaning read and write both have the full allowed timeout to complete. I haven't been able to trigger any errors with the provided sample using the latest build.

[afscrome]

Confirming that after upgrading to aspir e8.2, I'm now able to use the proxy. My app has now lasted for several hours dying due to a SQL failure. Thank you very much @danegsta .