SQL Server : how to create user with access only from specific HOSTNAME or IP addres

Question

I have one stupid app which I rely on, which has hardcoded connection string inside.

To increase security of my SQL Server I'd love to make SQL users same as one hardcoded inside app, but I want to allow that user to be able use SQL Server only from certain host (ip address).

Answer 1

You can use a Logon Trigger for that.

CREATE TRIGGER TR_check_ip_address
ON ALL SERVER
FOR LOGON
AS 
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1'
        ROLLBACK;

END

If you try to connect from an unauthorized IP, you'll get an error:

Logon failed for login 'bob' due to trigger execution.

Remember that Logon Triggers are potentially evil and might also end up locking everyone out of the instance. Be careful!

However, I think you don't really need to do that. If you want to enable connections from a list of known addresses, a firewall is the most suitable tool for the job. The worst thing that could happen is the wrong user connecting from a known IP address, which is quite unlikely if users keep their credentials carefully.

Also, take into account that IP addresses can be spoofed, so I'm not sure which degree of additional security you would be providing.

Answer 2

You can achieve this using logon trigger as following

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 my blog connectsql.com