Post reply

Performance Issues - Want to Stop All Incoming Connections for Specified Hosts

  • March 24, 2014 at 11:03 am

    #283753

    Without modifying firewall settings, is there a way to refuse connections from withing sql server from certain hosts?

  • March 24, 2014 at 11:23 am

    #1699630

    You may want to look at DDL triggers, specifically LOGON triggers. Be careful when setting up a LOGON trigger as you could accidently block everyone from login into the server.

  • March 24, 2014 at 1:00 pm

    #1699661

    here's a rough example of a logon trigger;

    like Lynn said, you can easily lock everyone out, including yourself, with a badly written trigger, so don't disconnect the SSMS query window you use to create the logon trigger

    CREATE TRIGGER logon_trigger_not_FromTheRightPC

    ON ALL SERVER FOR LOGON

    AS

    BEGIN

    IF host_name() IN('Developer-Bob','Developer-Bill','BizGuy-Ted')

    --raise an error, which goes to the error log

    RAISERROR('Unauthorized use of login from inpermissible host.', 16, 1)

    ROLLBACK

    END

    ENABLE TRIGGER logon_trigger_not_FromTheRightPC ON ALL SERVER

    /*

    DISABLE TRIGGER logon_trigger_not_FromTheRightPC ON ALL SERVER

    DROP TRIGGER logon_trigger_not_FromTheRightPC ON ALL SERVER

    */

    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!

  • March 24, 2014 at 1:35 pm

    #1699670

    This is something I haven't toyed with yet and looks very helpful. Thanks so much!

Viewing 4 posts - 1 through 4 (of 4 total)

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