2

My SQL server 2012 log indicates multiple bruteforce attempts to logon to the server.

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: xxx.xx.xx.xx]

The account has a very secure password, which buys time for now.

It was recommended that I use a trigger to prevent ip logins. However, this is proving ineffective. How do I put an end to this issue.

CREATE TRIGGER IPtrigger
    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 ( 'xxx.xx.xx.xx' )
    BEGIN
        Print 'IP BANNED FROM LOGON: ' + @IP
        ROLLBACK TRANSACTION
    END
END
GO

migrated from stackoverflow.com Dec 3 '15 at 18:45

This question came from our site for professional and enthusiast programmers.

  • 9
    Why is your SQL server reachable publicly in the first place? Firewall/infrastructure should protect it from such attempts in my opinion. – FDIM Dec 3 '15 at 11:10
  • 7
    Instead of blacklisting you should use whitelisting (allow only specified IP addresses/ranges). Then you have to make that a firewall rule[set], not SQL server login trigger. Additionally it is advisable to change SQL listening port - little less probability to find it during automatic scan. – Arvo Dec 3 '15 at 11:19
  • 3
    This problem should be dealt with at network level, rather than in SQL Server. If you can describe your network layout a little, there will be an easier solution. – Stoleg Dec 3 '15 at 11:21
  • 2
    Inbound traffic is blocked using a Draytek router with firewall. I have created multiple firewall rules for IP addresses as they arise, but new IP addresses keep emerging. – AMorton1989 Dec 3 '15 at 11:25
  • 1
    I am also apprehensive about changing the SQL server default port due to configuration of IIS servers. – AMorton1989 Dec 3 '15 at 11:26
1

You can disable SA account and wait until attack ends. This is not a solution for the problem, but can buy some time to reconfigure your network.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
 GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
 GO 
  • 2
    The attack will not end. Script kiddies using brute force attacks against SQL Server have been around for over 20 years. – Dan Guzman Dec 3 '15 at 12:30
  • 1
    @DanGuzman, I agree that as long as they have access to the network they wouldn't stop. They will change vector of attack to something else until the door is closed. – Stoleg Dec 3 '15 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.