Improve company productivity with a Business Account.Sign Up

x
?
Solved

Opening a SqlConnection now broken - multiple exceptions when using TCP

Posted on 2013-10-24
8
Medium Priority
?
1,704 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

580 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