SQL Server : how to create user with access only from specific HOSTNAME or IP addres 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).
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 &lt;&gt; '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.