Solved

Opening a SqlConnection now broken - multiple exceptions when using TCP

Posted on 2013-10-24
8
1,633 Views
Last Modified: 2014-11-12
I've run into a problem where not too long ago, something like this would work just fine from app code:

            using (var conn = new SqlConnection("Data Source=sqlinstance;Database=work;User ID=sa;Password=*****"))
            {
                conn.Open();
            }

Now when I do this for a remote instance, I get an exception right on the Open:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The message received was unexpected or badly formatted.)

(as best I can tell, running locally it's using Shared Memory, running remotely it's using TCP)

I was about to blame changes at my client (network/VPN) but I can get this problem now from anywhere connecting to a Windows Azure VM with a SQL instance as well (also know it previously worked).

I did do some software installs not long ago, but in all cases have removed them (VS 2013 with .NET 4.5.1, Microsoft Monitoring Agent - never fully installed anyway, Microsoft Message Analyzer), and also tried repairs against various components to no avail.

Hitting Continue in the debugger, I get a subsequent "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." and continuing again, "The connection was not closed. The connection's current state is connecting." which is what ultimately presents in the exception handler.

Something has clearly become broken but I'm kind of at a loss of where I should look next.
- Joel
0
Comment
Question by:sqlheroguy
  • 5
  • 2
8 Comments
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 39600871
Do you have SSMS on the client?  Can you make a connection to the SQL Server that way?  What port is the SQL Server listening on?  You can see this using the SQL Server Configuration Utility.
0
 

Author Comment

by:sqlheroguy
ID: 39600994
Using OSQL, I can connect without an error to the same remote instance (run queries, etc.).

Using SSMS, I get the same error I mentioned initially - and then it proceeds to give me a message about "SQL Server Management Studio has stopped working" and then closes.

This is also being done in the host OS on Windows Server 2012.  On a VM on the same box, I can run the Open() just fine to the remote instance.

Given all of the above, seems pretty clear it's not a problem on the remote side.  To your other question - using port 1433.
0
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 39601193
OSQL is odbc based and deprecated.  SQLCMD is the new command line client that uses the native client, just like .NET or SSMS.

"This is also being done in the host OS on Windows Server 2012.  On a VM on the same box, I can run the Open() just fine to the remote instance."

Not sure I understand this.  I thought you said you were using shared memory locally?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sqlheroguy
ID: 39601264
Right - use of OSQL was just to demonstrate the issue is most likely somewhere above the protocol level - no problems with connecting to the remote server with that, versus SSMS and ADO.NET which both fail on the same box.

The VM statement was further proof that the problem isn't necessarily protocol level - same physical box but from a VM, also have perfectly functioning SSMS & ADO.NET remote connectivity.
0
 

Author Comment

by:sqlheroguy
ID: 39601293
An additional point...  I just tried the SqlConnection .Open() in this simple test harness, but switching the target .NET framework to 3.5...  and it *works*...  so very much looking my like .NET 4.0/4.5 framework version has become corrupted somehow.  Any suggestions on a way to fix that?  (There's no easy "repair" options, it seems.)
0
 

Accepted Solution

by:
sqlheroguy earned 0 total points
ID: 39601956
I've managed to resolve the problem.  I replaced System.Data.dll from a working location.  Non-working version is 4.0.30319.18408, working version is 4.0.30319.18046.  May not be the safest route but it does let me continue working again, without a complete dev box rebuild.. whew.
0
 

Author Closing Comment

by:sqlheroguy
ID: 39611060
This allows the code noted initially as failing to actually execute without an exception any longer.  B instead of A since I'd still have preferred to understand the root causes here.
0
 

Expert Comment

by:River2009
ID: 39694128
The correct answer is found here:

http://social.msdn.microsoft.com/Forums/en-US/ea2c470f-14ba-4864-8a39-1396f4e361e8/sqlconnection-now-failing-exception-when-connecting-to-remote-instance?forum=sqldataaccess

run:

netsh winsock reset

Reboot the server. The provider connection order will be reset and the problem will be resolved.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most of the applications these days are on Cloud. Cloud is ubiquitous with many service providers in the market. Since it has many benefits such as cost reduction, software updates, remote access, disaster recovery and much more.
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question