locked
How to create a condition and policy to deny access based on host name RRS feed

  • Question

  • Hello,

    With SQL Server 2008 Enterprise, can I prevent an Active Directory Account Login ( domain\user ) from connecting if its coming from a host_name?

    What I really need to do is this:  

     

    On SQL1, login mydomain\user1 is only allowed to login from servers APP2, APP3, and APP4.

     

    If mydomain\user1, though it has a login on SQL1 attempts to connection any other hostname besides APP2, APP3 or APP4 I want to deny it access to SQL1.

     

    Can anyone point me in the right direction for the correct condition(s) for a policy that would work?

     

    Thanks


    Erik A. Grob, MCITP, MCDBA
    Tuesday, October 5, 2010 3:17 PM

Answers

  • Careful here. Clients can lie about their connection parameters.  For example, SQLCMD from host App1 can say it is on App2 and connect with

    SQLCMD -S SQL1 -H App2

    That is, the host name argument can be anything the user wants to put in there. But of course, some things are hard to lie about, such as domain credentials. I couldn't fool Mohit's example, but I didn't try very long, and I don't know all the tricks.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by Tom Li - MSFT Wednesday, October 13, 2010 12:04 PM
    Tuesday, October 5, 2010 9:07 PM

All replies

  • Maybe this will do the trick for yaa...

    CREATE TRIGGER hostmonitortrigger
    ON ALL SERVER
    FOR LOGON
    AS
    BEGIN

        DECLARE @data XML
        DECLARE @UserName nVARCHAR(2000)
        DECLARE @hostName nVARCHAR(2000)
       
        SET @data = EVENTDATA()

        SET @UserName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(2000)')
        Set @hostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(2000)')
       
        IF @UserName LIKE 'test' AND
           @hostName LIKE '10.11.2.16'
        BEGIN
            RAISERROR ('Connection not allowed from this location',10,1);
            ROLLBACK;
        END
       
    END;
    go


    Thanks. Mohit. -------------------------------------- MCTS: SQL Server 2005, MCITP: Database Administrator. http://sqllearnings.blogspot.com/
    • Proposed as answer by Tom Li - MSFT Thursday, October 7, 2010 12:21 PM
    Tuesday, October 5, 2010 3:49 PM
  • Careful here. Clients can lie about their connection parameters.  For example, SQLCMD from host App1 can say it is on App2 and connect with

    SQLCMD -S SQL1 -H App2

    That is, the host name argument can be anything the user wants to put in there. But of course, some things are hard to lie about, such as domain credentials. I couldn't fool Mohit's example, but I didn't try very long, and I don't know all the tricks.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by Tom Li - MSFT Wednesday, October 13, 2010 12:04 PM
    Tuesday, October 5, 2010 9:07 PM
  • I was thinking the same thing Rick.  But HostName is IP of the originating client so I think it is safe.  But don't know what information is in the EVENT_INSTANCE, or where it captures that sorry.
    Thanks. Mohit. -------------------------------------- MCTS: SQL Server 2005, MCITP: Database Administrator. http://sqllearnings.blogspot.com/
    Tuesday, October 5, 2010 10:09 PM