Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
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.
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.
(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.
(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
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
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
(LISTENER_PORT = 1500, LISTENER_IP = '192.168.1.10')
FOR TSQL() ;
I get an error at 192.168.1.10
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.
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.
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
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
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
inner join sys.trace_events e on t.eventclass = e.trace_event_id
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.
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
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 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.