2

We have an SQL Server database which is getting bombarded each day with login attempts. It is on an online server and I am sure you'll frown upon the fact that it is accessible to the outside world but I cannot do much about that (but am open to suggestions).. I have found a way by searching online to restrict log ins by IP address in the following code.. I like the idea of this code running but I want to only run the check if the log in is unsuccessful.. does anyone know what is returned if unsuccessful?

Many thanks,

Derek

-- -- Block IP Addresses to SQL Server using a Logon Trigger -- 
CREATE TRIGGER LogonTrigger_RestrictIpAddresses     
ON ALL SERVER     
FOR LOGON AS
BEGIN    
DECLARE @IP Varchar( 500 )     
SET @IP = EVENTDATA().value( '(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)' )           
-- set of the restricted IP addresses     
IF @IP IN ( '172.16.255.11', '172.20.254.1', '172.26.254.12' )     
BEGIN        
Print 'Logging in from the restricted IP: ' + @IP         
ROLLBACK TRANSACTION    
END
END
GO
PravinS
  • 2,640
  • 3
  • 21
  • 25
Derek Jee
  • 147
  • 1
  • 14
  • 5
    Kill the attempts before they reach the server, preferably in your router or hardware/software firewall – Alex K. Apr 20 '15 at 13:02
  • 2
    You can do this in SQL, but you should really use a firewall for this – Alex Apr 20 '15 at 13:03
  • 1
    Yeah, it does seem overwhelmingly so that the router should do this, and I do agree very much.. Thank you Rahul. I will look at your suggestion as well as how we can get the router involved.. – Derek Jee Apr 20 '15 at 13:08
  • 2
    It would seem unlikely that you need access from the entire Internet and the ability to block a few addresses. It is much more likely that you should block the entire Internet and whitelist only the approved IP's. And yes, it is better to do this in your firewall/router or in the Windows firewall. – Gary Walker Apr 20 '15 at 13:09
  • Thank you Gary, I wish they thought of this 7 years ago when they developed this process. I'd love a whitelist scenario.. Thanks again.. – Derek Jee Apr 20 '15 at 13:11
  • One big oversight is that the logon triggers only fire if they are successful and those which I am trying to capture are not.. Firewall time it is then.. Thanks for your help all.. – Derek Jee Apr 21 '15 at 09:45

0 Answers0