1

I installed AlwaysOn on my laptop (testlab) with three VMs, of which one is a domain controller and two are hosting SQL Server instances (planned to be one primary and one secondary replica). Everything went well until I wanted to add the secondary replica to the Availability group.

Please find a screenshot attached. I have already changed the port to 1433 in the TCP/IP settings and have also added an exception for SQL Server in the Windows Firewall. Both of the servers are in the same cluster network. Not sure what else I am missing here.

enter image description here

  • This is a connection string issue or appropriate port is not open. Can you try to login into SSMS not on its box (server) but on your local machine using the same connection string win-…..\SQL02 ? – Hailegziabher Dechassa Sep 5 '16 at 4:07
  • Can you go to the secondary replica you are adding. Now open SQL Server configuration manager and on left pane click on SQL Server network protocols. Now check whether TCP/IP protocol is enabled or not. Make sure it is enabled. – Shanky Sep 5 '16 at 4:25
5

Did you add the browser service to the firewall? UDP 1434. Because you're using instance names so they have to get resolved. Otherwise remove the instance name to use that 1433 port.

  • Thank you. I think adding the UDP port to the firewall exception solved the issue! Thanks a ton! – user2923332 Sep 5 '16 at 4:29
0

I use netsh to open as many (or few) standard SQL Ports in the Windows firewall. Run this in an Administrator cmd prompt or powershell. I forget where I "borrowed" this from but someone else had posted this somewhere.

REM =========  SQL Server Ports  =================== 
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 profile = DOMAIN
netsh advfirewall firewall add rule name = SQLAdmin dir = in protocol = tcp action = allow localport = 1434 profile = DOMAIN 
netsh advfirewall firewall add rule name = SQLBroker dir = in protocol = tcp action = allow localport = 4022 profile = DOMAIN 
netsh advfirewall firewall add rule name = SQLRPC dir = in protocol = tcp action = allow localport = 135 profile = DOMAIN 
netsh advfirewall firewall add rule name = SQLBrowser dir = in protocol = udp action = allow localport = 1434 profile = DOMAIN 
REM =========  Reporting Services Ports  ==============
netsh advfirewall firewall add rule name = SQLRptSrvcHTTP dir = in protocol = tcp action = allow localport = 80 profile = DOMAIN 
netsh advfirewall firewall add rule name = SQLRptSrvcHTTPS dir = in protocol = tcp action = allow localport = 443 profile = DOMAIN
REM =========  Analysis Services Ports  ============== 
netsh advfirewall firewall add rule name = SQLAnalySrvc dir = in protocol = tcp action = allow localport = 2383 profile = DOMAIN 
netsh advfirewall firewall add rule name = SQLAnalySrvcBrwsr dir = in protocol = tcp action = allow localport = 2382 profile = DOMAIN

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.