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

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

  • Comments 10

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

     

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
  • 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?

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

  • 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.

  • Hi,

    As per above mentioned solutions, I always follow IPSec to protect Server from SA attacks.

  • how to block specific ip in sql.

  • Is there any way to audit this kind of invalid logins attempts at database level, i mean if there is an invalid login attemp how can I keep that information in a table in my database.

    Awaiting for a help.

    Thanks...

  • Prashant, I use this:

    --select * from sys.traces where is_default = 1

    declare @path VARCHAR(512)

    select @path = path from sys.traces where is_default = 1

    SELECT loginname, IP, Max(starttime) as LastErrorTime, COUNT(*) AS AttempCounts

    FROM (

    SELECT t.loginname, REPLACE(SUBSTRING(t.textdata, CHARINDEX(':', t.textdata, CHARINDEX(':', t.textdata, 1) + 1) + 1, 16), ']', '') AS IP, t.starttime

    FROM dbo.fn_trace_gettable(@path, DEFAULT) AS t INNER JOIN

    sys.trace_events AS e ON t.eventclass = e.trace_event_id

    WHERE (eventclass = 20)) AS A

    GROUP BY loginname, IP

    order by LastErrorTime desc

    select distinct  t.hostname

    from fn_trace_gettable(@path, default) t

    inner join sys.trace_events e on t.eventclass = e.trace_event_id where eventclass=20

    execute sp_who2

    select e.name as eventclass, t.loginname,

    Replace(SUBSTRING (t.textdata,CHARINDEX(':', t.textdata, CHARINDEX(':', t.textdata, 1)+1)+1,16),']','') as IP ,

    t.textdata, t.hostname, t.ntusername, t.ntdomainname, t.clientprocessid,

    t.applicationname,  t.spid, t.starttime, t.error

    from fn_trace_gettable(@path, default) t

    inner join sys.trace_events e on t.eventclass = e.trace_event_id

    where eventclass=20

    order by starttime desc

  • 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

  • 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,

  • 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

Page 1 of 1 (10 items)