Pages

Wednesday, March 7, 2012

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.



No comments:

Post a Comment