3

Issue

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

My Existing Environment

  1. Windows Server 2012
  2. 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

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

Error Message I Am Receiving

Error Message - Connect to Server

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

Improve this question
10
  • Try checking on services.msc if both the SQL SERVER 2012 and 2016 are running
    – jun drie
    Jul 17, 2018 at 6:55
  • 2
    It's trying to establish a Named Pipes connection. The SQL Server TCP settings/port are irrelevant if that's the type of connection you want to establish
    – Damien_The_Unbeliever
    Jul 17, 2018 at 6:55
  • 1
    you can't have both SQL Server listening on same port 1433. Use different port no or use dynamic port
    – Squirrel
    Jul 17, 2018 at 6:57
  • 1
    @ Damien_The_Unbeliever, he is not trying to connect via Named Pipes. All the protocols are tried in order Shared Memory, TCP/IP, Named Pipes. The last one just reports the error
    – sepupic
    Jul 17, 2018 at 7:22
  • 1
    @Sharad you'll find a lot of duplicate questions. There's nothing special about SQL Server 2016, even Express. It's either firewall, wrong address or wrong instance name. If mulitple versions are installed on the same server you probably need the SQL Server Browser service as well, otherwise you'll have to specify different ports during installation
    – Panagiotis Kanavos
    Jul 17, 2018 at 7:38

3 Answers 3

Reset to default
1

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.

Improve this answer
0

You have a named instance so if you want to use its name, SQL Server Browser should run. But the correct exception in Firewall still should be created so you should know the correct port number.

Now open SQL Server error log (in Notepad, via SSMS under Management node or by using xp_readerrorlog), one of the first rows in the error log reports the port on which your instance is listening on. For this port you should create the exception.

The error cited on one hand says that the instance cannot be found, if it is so finding the correct port number will resolve your problem.

On the other hand it says "the user name or password is incorrect", if this is true, you'll see it among the last rows of error log.

So in any case, the key is in the error log.

Improve this answer
2
  • I cant see any error log. Because I am trying to connect in remote machine.
    – Sharad
    Jul 17, 2018 at 9:31
  • But how could you configure SQL Server port and firewall exception if you cannot access this remote pc? I suppose you used RDP
    – sepupic
    Jul 17, 2018 at 9:33
0

You might want to have a look at my extensive question and answer regarding

Depending on circumstances not yet aware to you, you might have better chances of connecting to your instance using a different variation of the servername\instancename:port string.

Possible variations instead of Server\Instance would be:

  • 123.1.2.4,1433 <== using the SQL server's IP address and instance port number
  • B4EDEV01,1433 <== using the SQL Server's host name and instance port number
  • ADMIN:123.1.2.4\SQLEXP2016 <== DAC connection with IP address and port number

Depending on your network settings in the SQL Server Configuration Manager for the 2016 instance, you might have to use different port numbers and/or IP addresses.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.