Jun 08 2022 06:00 AM
Jun 08 2022 06:00 AM
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.
Jun 09 2022 12:07 AM
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.
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
DECLARE @ip_addr varchar(48)
SELECT @ip_addr = client_net_address
WHERE session_id = @@SPID
IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1'
For more information regarding connection properties which you can use check this doc: