Post reply

Logon Trigger, Restricting the connection to several IPs

  • April 20, 2012 at 4:21 am

    #272960

    Hi,

    Please let me know if this logon trigger which is used to Restrict the connection to several IPs will have performance issues, where the concurrent users are more.

  • April 20, 2012 at 4:46 am

    #1476523

    dakshinamurthy-655138 (4/20/2012)

    Hi,

    Please let me know if this logon trigger which is used to Restrict the connection to several IPs will have performance issues, where the concurrent users are more.

    You said this logon trigger, did you forrget to post your code, or did you need an example?

    Did you want to prevent traffic from specific IPs, or say limit the number of connections from a specific IP?

    Performance issues have very little to do with any of that, you'll really need to take a poorly performing query and review the execution plan for it. You can post the plan here for ssome top notch suggestions and peer review.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 20, 2012 at 5:09 am

    #1476534

    Hi,

    I am here with sending the code. Here i am trying to avoid others IP than the mentioned one in the code.

    CREATE TRIGGER tr_logon_hostname_whitelist

    ON ALL SERVER WITH EXECUTE AS 'sa'

    FOR LOGON

    AS

    BEGIN

    DECLARE @ClientHost nvarchar(max);

    SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');

    -- Insert into IPBLocks(ipaddress) Values(@ClientHost)

    -- ClientHost gives IP except if the connecting to SQL Server from instance machine.

    IF @ClientHost NOT IN ('<local machine>' -- client is on the instance box (Do NOT change or remove!)

    ,'19.16.1.2'

    ,'19.16.1.4'

    ,'12.18.2.14'

    )

    ROLLBACK;

    END;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply