Forum Discussion

User1165's avatar
User1165
Copper Contributor
Sep 10, 2024

Unable to connect to windows mssql 2022 update 14 running on opposite node

We are in the attempt of creating a Always On, 2 node,  setup. We were sucessful in windows 2016 and mssql. We reloaded to Windows 2022 server, and MSSQL Server 2022 Developer Edition.

     We get MSSQL and SSMS loaded on both boxes. We create a database and create and grant the required DB user. On node1, we connect to DB just fine with SSMS using SQL Authentication. 

     On Node 2 we use SSMS to try to connect to node1 (with SQL Authentication) without success, just the generic network error. Our DBA has MSSQL server at his house he tests with, and we cannot find the differences. we can ping node1 from 2, we can \\ node1 from node2 (i know different mechanisms), but network is not the issue. we can rdp to each other.

      Is there something new in SQL Server 2022 that we are overlooking? We cannot connect to the DB running on NODE1 from other SQL developer tools either. 

1) The SQL server properties are set to permit remote login, (have not enabled Always On yet, until we can get basic connectivity tested).

2) MS Defender Firewall has been disabled.

3) NODE1 Can connect to itself without issue.

4) NO corp external firwall in the way, had them check that.

5) NO Domain involved, just a workgroup between the 2 server. 

6) Enabled Named Pipes and TCPIP in SQL Server Network properties.

Error Message from NODE 2

TITLE: Connect to Server
------------------------------

Cannot connect to SSQL1\MSSQL.

------------------------------
ADDITIONAL INFORMATION:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

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

------------------------------
BUTTONS:

OK
------------------------------

 

  • User1165's avatar
    User1165
    Copper Contributor
    Sep 19, 2024
    Fixed the Problem. Apparently MSSQL Server no longer defaults to port 1433, but uses dynamic ports, starting at port 50027.
    To find the problem Went onto the running DB machine, opened up SQL Server Configuration Manager, went to SQL Server Network Configuration --> Protocols --> TCP/IP -->Properties and then IP Addresses, and clear at the bottom is running on Port 50027, so on my second node, I used <SERVER>\MSSQL,50027 and used SQL Server Authentication --> DB user:<User> Password:<pwd> remember password, and remember server certificate.. and it connected just like described. so not sure where port 1433 comes into play anymore. Problem solved.

Resources