8

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).

2 Answers 2

6

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.

2
  • 2
    As @spaghettidba said logon triggers are potentially really bad news. If you run into trouble read this sqlstudies.com/2014/05/19/escaping-from-a-runaway-logon-trigger. I went over methods of bypassing the trigger, finding it, and disabling it. Dec 3, 2014 at 18:27
  • 1
    He might want to allow anyone other than the app user to log in from any address, but the application can only run from one server so the app user can only log in from that address. Few firewalls can handle that. Dec 10, 2014 at 5:21
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.