Prevent a host from connecting to SQL Server

Normally I would block a network device using the windows firewall; however, when that option is not available you can can still block the device from SQL server. This SQL Script will create a DDL trigger that will reject connections attempts from a specific network client based on the host name.

CREATE TRIGGER tr_Reject_Connection ON ALL SERVER

    WITH EXECUTE AS self

    FOR LOGON

AS

BEGIN

    IF 'Client Host Name' = HOSTNAME()

        BEGIN

            ROLLBACK;

        END

END;

GO

ENABLE TRIGGER tr_Reject_Connection ON ALL SERVER

GO

To Reject a client based on the IP address, change the IF statement to read:


     IF 'Client IP Address' = ConnectionProperty('local_net_address')


In addition, these events are automatically logged in the SQL Log file, for example:



       Date            Source                    Message
-------------------  ----------  ------------------------------------------------------------------------------------------------
03/07/2012 16:08:37 Logon    Logon failed for login 'ADOMAIN\TestUser' due to trigger execution. [CLIENT: 192.168.78.190]
03/07/2012 16:08:37     Logon    Error: 17892  Severity: 20   State: 1.
03/07/2012 16:08:37     spid104  The transaction ended in the trigger. The batch has been aborted.
03/07/2012 16:08:37     spid104  Error: 3609   Severity: 16   State: 2.