2

I am currently unable to connect to a remote SQL Server using the TCP/IP protocol in C#.

Using named pipes does work, but the TCP/IP connection doesn't. The frustrating thing is that I can connect to the database using the TCP/IP protocol in the server explorer window within Visual Studio 2015, but my code cannot, even though I am copying and pasting the exact connection strings.

As soon I a remove "Network Library = dbmssocn" from the connection strings, the code works, but this makes it use named pipes, which I cannot rely on being an an option outside of the development environment.

Just in case, I have verified that the TCP/IP protocol is enabled on the SQL Server. This makes sense, since I can specify the TCP/IP protocol in visual studio's server explorer window and those connections work.

The server is also configured for both SQL authentication and Windows authentication. The windows firewall is probably also not the issue as it is currently turned off.

Here is the code that I'm using: 

        DbConnection testConnectionIntegrated = new SqlConnection("Data Source = MyServer; Initial Catalog = MyDatabase; Integrated Security = True; Encrypt = True; TrustServerCertificate = True; Network Library = dbmssocn");
        try
        {
            Console.Write("Attempting to connect using Windows Authentication...");
            testConnectionIntegrated.Open();

            Console.WriteLine(testConnectionIntegrated.State);
        }
        catch (Exception caughtException)
        {
            Console.WriteLine("Integrated Security Connection Failed");
            Console.WriteLine(caughtException.Message);
        }
        finally
        {
            Console.Write("Closing connection...");
            testConnectionIntegrated.Close();
            Console.WriteLine(testConnectionIntegrated.State);
        }


        DbConnection testConnectionSQLAuth = new SqlConnection("Data Source = MyServer; Initial Catalog = MyDatabase; Persist Security Info = True; User ID = my_user_name; Password = my_password; Encrypt = True; TrustServerCertificate = True; Network Library = dbmssocn");
        try
        {
            Console.Write("Attempting to connect using SQL Authentication...");
            testConnectionSQLAuth.Open();
            Console.WriteLine(testConnectionSQLAuth.State);
        }
        catch (Exception caughtException)
        {
            Console.WriteLine("SQL Authentication Connection Failed");
            Console.WriteLine(caughtException.Message);
        }
        finally
        {
            Console.Write("Closing connection...");
            testConnectionSQLAuth.Close();
            Console.WriteLine(testConnectionIntegrated.State);
        }

And this is the error message I'm seeing for both connection attempts:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A non-recoverable error occurred during a database lookup.)

Is there anything that I'm missing?

Edit: I have verified the following on the server:

  • Remote connections are allowed.
  • The TCP/IP protocol is enabled and configured.
  • The named pipes protocol is enabled and configured.
  • Firewall is not the problem.
  • The SQL Server and SQL Server Browser services are running.
  • I have restarted the services to make sure.

Also, I have restarted my development machine, and the problem still exists.

Robert
  • 371
  • 1
  • 7
  • If you google the error you'll find lot's of articles. it may have different reason. one useful in SO is: http://stackoverflow.com/questions/18060667/why-am-i-getting-cannot-connect-to-server-a-network-related-or-instance-speci – FLICKER Apr 07 '16 at 19:03
  • Thank you, but I did use Google, and I have already tried those solutions. I mentioned in my question that I was able to connect using named pipes, which would not be possible if remote connections were disabled. I also mentioned that the firewall was turned off and that I verified that the protocol was enabled on the database. All of the required services are running. If something was wrong with the database configuration, would I still be able to connect using the TCP/IP protocol within the server explorer panel within Visual Studio? – Robert Apr 07 '16 at 21:04
  • Sorry, I should read it more carefully. Sounds like a weird situation! – FLICKER Apr 07 '16 at 21:22
  • I am also having similar connectivity issues when I try to run unit tests using VSTest.console.exe against a remote DB using TCP. I've also opened all ports and have made sure that I can connect remotely to the DB over TCP using different methods (see: https://blogs.msdn.microsoft.com/steverac/2010/12/13/test-remote-sql-connectivity-easily/) but I still get connection errors. My team's speculation is that this is related to a user permission somewhere deeper in the process (maybe somewhere in VSTest.console.exe libraries?) – exbuddha Jul 06 '16 at 18:10

0 Answers0