How to create user for spesific IP?

[riupie]

Hi,

Like the title said, how to achieve my requirement? On another database server, for example MySQL, we can define it when creating user:

CREATE USER 'bob'@'10.1.1.1' IDENTIFIED BY 'password123';

Or, in  PostgreSQL we can define it on pg_hba.conf.

Is it possible on SQL Server? If possible, how to do that? FYI, I use Microsoft SQL 2016.

[#2]

In MS SQL Server it's not possible.

[#3]

Hi @riupie 

First you need to remember that in SQL Server, a USER which you speak about is NOT the same as LOGIN which is probably what you want since you mention MySQL structure like 'bob'@'10.1.1.1'.

A Login is an instance level identity which is used to connect to a SQL Server instance. SQL LOGIN is for Authentication.

A User is a database level entity which allows you to log into specific database. SQL Server USER is for Authorization. A USER can be mapped to a LOGIN but not necessarily. A USER which is not mapped to LOGIN (guess what?) cannot login the server. MySQL does not have the same approach.

Secondly, in MySQL not all of this 'bob'@'10.1.1.1' is the USER, but only the 'bob' part.

When execute the following command in MySQL in order to create a USER

> CREATE USER 'bob'@'10.1.1.1' IDENTIFIED WITH authentication_plugin BY 'password';

CREATE USER: Command

'bob' is the username.

'10.1.1.1' is the hostname - computer networking name or ip or domain for example, from which this user will connect.

This specific exact format is not supported in SQL Server

But there are multiple options to get what you are asking for

Option 1: Using external Firewall

The approach of managing source host during LOGIN is done in SQL Server using external firewall. You simply need to configure the operating system firewall to allow specific IP for example.

Pros: This is simple and most common and recommended for most cases

Cons: SQL Server LOGIN not always related to OS USER and in such cases you cannot configure specific SQL Server LOGIN in the firewall. This mean that you can limit IP but this will work on all people that try to connect and not specific SQL Server LOGIN.

Option 2: Using trigger

This will provide exactly what you asked for.

You can use a TRIGGER on LOGIN which will check the LOGIN name and hostname he use to connect from. If these not in the list (can be for example set of rows in a table or hardcoded list), then you prevent the login

CREATE TRIGGER ConfirmIP4LOGIN 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

 For more information regarding connection properties which you can use check this doc: 

https://docs.microsoft.com/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-ver16...