Skip to main content

Hello,

While migrating from TX 20.10.35.64 to new version 6675.2 with the Portal, we’re running into issues with connecting to a certain data source. I’m getting the same error message in the Portal as i’m getting in SSMS.

In our current environment on Azure VM A I’m able to successfully connect to the datasource running on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)  using the TimeXtender SQL Data Source 16.1.0.1 version. I’m also able to connect through SSMS to this Server.

However, using the same settings and credentials on our new environment on Azure VM B using the portal I’m not able to connect, resulting in the error message below. The portal has SQL Data Source version 17+, so not sure if that has something do with it. Using the SqlClient Data Provider Source results in the same error.

Meanwhile, I’m also not able to connect to this server with SSMS on VM B. I am able to ping the server through CMD though, so the connection is up and running. 

I’ve asked our IT partner to check all the network, routing & security settings of the VM’s and compare them, but all looks fine.

Googling results in some SSL/TSL and certificate related settings. I tried enabling/disabling all kind of settings but with no result.

 

===================================

Cannot connect to 10.27.3.8.

===================================

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Framework Microsoft SqlClient Data Provider)

------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-10054-database-engine-error

------------------------------
Server Name: 10.27.3.8
Error Number: 10054
Severity: 20
State: 0


------------------------------
Program Location:

   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateSqlConnection(UIConnectionInfo ci, IDbConnection dbConnection, IServerType server)
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

An existing connection was forcibly closed by the remote host
 

@KCMT can you please confirm where the data source is located? Is it an Azure SQL db? Which authentication method are you using?

Is VM A running one ODX instance and VM B is running another ODX instance?


@KCMT can you please confirm where the data source is located? Is it an Azure SQL db? Which authentication method are you using?

Is VM A running one ODX instance and VM B is running another ODX instance?

Hi @Christian Hauggaard 

Data source is an On Premise Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor located in in South Africa.

VM A is running another ODX instance than VM B.

Below are the current working settings on VM A with SQL Data Source 16.1.0.1.

 


@KCMT thanks. can you please share the data source settings for VM B?


@KCMT thanks. can you please share the data source settings for VM B?

 

Changing the Encryption or Trust Server Certificate results in the same error message.


Hi @KCMT since you are unable to connect to the data source using SSMS on VM B, this appears to be a networking issue. Please ask your IT department to check access to the server from VM B and compare differences between VM A and VM B


Hi @KCMT since you are unable to connect to the data source using SSMS on VM B, this appears to be a networking issue. Please ask your IT department to check access to the server from VM B and compare differences between VM A and VM B

Hi @Christian Hauggaard 

I asked them before posting this questions. They checked all the network, routing and security settings and concluded they are the same, as there’s also no distinction between certain servers, e.g. VM A & VM B. I can also successfully ping the server from VM B, just the further login process seems to be not working on VM B… 

Can this then still be some network/Firewall issue?

 

 


Hi @KCMT there must be something differences between the VMs if one is able to access and the other is not, or this is due to some settings on the SQL server

I asked Chat GPT and got the below answer:

If you can ping the SQL server from the second VM but cannot connect to it via SQL Server Management Studio (SSMS), the issue is likely related to one of the following:

  1. SQL Server Configuration:

    • Ensure that SQL Server is configured to accept remote connections.
    • Verify that TCP/IP protocol is enabled and configured correctly in SQL Server Configuration Manager.
  2. Firewall Settings:

    • Check if the Windows Firewall on the SQL server VM is allowing inbound traffic on port 1433.
  3. Network Security Group (NSG) Rules:

    • Ensure the NSG associated with the SQL server VM allows inbound traffic on port 1433.
  4. SQL Server Authentication:

    • Ensure the login credentials (username and password) used in SSMS are correct and have the necessary permissions.

Detailed Steps to Troubleshoot:

1. SQL Server Configuration

  1. Allow Remote Connections:

    • Open SQL Server Management Studio (SSMS).
    • Right-click the server name in Object Explorer, and select "Properties".
    • Go to the "Connections" tab and ensure that "Allow remote connections to this server" is checked.
  2. Enable TCP/IP:

    • Open SQL Server Configuration Manager.
    • Navigate to "SQL Server Network Configuration" -> "Protocols for rYour SQL Instance]".
    • Ensure "TCP/IP" is enabled.
    • Right-click on "TCP/IP", select "Properties", and check that the "Listen All" property is set to "Yes".
    • Ensure that TCP Dynamic Ports is blank and that TCP Port is set to 1433 (or your configured port).

2. Firewall Settings

  1. Windows Firewall:
    • On the SQL server VM, open the Windows Firewall settings.
    • Create an inbound rule to allow traffic on port 1433.
      • Go to "Inbound Rules" -> "New Rule".
      • Select "Port", then "TCP", and specify port 1433.
      • Allow the connection, and specify the profiles this rule applies to (Domain, Private, Public).
      • Name the rule and finish.

3. Network Security Group (NSG) Rules

  1. Check NSG Rules:
    • In the Azure portal, navigate to the NSG associated with the SQL server VM.
    • Ensure there is an inbound rule that allows traffic on port 1433 from the second VM's IP address or subnet.

4. SQL Server Authentication

  1. Login Credentials:

    • Ensure the SQL login credentials (username and password) used in SSMS are correct.
    • Verify that the SQL server is set to allow SQL Server and Windows Authentication mode:
      • In SSMS, right-click the server name in Object Explorer, select "Properties".
      • Go to the "Security" tab and ensure "SQL Server and Windows Authentication mode" is selected.
  2. SQL Server Logs:

    • Check the SQL Server error log for any login attempt failures from the second VM. This can provide specific error messages that indicate the problem.

Testing Connectivity

  • Use telnet or nc:
    • From the second VM, open a command prompt or terminal and run:
      • telnet <SQL_Server_IP> 1433
      • If telnet is not available, you can use nc (netcat) if installed: nc -zv <SQL_Server_IP> 1433
    • Successful connection indicates that port 1433 is open and listening.

Hi @Christian Hauggaard 

I tried most of these things going through the same prompt haha, but with no luck unfortunately. I think I’ll go back to our IT partner with the conclusion that there must be a difference in the NSG / Firewall.

Thanks for your help & Suggestions so far!


Hi,

 

not sure about SQL Server 2008 R2 (it's been a while...) but failed logins should be visible. In modern on-prem SQL you can run: EXEC sp_readerrorlog 0, 1, 'Login failed' on the SQL instance . If you see nothing there, that implies your connection is not succeeding either through networking or through the instance's setup. If you do see something, it will list the reason why login is failing.

For networking I usually check:

  • nslookup <dns name of SQL Server>
  • Test-NetConnection <dns name of SQL Server> -port 1433

If both succeed I would check the TLS situation; you are running on a stone-age build (July 2015) of a stone-age version of SQL Server and I don't think it has TLS 1.2 support. Apart from upgrading to an actually supported version of SQL Server (and licensed) I would patch that instance immediately and that may help.

You can also run Connection Troubleshoot in the Azure Portal from your VM to the SQL Server and check that nothing is going wrong in the routing.


@Christian Hauggaard & @rory.smith 

Agreed that SQL Server 2008 R2 should be upgraded. I will advise this. However, unfortunately this is out of my power... 

One of the differences I noticed in the VM’s is the OS. VM A: OS: Windows Server 2019 Datacenter vs. VM B: Windows Server 2022 Datacenter Azure Edition. 

Could it be that SQL 2008 doesn’t like connecting to  Windows Server 2022 Datacenter Azure Edition. 


Hi,

hopefully someone listens and upgrades that system or takes the security precautions that would be required to safely run such a system… :-)

it could very well be that Windows Server 2022 no longer allows less secure ways of connecting to SQL Server or that the default settings for 2022 disallow lower than TLS 1.2 . Your build of SQL Server does not support TLS 1.2: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-1-2-support-microsoft-sql-server

There is an update for SQL 2008R2 that makes this possible:

 

 

 


Hi @rory.smith & @Christian Hauggaard 

Yeah, that would be ideally, since their 2 / 3 other South-African servers are on 2016 already, not sure why this one is on 2008. But i’ve got some guesses though ;) 

Meanwhile, I found that on the SQL servers of both VM A where the connection is working and VM B a minimum TLS version of TLS 1.2 is set up, So i think it also might not be on the minimum TLS side.

Besides, when looking at the TLS setting with REGEDIT and mirroring the setup of VM A and VM B the issue still persists.

Sources related to error:

Connection error 10054 occurs in SQL Server post upgrade - SQL Server | Microsoft Learn

An existing connection was forcibly closed (OS error 10054) - SQL Server | Microsoft Learn

Regedit path Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\

 


Hi,

given that you aren't even on SP2 of 2008 R2, you shouldn't have TSL 1.2 support in that SQL Server yet. But you may have a mismatch between the cypher suites available on the VM that cannot connect and the SQL Server as Windows versions, .NET, and registry settings can all influence what is available. I would just deploy another VM with the same OS and setup as the working one and verify through SSMS whether that can connect, that will be easier than running all sorts of scripts to check TLS setup between the VM and server.


Hi @KCMT please let us know if the issue is resolved, and if so please help us by marking a best answer above. If you have any follow up questions please let us know


Hi @rory.smith & @Christian Hauggaard ,

Thanks for all your help.

In the meantime I escalated the related security issues higher up and the company upgraded to Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64)   Oct 18 2021 09:37:01   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor).

Just tested it and I’m able to connect successfully.

Conclusion: SQL Server 2008 R2 is not (easily) supported anymore by Windows Server 2022 Datacenter Azure Edition?


Hi @KCMT ,

 

that is the best result achievable, success on both topics!


Reply