使用登录触发器限制SQL Server登录身份验证范围

This article contains the SQL Server login Authentication scope on the Security side. We will find the subtleties, how Login Authentication extension can be controlled SQL Server level Logon trigger.

本文在安全方面包含SQL Server登录身份验证范围。 我们将发现微妙之处，即如何可以控制SQL Server级登录触发器控制登录身份验证扩展。

Common SQL Server Principals are clients and Logins and that is constrained by authorizations with GRANT and DENY. SQL Server database engine will authorize the login by authentication request from any Query Management Studio(SSMS), Application Integration or some other apparatuses.

常见SQL Server主体是客户端和登录名，并且受GRANT和DENY的授权约束。 SQL Server数据库引擎将通过来自任何Query Management Studio（SSMS），Application Integration或某些其他设备的身份验证请求来授权登录。

Development, QA or stage servers are fine to permit all login to Authenticate SQL Server utilizing diverse customer applications yet for Production servers, we can’t. SQL Server Logins ought to be limited with an entrance approach to authorized authentication for Client Applications. In this article, we will experience in detail to confine the SQL Server login scope with predefined rules for the approval of customer’s / client applications.

开发，QA或阶段服务器可以允许所有登录使用不同的客户应用程序对SQL Server进行身份验证，但是对于生产服务器，我们不能。 SQL Server登录应该使用进入客户端应用程序的授权身份验证的入口方法进行限制。 在本文中，我们将详细体验如何使用预定义的规则来限制SQ​​L Server登录范围，以批准客户/客户端应用程序。

Essentially, Database Engineers will have the consented access to utilize the SQL Server database engine as a level of Designation. As the Industrial standard, organization will make individual login for database users to get to access on production; Now, each application could have diverse SQL login so as to communicate with a database, be that as it may, the SQL login ought to be authorized by the database engine utilizing customers\client application just and not just by utilizing any Query management studio or tools.

本质上，数据库工程师将获得同意的访问权限，以将SQL Server数据库引擎用作指定级别。 作为行业标准，组织将对数据库用户进行单独登录以访问生产环境； 现在，每个应用程序可以具有不同SQL登录名，以便与数据库进行通信，无论如何，SQL登录名应该仅由数据库引擎利用客户\客户端应用程序来授权，而不仅仅是通过使用任何Query Management Studio或工具。

The various types of SQL triggers, for example, DDL(Data Definition Language), DML(Data Manipulation Language) and Logon triggers are upheld by Microsoft SQL Server.

Microsoft SQL Server支持各种类型SQL触发器，例如DDL（数据定义语言），DML（数据操作语言）和登录触发器。

Authorization rule metrics can be defined in Logon trigger to command over SQL login to Enhance the Security of the Database. See here, we have attempted to clarify various situations having been connected different rationales in Logon trigger with a model:

可以在登录触发器中定义授权规则指标，以命令SQL登录以增强数据库的安全性。 看到这里，我们试图阐明将登录触发中的不同原理与模型联系在一起的各种情况：

We are to give a shot with the logon triggers such that it would make a hazard to the trigger ought not to follow up on as likely it ought to and it would not allow finishing the login procedure on the SQL server. See here, to improve security, in this kind of situation the Database administrator ought to be reached so as to determine the issue and to have the option to reconnect to the SQL Server by altering or dropping the Logon trigger.

我们要对登录触发器进行一些尝试，以免对触发器造成危害，因此不应跟进，并且不允许在SQL Server上完成登录过程。 请参阅此处，以提高安全性，在这种情况下，应联系数据库管理员，以便确定问题并可以通过更改或删除登录触发器来选择重新连接到SQL Server。

允许登录授权访问数据库引擎。 (Allow Logins to Authorize to access database Engine.)

In specific situations, user-defined Windows and SQL logins exist in SQL Server instance; be that as it may, for security reasons, we just utilize a couple of user accounts using the back-end applications. Prudently this sort of excess logins ought to be removed.

在特定情况下，SQL Server实例中存在用户定义的Windows和SQL登录名。 出于安全原因，我们可能只是通过后端应用程序利用了几个用户帐户。 谨慎地应删除这种多余的登录名。

Recommendation: We should plan our framework such that the users ought to have the option to interface legitimately to the database and hence to limit to control their permissions, to avoid any complication to trigger and increase the security of the database.

建议：我们应该对框架进行规划，以使用户应该可以选择合法地连接到数据库，从而限制控制他们的权限，以避免触发和增加数据库安全性的任何复杂性。

For example, four Logins are existed in server “sa”, “jraiyani”, “myel” and “sdonda”. As a necessity, we have to permit just a couple of them “sa” and “jraiyani” to the access to SQL Server database engine, at that point below trigger assistance us to oversee it.

例如，服务器“ sa”，“ jraiyani”，“ myel”和“ sdonda”中存在四个登录名。 必要时，我们只需要允许其中的几个“ sa”和“ jraiyani”访问SQL Server数据库引擎，这时下面会触发协助我们进行监督。

CREATE TRIGGER Prevent_login
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
  DECLARE @LoginName sysname
  DECLARE @LoginType sysname
  
  SET @LoginName = ORIGINAL_LOGIN()
 
  IF(@LoginName NOT IN ('sa', 'jraiyani')) 
  BEGIN
    ROLLBACK; --Disconnect the session
  END
END

On the off chance that the user is attempting to log in with the restricted Login, at that point SQL Server will return a blunder as Logon failed for log in because of trigger execution. We can see the equivalent in the below screen capture.

如果用户尝试使用受限的登录名进行登录，那么由于触发器执行而登录失败，导致登录失败，SQL Server将返回一个错误。 我们可以在下面的屏幕截图中看到等效的内容。

The SQL Server is intended to deal with tremendous quantities of speedy login endeavours, thusly for the reason of security, exceptionally suggested that sa login is required to disable in SQL Server.

SQL Server旨在处理大量快速的登录工作，因此出于安全性考虑，特别建议在SQL Server中禁用sa登录是必需的。

At the point when the users get allowed to access the confined information that is the place, the client triggers the most minimal at the security standard.

在允许用户访问该场所的受限信息时，客户端会以安全标准触发最少的事件。

允许某些应用程序授权访问数据库引擎。 (Allow certain Applications to Authorize to access database Engine.)

There is no security competency that will enable any authorized Login to associate from any program (for example Back-end Application), however no other than it, for example, Query Management studio or different applications.

没有安全能力可以使任何授权的登录名与任何程序（例如，后端应用程序）相关联，但是除了它（例如，Query Management studio或其他应用程序）之外，别无其他。

You may discover interesting as a workaround is to utilize logon triggers to attempt to maintain a strategic distance from accidental access through non-approved or non-authorized programs. In any case, be cautioned: This isn’t a security limit, and it is anything but difficult to sidestep, however it will enable you to keep fair individuals genuine. So I will make a logon trigger that will check the application name on the session, and square any application which hasn’t affirmed for this Login.

您可能会发现有趣的解决方法，即利用登录触发器来尝试与未经批准或未经授权的程序意外访问保持策略距离。 在任何情况下，请注意：这不是安全限制，几乎可以回避，但这可以使您保持公平的个人真实。 因此，我将创建一个登录触发器，该触发器将检查会话上的应用程序名称，并对未确认此登录的任何应用程序进行平方处理。

See here, this trigger would impact every one of the users, subsequently to avoid redundant restrictions on a specific type of Login. Subsequently we have added the checks to stay away from preventable restrictions on specific segment of Login, as and when a user group attempt to connect with SQL Server, the security Logon trigger will check the name of the application and the login trigger will raise the blunder message “Connection to fail” on the off chance that the login qualifications do not match.

参见此处，此触发器将影响每个用户，随后避免对特定类型的登录进行冗余限制。 随后，我们添加了检查，以防止对登录的特定段进行可避免的限制，因为当用户组尝试连接SQL Server时，安全性登录触发器将检查应用程序的名称，并且登录触发器将引发错误。如果登录资格不匹配，则会显示“连接失败”消息。

the Login who is authorized will most likely get to the Database by avoiding the security triggers and the unauthorized Login won’t ready to deny that she/he attempted to sidestep your policy.

被授权的登录名很可能会通过避免安全触发器来进入数据库，并且未经授权的登录名不会准备否认他/他试图回避您的策略。

In below example, we allowed ‘myel’ user to login in using ‘SQLCMD’ application only.

在下面的示例中，我们仅允许“ myel”用户使用“ SQLCMD”应用程序登录。

CREATE TRIGGER Prevent_login
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
  DECLARE @AppName varchar(max)
  DECLARE @LoginName sysname
  DECLARE @LoginType sysname
  
  SET @AppName = APP_NAME()
  SET @LoginName = ORIGINAL_LOGIN()
 
  IF(@LoginName = 'myel' AND @AppName like 'SQLCMD')
  BEGIN
    RETURN;--Login Authorized
  END
  IF(@LoginName NOT IN ('sa', 'jraiyani', 'sdonda'))
  BEGIN
    ROLLBACK; --Disconnect the session
  END

User logged in successfully for authorized SQL Login.

用户成功登录以进行授权SQL登录。

SQL Server will not allow restricted user which has been mentioned in second IF condition. In that case, SQL Server will return error with Logon trigger reference as below image.

SQL Server将不允许第二个IF条件中提到的受限用户。 在这种情况下，SQL Server将返回带有登录触发器参考的错误，如下图。

We can mention here different client application name and combination of the client application and SQL Login name to get the filter out in SQL Authentication.

我们可以在这里提到不同的客户端应用程序名称，以及客户端应用程序和SQL登录名的组合，以在SQL身份验证中过滤掉。

The firewall will be continually denying any unauthorized correspondence and verifying the Database server behind it. The firewall won’t permit any immediate client to get separated from the correspondence from the particular applications. The change principle control methods are prepared by Firewall and the System Manager and Database Administrator could get the notice as a warning of standard changes can be set.

防火墙将继续拒绝任何未经授权的通信并验证其背后的数据库服务器。 防火墙不允许任何直接客户端与特定应用程序的对应关系分开。 更改原则控制方法是由防火墙准备的，系统管理员和数据库管理员可以设置标准更改警告来获得通知。

只允许从授权的机器IP登录 (Allow Login from Authorized Machine IPs Only)

The Microsoft SQL Server has got an inbuilt function for trigger called EVENTDATA() that would return Login event data in XML format for Logon event. However various kind of rules can be configured with EVENTDATA() that would allow certain authorized IP address only to authenticate by the Database engine. In most cases, a database server is accessible by the application server and responsible Database Engineer only and those users who are able to access SQL Server Instance using internet or Intranet.

Microsoft SQL Server有一个名为EVENTDATA（）的触发器内置函数 ，它将以XML格式返回Logon事件的Login事件数据。 但是，可以使用EVENTDATA（）配置各种规则，这些规则将允许某些授权的IP地址仅由数据库引擎进行身份验证。 在大多数情况下，只有应用程序服务器和负责的数据库工程师以及能够使用Internet或Intranet访问SQL Server实例的那些用户才能访问数据库服务器。

Various Security Layers applied by organizations to connect SQL Server Instance. For Example, VPN (Virtual Private Connection), Port access, SQL connection. These IP restrictions can be incorporated with the firewall as well. However here are a few described Logon trigger rules with an example to allow authorized IP addresses only. EVENTDATA() returns below XML with connection data while Logon trigger executed.

组织为连接SQL Server实例而应用的各种安全层。 例如，VPN（虚拟专用连接），端口访问，SQL连接。 这些IP限制也可以与防火墙合并。 但是，这里介绍了一些登录触发规则，并举例说明了仅允许授权的IP地址。 当执行登录触发器时， EVENTDATA（）返回带有连接数据的XML以下。

<EVENT_INSTANCE>
  <EventType>LOGON</EventType>
  <PostTime>2019-09-03T12:33:59.773</PostTime>
  <SPID>53</SPID>
  <ServerName>JERRY\JIGNESH</ServerName>
  <LoginName>sa</LoginName>
  <LoginType>SQL Login</LoginType>
  <SID>AQ==</SID>
  <ClientHost>192.168.1.1</ClientHost>
  <IsPooled>0</IsPooled>
</EVENT_INSTANCE>

To get Client host address use below XML query in Trigger only. Because, In regular session of SQL Server, it will return NULL value.

要获取客户端主机地址，请仅在触发器中的XML查询下方使用。 因为，在SQL Server的常规会话中，它将返回NULL值。

EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(128)')

To apply this rule on logon trigger, needed to prepare a list of authorized IP address and add the list to the trigger with applying filter. Below example help us to design rule to allow a particular IP list on Logon. we can add “<local machine>” in the IP whitelist to allow localhost connection.

将此规则应用于登录触发器，需要准备一个授权IP地址列表，并使用应用过滤器将该列表添加到触发器。 下面的示例可帮助我们设计规则以允许登录时使用特定的IP列表。 我们可以在IP白名单中添加“ <local machine> ”以允许localhost连接。

CREATE TRIGGER prevent_ip 
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
        IF (EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(128)') IN 
        ('192.168.1.1', '192.168.1.1', '<local machine>'))
    BEGIN
      RETURN; -- Login Authorized
    END
    ELSE            
    BEGIN
            ROLLBACK; -- Disconnect the session
        END
END

In the above illustration, if requesting client application IP address is valid as IP whitelist then Logon trigger will allow to access database engine or else Login request will be kicked out with an error message. Now to make a combination of multiple rules within trigger we can define our own logic. Different Logon triggers are additionally bolstered in the single SQL Server instance.

在上图中，如果请求的客户端应用程序IP地址作为IP白名单有效，则登录触发器将允许访问数据库引擎，否则登录请求将被踢出并显示一条错误消息。 现在，要在触发器中组合多个规则，我们可以定义自己的逻辑。 在单个SQL Server实例中还额外支持了不同的登录触发器。

结论： (Conclusion:)

With the assistance of the Logon trigger, the client connections can be likewise be observed, similar to Which client got associated and when the connection was built up in a joint effort with the guidelines require at different SQL Server security levels. There are regular possibly high-security arrangement rejections, for example, default organize settings, logins, and passwords as often as possible utilized by aggressors.

在登录触发器的帮助下，同样可以观察到客户端连接，类似于关联了哪个客户端以及何时在不同SQL Server安全级别上与准则共同努力共同建立了连接。 定期拒绝可能的高安全性安排，例如，默认的组织设置，登录名和密码，由 侵略者。

目录 (Table of contents)

Limit SQL Server Login Authentication scope using a Logon Trigger

Database Level DDL Triggers on Tables

Database Level DDL Triggers for Views, Procedures and Functions

使用登录触发器限制SQL Server登录身份验证范围

表上的数据库级DDL触发器

用于视图，过程和函数的数据库级DDL触发器

翻译自: https://www.sqlshack.com/prevent-sql-server-login-authentication-scope-using-logon-trigger/