Can not connect remotely to a second instance of SQL Server 2016

1
votes

In a server I have 2 instances, the first that is the default called SQLCompart on TCP port 1433 and the second called AISLANTES on TCP port 1435.

When I try to connect remotely to SQLCOMPART it works successfully, but just with the ip (it doesn't connect with named instance <IP>\<INSTANCE>).

So the problem here is that I can't connect remotely to AISLANTES.

I am trying with <IP>, <PORT> also with <IP>\<INSTANCE> and with <IP>\<INSTANCE>, <PORT>

The firewall inbound rule for tcp port 1435 is enabled.

What am I missing?

SQL Server Configuration Manager

enter image description here

SQL Server Configuration Manager TCP/IP properties

enter image description here

this is the error that i get SQL SERVER MANAGEMENT STUDIO SQLCMD

1
sql-serversql-server-2016
What are you using to connect? Show us the actual connect string or text of the one that works and the one that doesn’t work. Also the tool and/or code you are connecting with. Finally, what error message do you receive? – RBarryYoung
I edit the post with the images of the error, i am using SQL Server Management Studio and also tried with sqlcmd with command line – Diego Chilomer
Edit your images to hide your password, please. – RBarryYoung

1 Answers

1
votes

Possible reasons:

  • Port was changed, but instance was not restarted
  • Multiple firewalls and port 1435 is not opened on all of them
  • TCP for a second instance not enabled at all

Consider to check your latest SQL Server log and find something like:

2018-04-09 18:53:11.95 spid11s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2018-04-09 18:53:11.95 spid11s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2018-04-09 18:53:11.97 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-04-09 18:53:11.97 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].

So you will know for sure on which port SQL Server listens for incoming connections

p.s. if SQL instance installed on domain controller, it cannot use NETWORK_SERVICE as an account