SQL Server Remote Connection enable for specified IP Address

Question

I have a Windows Server 2016. In this server I have a SQL Server 2016 installed.

I must configure the sql server to allow remote connections because there are some databeses which synchronise with our local databases.

I do this by openning the SQL Server Configuration Manager, selecting SQL Server Network Configuration and enabling the TCP/IP protocol.

But, this causes a security gap. I realised that the SQL Log File C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log is constantly increaising the size.

By analysing the log file, I realised that there are some IP addresses tries to connect constantly with a wrong password (15 times per minute).

As I need to keep open the TCP/IP protocol, I tried to add an inbound firewall rule to the firewall but it doesn't change anything.

First, I tried to disable the TCP/IP and authorise connections from a defined IP. It didn't work. Then, I tried to enable TCP/IP and block the port 1433 for any IP but it doesn't block. I can connect from a local pc.

Briefly, if I enable the TCP/IP, the remote connection is enabled to any IP. If I disable the TCP/IP, the remote connection is disabled to any IP. It doesn't consider the firewall rule.

Is there a way to enable the remote connection for a specific IP Address ?

Here is what I do to block the 1433 port (But I am allways able to connect via SSMS from a local computer ( which is in another domain ) :

Answer 1

I faced recently the same issue and fixed it by setting customised rule in windows Firewall:

1) Enable TCPIP, otherwise no connection will be possible

2) then you should use the windows firewall as follows:

• first Block the port 1433 for any inbound connection
• then add a customised rule to port 1433 for which you authorise connections from only specific Ip address, you can follow the steps explained in this link

Here is a screenshot of the firewall setting I have

Here is a screenshot of the port blocking setting I have