@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?

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 @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:

• https://techcommunity.microsoft.com/t5/sql-server-blog/tls-1-2-support-for-sql-server-2008-2008-r2-2012-and-2014/ba-p/384613
• https://support.microsoft.com/en-us/topic/update-to-add-support-for-tls-1-1-and-tls-1-2-in-windows-server-2008-sp2-windows-embedded-posready-2009-and-windows-embedded-standard-2009-b6ab553a-fa8f-3f5e-287c-e752eb3ce5f4 

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!