C# SQLConnection.Open() hangs, with no exception

Question

I am using C# in Visual Studio 2019, with Xamarin.Forms, and SQl in SSMS 2018 and have the below code (where [] is used to replace unneccessary information)

try
{
  using(SqlConnection connection = new SqlConnection())
  {
     connection.ConnectionString = "Server=[ServerName]; Database=[DatabaseName]; User Id= [UserID]; Password=[Password]";

     connection.Open();

     SqlCommand command = new SqlCommand("SELECT * from [TableName]", connection);

     [Does stuff here]
  }
}
catch (Exception ex)
{
  System.Diagnostics.Debug.WriteLine(ex)
}

When I run this, it hangs indefinitely at connection.Open(). Debug mode continues to run and appears to move on from Connection.Open(), but never reaches the next line.

I have attempted this with different versions of the ConnectionString, using different databases and with Trusted_Connection=true instead of specifiying the username and password but they have made no difference. Adding Connection Timeout = 5 to the connectionString has no effect.

I believe it is probably an issue with my settings in SQL but as I am a novice with this I have no idea where to start and the similar forums posts I have checked have been given answers along the lines of Connection Timeout (Connection.open for hangs indefinitely, no exception is thrown) or never got answered.

Any advice would be greatly appreciated.

Is the database server on the same machine? If not, is it on the same network? Can you ping the db server? — , Oct 14 '19 at 15:46
Does the SQL Server have the inbound and outbound blocked on the designated port? — Greg, Oct 14 '19 at 15:51
SqlConnection.Open works. Connection strings work, although we don't know what *your* connection string is since you've removed every value. There's no infinite block issue, which is why questions that ask about this never get an answer. It's always a timeout issue or a server that isn't working, or a networking issue. — Panagiotis Kanavos, Oct 14 '19 at 16:11
If you use a local server use `.` or `(local)` or `localhost`, depending on what network protocols are enabled. By default, only shared-memory is enabled. instead of anything else. If you try to connect to a named instance, eg SQL Server Express, include the instance name, eg `.\SQLExpress`. For [LocalDB](https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/creating-a-connection-string) ensure SQL Server Express is installed and use `(LocalDB)\MSQLLocalDb` — Panagiotis Kanavos, Oct 14 '19 at 16:15
If this was a timeout issue, then it wouldn't hang and would instead return an exception, especially when I set the timeout to 2 seconds, right? I am able to connect to the server directly using SSMS and there is no network between the C# and the database as they are on the same device — Mythos, Oct 14 '19 at 16:17
using (localDB)\\MSQLLocalDb has resulted in an exception but using .\\SQLExpress had the same issue as before. The excception returned has the message LocalDb is not supported on this platform — Mythos, Oct 14 '19 at 16:20
using localhost returns the SQLException ```"Snix_Connect (provider: SNI_PN7, error: 40 - SNI_ERROR_40)\nSnix_Connect (provider: SNI_PN7, error: 40 - SNI_ERROR_40)"```. This appears to be related to Xamarin but I have no idea how it would be causing the problem — Mythos, Oct 14 '19 at 16:36
@Mythos By default the inbound and outbound communication for SQL is blocked by Windows Firewall, you should open up port 1433 (I'd double check that port number). — Greg, Oct 14 '19 at 18:16

Jay · Accepted Answer · 2019-10-14T17:16:25.973

0

Can you log into SSMS with the credentials that are in the connection string?

Otherwise I've had luck making sure the connection isn't already open or broken first:

if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
{
    connection.Open();
}

edited Oct 14 '19 at 17:16

answered Oct 14 '19 at 15:54

Jay

258
2
6
16

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/200867/discussion-on-answer-by-jay-c-sqlconnection-open-hangs-with-no-exception). – Samuel Liew Oct 14 '19 at 21:22

score -1 · Answer 2 · answered Oct 14 '19 at 16:08

-1

Can you try code changing line as per below -

connection.ConnectionString = "Data Source=[ServerName]; Initial Catalog=[DatabaseName]; Integrated Security=SSPI;"

answered Oct 14 '19 at 16:08

riddhi soni

1

asjuan · Answer 3 · 2021-02-25T01:49:54.140

A workaround to this problem is to pass in a cancellation token instance as shown below,

public async Task<IEnumerable<Toy>> ShowToybox(CancellationToken t)
{
   // notice I turned this into an async operation. 
   // Reason is to let this operation to find its way out if something happens
    var connString = "Server=xyz; Connection Timeout=250";
    //Timeout might not happen, if that is not the case see below..
    using(SqlConnection connection = new SqlConnection(connString))
    {
        if ( t.IsCancellationRequested) {
            t.ThrowIfCancellationRequested();
        }
       // await query here. To fetch records from the toybox table
       return matches;
     }

Main issue is that you cannot trust connection.State

To get this working, code that consumes this method should expect something might go wrong.

class Program
{
   static void Main()
  {
      var box = new ToxBox(); //it has method shown above
      var s = new CancellationTokenSource();
      s.CancelAfter(400); //it prevents method from hanging
      var task = Task.Run(() = box.ShowToybox(s.Token));
      try
      {
          task.Wait(s.Token);
          var myToys = task.Result();
          Console.WriteLine($"I have {myToys.Count()} toys");
      }
      catch (Exception e)
      {
          Console.WriteLine("Something happened!"); 
      }
      finally
      {
          s.Dispose(); //this is important
      }  
  }
}

See https://learn.microsoft.com/en-us/dotnet/standard/threading/cancellation-in-managed-threads

That if statement is redundant as the "ThrowIfCancellationRequested" method makes that evaluation internally. Also, single character parameter/variable names (especially those with no apparent correlation with the purpose) should be avoided at all costs. — Timothy Stepanski, Feb 25 '21 at 01:31

C# SQLConnection.Open() hangs, with no exception

3 Answers3