Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Opening a SqlConnection now broken - multiple exceptions when using TCP

Posted on 2013-10-24
8
Medium Priority
?
1,674 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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