How SQL Server restricts IP landings: application of landing triggers

I. BACKGROUND

In the MySQL mysql.user table has saved the login user's permission information, the host and the user field is regarding the landing IP limit. But without such a table in SQL Server, what is the way SQL Server can achieve similar security-control capabilities?

SQL Server includes three common types of triggers: DML triggers, DDL triggers, and logon triggers. DML triggers are more commonly used, and typically modify data (INSERT, update, delete, and so on) in a table or view to ensure the integrity and consistency of business data, you can rollback transactions, and so on; If you are interested in DDL triggers, you can refer to: SQL Server DDL triggers are used, which involve knowledge of DDL triggers, and landing triggers will be used in this article in the solution of IP landing restrictions.

What problems can landing triggers solve for us? This article will tell you 5 different scenarios for applying a login trigger:

1 Limit a login name (such as SA) only in the local or designated IP login;

2 Limit the server role (such as sysadmin) only in the local or designated IP login;

3 Limit a login name (such as SA) can only be landed within a certain period of time;

4 limit the corresponding relationship between the login name and IP, and support the many-to-many relationship;

5 Limit a login can be in an IP segment login (such as 192.168.1.*), the following figure;

Second, the implementation process

(i) The IP of my machine is: 192.168.1.48, first I create a test account in the database, set the password to 123, then create landing trigger: Tr_connection_limit, it will be triggered when the user login, through the EVENTDATA () function returns the IP of the client, using the login name returned by the Original_login () function to determine the IP and login name.

When the login name is test, if the login IP address of the local <local machine> or 192.168.1.50,192.168.1.120 is allowed to log in, the other circumstances of the landing will be rolled back. Login failed as shown in Figure1.

--SCRIPT1: Creating test login account Create
login Test with PASSWORD = ' 123 '
go
    
--====================================== =======
--Author:        < Listen to wind and rain >-
-Create Date:    <2013.05.21>
--Description:    < Limit test users can only log on to the local and designated IP >-
Blog:        
 
 
(figure1:test user Login error message)
 
I logged on to my SQL Server database using the test login on an IP-192.168.1.115 machine, because this IP was not in the allowed IP list, so there was a Figure1 error message. I use an IP for 192.168.1.120 machine to log into my SQL Server database, successfully logged in, use SCRIPT2 to return the landing information, such as Figure2, please see the record of session_id 58: Login named Test, The IP that landed is 192.168.1.120.
 
--SCRIPT2: Return to login information
SELECT
a.[session_id],a.[login_time],a.[host_name],
a.[original_login_name],b.[ Client_net_address] from
MASTER.sys.dm_exec_sessions a 
INNER JOIN MASTER.sys.dm_exec_connections b 
on a.session_id=b.session_id
 
 
(Figure2: User login information)
 
For the use of EXECUTE as in the Script1 script, refer to: EXECUTE AS (Transact-SQL), Original_login () function can refer to: original_login (Transact-SQL), EventData () function usage can refer to: EVENTDATA (Transact-SQL)