Sql брандмауэр windows включен чтобы обеспечить удаленный доступ убедитесь что открыты порты

(Updated 2016-10-24)

Intro

If you want to allow remote computers on your network to access an instance of Sql Server running on one machine, you’ll have to add some new Incoming rules to the Windows Firewall, or no connections can come through. So, let’s show you what has to be considered and how to achieve this.

Sql Server Instance Types

Before you can configure the Windows Firewall to allow remote access to your Sql Server instance, it’s important to understand the “instance type” of the Sql Server that you are running.  For Sql Server, its “instance type” can either be the default instance, an unnamed instance, or a named instance. Which one you have determines which port you have to open in Windows Firewall.

• When SQL Server is installed as the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. This typically uses Port 1433 in the TCP protocol.
• A named instance is identified by the network name of the computer plus the instance name that you specify during installation (i.e. \\ComputerName\SqlExpress or something similar). The client must specify both the server name and the instance name when connecting. This typically uses the Sql Server Browser service to find the Sql Server instance, and that requires access to Port 1434 UDP protocol.

Windows Firewall Setup to Allow Remote Access

So, in order to allow remote access to the Sql Server instance, you will need to add these Incoming rules to the Windows Firewall:

1.  Add an Incoming rule to all access the application SqlServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Binn\sqlservr.exe)
2. If you want to access the Sql Server Instance from other computers by using the computer name (i.e. {ComputerName}\SQLEXPRESS, you will need to add an Incoming rule to allow access to the application SqlBrowser.exe (C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe).   Note: If you are only going to use the IP address {xxx.xxx.xxx.xxx}\SQLEXPRESS, then you will not need this rule for SqlBrowser.exe.
3. Add an Incoming rule for the correct port (1433 TCP or 1434 UDP depending on Sql Server instance type (from above)).

You can use these handy Poweshell scripts to add the above Firewall rules:

You should see the new rules here:

Картинка с сайта: mattslay.com

Sql Server Full version

For the full version of Sql Server (not Express), by default it installs as the default instance unless you specify an instance name. So, the next step to allow remote connections to the default instance is to add a Port Rule to open Port 1433 for the TCP protocol.

Sql Server Express

SQL Server Express always installs by default as a named instance unless you force a default instance during setup.  So, if you are running a named instance of Sql Server (or Sql Server Express), and you want to allow remote connections to it, add an Incoming port rule for Port 1434 for the UDP protocol.

Since I was running a named instance, I found that I did not need the 1433 TCP port rule at all, I only needed the 1434 UDP port rule (along with the two application rules) to get it working on my network.

Other steps to check

This post only covers the Windows Firewall. There are other steps required in configuring Sql Server itself:

• Enabling the correct protocols, and make sure Sql Server has the option “Allow remote connections to this server” checked on the Connections tab of the Server properties. You can access this setting in Sql Server Management Studio tool.

Картинка с сайта: mattslay.com

• Enable the correct protocols in the Sql Server Configuration Management Tool, under the Sql Server Network Configuration node. Honest, I wasn’t sure exactly which I needed, so I enabled all 3.  I’m pretty sure TCP/IP was disable by default.

Картинка с сайта: mattslay.com

Some details pulled from these links:

https://technet.microsoft.com/en-us/library/ms165614%28v=sql.90%29.aspx

and a comment by user J_m on this Technet article: https://technet.microsoft.com/en-us/library/ms175043%28v=sql.110%29.aspx