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

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:

-
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.

-
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

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.

StackExchange Database Administrators Q#59304, answer score: 3