Solved

Opening a SqlConnection now broken - multiple exceptions when using TCP

Posted on 2013-10-24
8
1,623 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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