Wednesday, July 18, 2012

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.

6 comments:

  1. This is excellent blog post - I indeed learning something new today. Thanks Aasim.

    ReplyDelete
  2. Thanks Guru... You are my inspiration

    ReplyDelete
  3. Great. But could we restrict both user and IP ? Like MySQL.
    How can we allow Bob login any where, Jack login only with IP 1.2.3.4

    ReplyDelete
  4. Thank you nice article , how can we restricte using userID and IP ?

    ReplyDelete
    Replies
    1. Script already handling IP case, and for User use "/EVENT_INSTANCE/LoginName)[1]"

      Delete

All suggestions are welcome