Sql-server – How to remotely connect SQL Server 2016. Since we have 2012 and 2016 in same server

instancesql serversql-server-2012sql-server-2016

Issue

I am not able to connect to a SQL Server 2016 instance on a remote system/server.

My Existing Environment

Windows Server 2012
SQL Server 2012 and 2016 installed on same server

I tried to enable a firewall rule for SQL Server 2016 with port 1433. But it is not working.

Same thing I tried with SQL Server 2012 it is all working fine.

Settings I Have Tried

Enabled remote connection in SQL server.
Created Firewall rule to browse SQL Server.
Enabled TCP Port.
Enabled default port 1433.

Error Message I Am Receiving

It would be great if anybody could help me with this.

Best Answer

Sometimes you need to open firewall rule for UDP 1434 SQL Browser port. Be sure SQL 2012 and 2016 don't use same TCP port (1433). If multiple instance on same machine they should be configured to use separate TCP port.

Related Solutions

Sql-server – Cannot connect remotely to sql server 2012 instance using SSMS

You should not need to specify the port for a named instance (that's kind of the point of the name - and since it's dynamic you won't want to hard-code a port into your connection strings etc. unless you want to change your config files every time the port changes). Try connecting to:

123.123.123.123\SQLEXPRESS

Also make sure that your server is exposed to the Internet on that port (firewalls may be preventing this) and that the SQL Browser service is running.

PS Ping means nothing. You need to try to telnet to the port that SQL Server is exposed on.

SQL Server 2012 – How to Create a Server Alias

Another option is to add additional IP addresses to the machine (does not require an additional network card to the best of my knowledge) each one with a different DNS entry. So now you can connect to that machine using multiple names. Then you set up the IP address in the TCPIP settings for the instance (using configuration manager). Each of those IP Address/Machine Names has a port 1433 and that port can be assigned to extra instance.

Once you are done you have MachineName1\Live and MachineName1\System instances. MachineName1\Live is also accessible by MachineLiveAlias and MachineName1\System accessible by MachineSystemAlias.

Here is a link with fairly detailed set of instructions http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html