"Redirect" connection policy when hitting Azure SQL DB from website hosted on Azure app service

Question

I have a website hosted in an Azure app service, which connects to a SQL Server DB hosted (serverless) on Azure.

• When I set the Azure SQL firewall Connection Policy to "Proxy" (https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture)everything works as expected.
• When I set it to "Redirect" it works for read-only queries but fails on inserts and updates with the message, "An existing connection was forcibly closed by the remote host" and an obscure HResult (-2146232060). Stack trace below.
• When set to "Default" it fails the same way, but it works from my locally-hosted version of the site (as expected, since that scenario apparently defaults to Proxy).

Per the link above, I would prefer to get Redirect working. I see the bullet points beginning with "Allow outbound communications..." but I'm not sure (A) how to do that in an app service and (B) whether it is relevant here anyway. This seems like one of those things that ought to just work when you set the SQL server firewall to allow access from Azure services, but alas.

Details that may or may not be relevant:

• The web app is .NET 5.0, F#, built on the SAFE Stack.
• SQL connectivity is provided by F# SqlDataProvider
• SQL setup doesn't have to be serverless, if that's the problem
• As promised, full stack trace gobbledygook:

{"HelpLink.BaseHelpUrl":"https://go.microsoft.com/fwlink","HelpLink.EvtID":"10054","HelpLink.EvtSrc":"MSSQLServer","HelpLink.LinkId":"20476","HelpLink.ProdName":"Microsoft SQL Server","SqlError 1":"System.Data.SqlClient.SqlError: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"},"Errors":null,"ExceptionMethod":null,"HResult":-2146232060,"HelpURL":null,"InnerException":{"ClassName":"System.ComponentModel.Win32Exception","Data":null,"ExceptionMethod":null,"HResult":-2147467259,"HelpURL":null,"InnerException":null,"Message":"An existing connection was forcibly closed by the remote host.","NativeErrorCode":10054,"RemoteStackIndex":0,"RemoteStackTraceString":null,"Source":null,"StackTraceString":null,"WatsonBuckets":null},"Message":"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)","RemoteStackIndex":0,"RemoteStackTraceString":null,"Source":"Core .Net SqlClient Data Provider","StackTraceString":" at Microsoft.FSharp.Linq.QueryModule.CallGenericStaticMethod@374.Invoke(Tuple2 tupledArg) in D:\workspace\_work\1\s\src\fsharp\FSharp.Core\Query.fs:line 379 at Microsoft.FSharp.Linq.QueryModule.clo*anonymous user*-18.Microsoft.FSharp.Linq.ForwardDeclarations.IQueryMethods.Execute[a,b](FSharpExpr1 q) in D:\workspace_work\1\s\src\fsharp\FSharp.Core\Query.fs:line 1928 at RoundTable.Data.SqlDataProvider.updateKisPlanIndividualResult@455.Invoke(Object context) at RoundTable.Data.SqlDataProvider.runWritableQuerya at RoundTable.Data.SqlDataProvider.runWritableQueryResult@107.Invoke(FSharpFunc2 f) at RoundTable.Validation.ErrorRecordModule.runWithErrorCatchingResult[a,b](FSharpFunc2 f, a x)","WatsonBuckets"

Answer 1

Hi @Nathan@rtinvestments.com Thanks for posting your Question on Microsoft Q&A.

Servers in SQL Database supports three options for the server's connection policy setting:

Redirect (recommended): Clients establish connections directly to the node hosting the database, leading to reduced latency and improved throughput. For connections to use this mode, clients need to:
Allow outbound communication from the client to all Azure SQL IP addresses in the region on ports in the range of 11000 to 11999.
Allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.

Proxy: For connections to use this mode, clients need to allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.

Default: This is the connection policy in effect on all servers after creation unless you explicitly alter the connection policy to either Proxy or Redirect.

We highly recommend the Redirect connection policy over the Proxy connection policy for the lowest latency and highest throughput. However, you will need to meet the additional requirements for allowing network traffic as outlined above. If the client is connecting from a workstation on-premises then you may need to work with your network admin to allow network traffic through your corporate firewall.

Please refer to the article below for more details.

Regards,
Oury

Answer 2

Hi @Nathan@rtinvestments.com Thank you for your question.

Regarding the "Allow outbound communication from the client to all Azure SQL IP addresses in the region on ports in the range of 11000 to 1199." You can just use the Service Tag to achieve it but the error message indicates there might something else in the play too. Since this is a troubleshooting question in nature with unknown factors. I will suggest to create a support ticket so they can better investigate.

Please let me know if you don't have a support ticket so We can unable a one time free support case.

Regards,
Oury