Compartir a través de

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

  • Artículo

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.

https://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.

https://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 https://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

Comments

  • Anonymous
    November 24, 2006
    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?

  • Anonymous
    January 03, 2008
    i want to restrict certain ip address accessing my server.can any one fwd me the sql statement to do so?

  • Anonymous
    April 12, 2008
    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.

  • Anonymous
    September 26, 2008
    Hi, As per above mentioned solutions, I always follow IPSec to protect Server from SA attacks.

  • Anonymous
    February 18, 2010
    how to block specific ip in sql.

  • Anonymous
    March 16, 2010
    The comment has been removed

  • Anonymous
    August 11, 2010
    The comment has been removed

  • Anonymous
    February 26, 2011
    For anyone looking for an program that will create the IPSEC policy, filters, etc and automatically scan the event log and add IP's to the block list, I've written a small program that does just that. I had this problem too where my event log would be filled with thousands of entries for hackers attempting to login to my MSSQL instance with the 'sa' login.  After much searching, I decided to write my own program, have it create the neccessary IPSEC items, and then scan the event log every 60 seconds for attacks from new IP addresses.  It then adds the IP address to the IPSEC filter, and blocks all traffic to and from the IP.  I have only tested this on Windows Server 2008, but believe it will work on other versions as well. Feel free to download the program using the link below.  Donations are always appreciated using the link in the right-click menu of the taskmanager icon. www.cgdesign.net/.../AutoBlockIp.zip Please note that this only works for SQL login attempts using the 'sa' login, but I could modify it to work for other log events as well.  Also, you can view the IP's that have been blocked, but you will continue to see some items in the event log since the program only runs every 60 seconds.  This is due to not being able to delete a single event log entry, and I didn't think deleting the entire log would be a good idea. DISCLAIMER - By downloading and installing the above-mentioned program, you agree to hold me harmless for any damage, loss of data, corruption, or any other functionality issues resulting from the use of said software.  I have tested the program to the best of my ability and currently have it running on 2 servers, but you have been warned to use at your own risk. Any questions or comments, please feel free to get in touch with me using the contact form on my website at www.cgdesign.net -Chris

  • Anonymous
    April 06, 2011
    I keep getting these errors poping up when i am using your autoblockip program... System.InvalidOperationException: Cannot open log Application on machine .. Windows has not provided an error code. ---> System.ComponentModel.Win32Exception: Access is denied   --- End of inner exception stack trace ---   at System.Diagnostics.EventLogInternal.OpenForRead(String currentMachineName)   at System.Diagnostics.EventLogInternal.GetEntryAtNoThrow(Int32 index)   at System.Diagnostics.EventLogEntryCollection.EntriesEnumerator.MoveNext()   at AutoBlockIP.Form1.timer1_Tick(Object sender, EventArgs e)   at System.Windows.Forms.Timer.OnTick(EventArgs e)   at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,

  • Anonymous
    May 24, 2012
    Disco - You need to run the app with administrator rights, or as a user with privilages to access the Windows Event Log files. On a side note I have modified the app to search for both "sa" and "admin" logins since those appear to be the most prevelant on my own servers.  Also for CPU performance issues it now automatically archives a copy of the Event Log to the "C:WindowsEventLogArchive" folder and then clears the existing log once there are more than 20,000 entries. It appears that this may turn into an ongoing project, so if anyone has any feature enhancements let me know.  I can't promise that they will make it into a new release, but I'll see what I can do.  Also there is a donate link in the right-click menu of the taskbar icon.  Not saying that you have to, but if you like the app consider donating a few dollars to the cause. -Chris

  • Anonymous
    August 10, 2015
    Diagnostic Info IP Address: 41.103.70.251 Server: am