SQL Server: Restrict Login from Valid Machine IPs Only (Using Logon Trigger)

Today, I have practically learned how to stop valid database users to login on SQL Server instance from invalid machines (IPs).

Process is very simple. Just create a Logon Trigger and check if login user is coming from valid IP or not. If not, then just kick him out.

Download Script

USE master

GO

-- Create table to hold valid IP values

CREATE TABLE ValidIPAddress (IP NVARCHAR(15)

CONSTRAINT PK_ValidAddress PRIMARY KEY)

-- Declare local machine as valid one

INSERT INTO ValidIPAddress

SELECT '<local machine>'

-- Create Logon Trigger to stop logins from invalid IPs

CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

    FOR LOGON

AS

    BEGIN

        DECLARE @IPAddress NVARCHAR(50) ;

        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                           'NVARCHAR(50)') ;

        IF NOT EXISTS ( SELECT  IP

                        FROM    master..ValidIPAddress

                        WHERE   IP = @IPAddress )

            BEGIN

            -- If login is not a valid one, then undo login process

                SELECT  @IPAddress

                ROLLBACK --Undo login process

            END

    END

Once trigger is created, you can find it under Server Objects -- > Triggers tab

From invalid IP, which you have not added in secure list will see following error on log-in attempt.