A Difficult Case of "A transport-level error has occurred when receiving results from the server" SQL Server

Question

We are building a new BizTalk cluster, with two BizTalk Application Servers and two SQL Servers. From SSMS on BizTalk AppServer #1 (and similarly from #2), we have two scenarios:

1. Current - our infrastructure team just rebuilt 2012/R2 Windows. It cannot connect to one specific SQL Server, but it can connect to others. The perplexing part is that any other machine seems to be able to connect to the same SQL Server just fine, and also run queries against it.

2. Yesterday - (We rebuilt the box since yesterday, so can't go back... just wanted to describe the issues.) It seemed to be connecting sporadically. When it was connected, I tried a few queries, created a test database and table, and tried the following:

Code:

declare @MaxLoops int = 100
declare @LoopCounter int = 0 

while (@LoopCounter < @MaxLoops) 
begin 
     set @LoopCounter = @LoopCounter + 1 

     --select SYSDATETIME(), * from NealTest.dbo.NealTest
     waitfor delay '00:00:01' 
     print @LoopCounter 
     print SYSDATETIME() 
end 

My original intent of the script was to see if we lost connections after it got "rolling", in other words I could set it up and increase the @MaxLoops and let it run for an hour or something. (Could also add try/catch to help it keep going to see if their were sporadic connectivity problems.)

The script (with the Select statement commented out) gave a

Transport-level error

So we started highlight one or two lines at a time. The declare worked fine, and I could print the values after the declare. When we lowered the @MaxLoops to 5 it actually ran. We increased to 15 it failed with Transport-level error. The same script runs fine (even up to @MaxLoops = 1000) from any other client in SSMS 2008 or 2012 client running against the same SQL Server.

This lead me to think that the packet size involved might have been the issue. I put about 20 rows of data with 40 bytes per row, and was able to select the entire table, which seemed to nullify the idea that the packet size was the issue.

And further, from the BizTalk App Server #1, we can use SSMS to connect to any other SQL Server in our shop, and it works fine. So the issue seems to be specifically between these two pairs of servers.

We are stuck, trying to decide if we should call Microsoft, rebuild the SQL Server, or what.

The SQL Server @@Version is

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )

The original issue was that the BizTalk configure utility was having the same "Transport-level errors". In the current scenario, BizTalk is not even installed. If SSMS cannot connect, we don't expect BizTalk to connect.

There is no firewall between these internal servers. Could some GroupPolicy have any impact?

Answer 1

I'm not sure what authentication method you are using, but I've seen issues when using Windows Authentication when the DC (Domain Controller) is being overwhelmed with requests you can see issues. Usually it is a timeout issue though.

If you are using Windows Auth, try switching to SQL Auth to rule that out. Sorry I tried to add a comment but it wouldnt let me b/c i'm new.

Answer 2

We opened a Microsoft Ticket, and this was the response from our infrastructure team. I didn't get the HotFix KB #.

Steps to fix the Problem:

1) Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

2) Disable Receive Side-Scaling from the NIC Configuration/Advanced Tab on all 4 nodes. This must also be performed from the command prompt with: netsh int tcp set global rss=disabled

3) Disable TSC Chimney from the NIC Configuration/Advanced Tab on all 4 nodes. This must also be performed from the command prompt with: netsh int tcp set global chimney=disabled

4) Disable Large Send Offload V2 (IPv4) from the NIC Configuration/Advanced Tab

5) Reboot All Nodes

6) After nodes are back on-line, launch a command prompt and verify that the Connection Properties are still Disabled with the command: netsh int tcp show global