Solved

Opening a SqlConnection now broken - multiple exceptions when using TCP

Posted on 2013-10-24
8
1,605 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sqlheroguy
Comment Utility
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
Promote certifications in your email signature

Has your company recently won an award or achieved a certification? They'll no doubt want to show it off. Email signature images used to promote certifications & awards can instantly establish credibility with a recipient and provide you with numerous benefits.

 

Author Comment

by:sqlheroguy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now