SQL Protocols : Restrict IP Address to prevent invalid login attack to SQL Server.

Archives

Restrict IP Address to prevent invalid login attack to SQL Server.

Recently, we in customer scenario, amount of attacks come through making connection to SQL Server 2005 and trying invalid sa login. Under such a situation, SQL Server report error " login failed for user sa [CLIENT..]". It is quite annoying to get a lot of this kind of error under an attack and consequently impact the performance of SQL Server.

However, currently, SQL Server 2005 can not block client request based on IP Address automatically and it requires administrator to use one of the following operating system tools to workaround.

(1) Firewall

When enabling exception for SQL Server port, the administrator can specify which ip addresses/network to be allowed to connect to it. Currently Window Firewall is available on windows XP and windows 2003.

http://www.microsoft.com/technet/community/columns/cableguy/cg0204.mspx#ELC

(2) IP Sec

Configure IP Sec policy to block ip address from the attacker's machine. IPSec is available on windows 2000, XP and windows 2003.

http://support.microsoft.com/kb/313190

(3) RRAS IP Filter

Configure RRAS to drop the traffic from the attacker's ip address. RRAS is only available on windows server products. such as windows 2000 and windows 2003.

Refer to http://www.microsoft.com/technet/archive/winntas/proddocs/rras40/rrasch03.mspx?mfr=true

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Posted: Monday, April 10, 2006 5:35 PM by SQL Protocols

Comments

Ryan said:

Then what is the point of having a tcp parameter of LISTENER_IP = ? in the CREATE ENDPOINT TSQL statement for SQL Server 2005?

I wanted to use this to restrict access to all logins from any IP except my own. Why would this not work? When I trey the following:

CREATE ENDPOINT [CustomConnection]

STATE = STARTED

AS TCP

(LISTENER_PORT = 1500, LISTENER_IP = '192.168.1.10')

FOR TSQL() ;

I get an error at 192.168.1.10

WHY?

# November 25, 2006 12:25 AM

navharshal said:

i want to restrict certain ip address accessing my server.can any one fwd me the sql statement to do so?

# January 4, 2008 2:14 AM

alphatross said:

Ummm... this may be a couple of years late (just found this article), but I think your issue is to do with trying to use SQL's Endpoints to restrict a Client IP (your own) which according to this blog post you cannot do. The only valid value(s) for the LISTENER_IP value would be an IP Address configured for the *server* that SQL Server is on? If 192.168.1.10 is not configured on the server (but is the connecting Client's IP), then I'd say that's why you see an error.

# April 13, 2008 6:15 AM

SQL Protocols

Recent Posts

Tags