0

I have been at it for the past 11 hours, and I still can't figure it out. It started out as a JAVA connectivity problem, but at this point I was able to confirm, that the only tool able to connect to a remote SQL Server instance is the SQL Server Management Studio. JDBC fails, Windows 7 ODBC fails, DataStage clients from Linux fails, and Visual Studio 2017 Data Connections as well. Here is sample .Net code (C#) which works for other servers, except the one I am having trouble with:

    [TestCase(true)]
    [TestCase(false)]
    public void TestMethod(bool encrypt)
    {
        var sscsb = new SqlConnectionStringBuilder {
            DataSource = $"{server}\\{instance},{port}",
            NetworkLibrary = "dbmssocn",
            PacketSize = 4096,
            InitialCatalog = database,
            IntegratedSecurity = false,                
            UserID = user,
            Password = password,
            Encrypt = encrypt,
            TrustServerCertificate = true,                
        };
        try
        {
            using (var conn = new SqlConnection(sscsb.ConnectionString))
            {
                conn.Open();
            }
            Assert.Pass("Connected");
        }
        catch (Exception e)
        {
            Assert.Fail(e.Message);
        }
    }

The error returned when trying to connect from this test class is simply:

Message: Login failed for user 'etldstg'.

In JAVA, depending on the combination of TLS settings (mind you that login is always encrypted with a self-signed certificate), I sometime get this:

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:37dc2f52-c952-4f50-8fc9-62c3bdd84041".

I know that JAVA does not support the DHE cipher

Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384 Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384

At this point, I can't ask the server admin to follow Microsoft's recommendation and Disable DHE by registry hacking

So, I am stuck. Has anybody seen anything similar? How did you resolve it?

Darek
  • 4,687
  • 31
  • 47
  • Does user etldstg has a working account on the database engine? – PepitoSh Jul 12 '18 at 07:53
  • Yes, as I mentioned, I can connect with SSMS just fine. Same user, same password, same everything. – Darek Jul 12 '18 at 07:55
  • Doesn't SSMS use a different network library? – PepitoSh Jul 12 '18 at 08:02
  • Maybe this answer applies to your problem: https://stackoverflow.com/a/9001761/44522 – MicSim Jul 12 '18 at 08:04
  • Nope, I forced that as well. Just to make sure. Also, when I look over the TLS debug in JAVA, it does show connection attempts being made, when I use TLSv1 for the sslProtocol.. – Darek Jul 12 '18 at 08:04
  • Thanks @MicSim, but no, I've tried that. Also, the puzzling part is that not even ODBC is able to establish a connection. What is so darn special about SSMS? :D – Darek Jul 12 '18 at 08:17

1 Answers1

0

First check if the route on your network between the servers are open.

If so it is another problem, you can ask your administrator to see if the TCP/IP protocol is active on the SQL Server Instance. You can do so by opening SQL Server Configuration Manager on the server and check the network protocols.

robinthebird
  • 9
  • 5
  • That does not explain why SSMS works, but the others do not. Thanks for your contribution. – Darek Jul 12 '18 at 09:25
  • Just to be clear, I can connect to the port the server is listening on, so it is not a routing issue. – Darek Jul 12 '18 at 09:36