sql server用户日志 sqlserver登陆日志

The authorization/login process can either be successful or no. Even the both login outcomes are security related and should be documented (audited), the focus should be on the failed logins. These can be a result of accidentally mistyped username/password, expired credentials, or insufficient permissions. However, failed logins can also indicate malicious attempts to access confidential data hosted on SQL Server instances.

授权/登录过程可以成功或不成功。 即使两个登录结果都与安全性相关，并且应记录（审核），重点应该放在失败的登录上。 这可能是由于用户名/密码输入错误，凭证过期或权限不足引起的。 但是，失败的登录也可能表示恶意尝试访问SQL Server实例上托管的机密数据。

(Methods for auditing failed logins in SQL Server)

SQL Server provides several native methods for auditing failed logins. Some of them are easy to set up, others provide more details than the others. They all have in common that the audited information is difficult to review.

SQL Server提供了几种本地方法来审核失败的登录。 其中一些易于设置，其他提供的详细信息比其他提供的更多。 它们都有一个共同点，即审核后的信息难以审核。

(Login auditing in SQL Server Management Studio)

One of the options to audit failed logins is to turn on the appropriate option in SQL Server in the Server Properties dialog (the Security

服务器属性”对话框（“ 安全性”节点）中，在SQL Server中打开相应的选项。

The setting is on the instance level. The SQL Server instance must be running to capture failed logins. Otherwise, there will be no information available. This applies to all SQL Server native auditing techniques.

该设置在实例级别。 SQL Server实例必须正在运行以捕获失败的登录。 否则，将没有可用的信息。 这适用于所有SQL Server本机审核技术。

Once the option is set, failed logins are captured in SQL Server log files and shown in the Log File Viewer of SQL Server Management Studio. These files can be queried using an undocumented xp_readerrorlog extended stored procedure.

设置该选项后，失败的登录将捕获到SQL Server日志文件中，并显示在SQL Server Management Studio的日志文件查看器中。 可以使用未记录的xp_readerrorlog扩展存储过程查询这些文件。

Additionally, captured failed logins can be reviewed using the Windows event viewer, in the Application sub-node of the Windows Logs node.

此外，可以使用Windows事件查看器在Windows日志节点的“应用程序”子节点中查看捕获的失败登录。

Even this auditing method is easy to set up, there are numerous downsides that make it inappropriate for usage. The main issue is related to native log files. They get recycled and are difficult to be saved automatically for later use. Also, whether the logs are reviewed using built-in viewers or queried, they contain numerous entries not related to failed logins and this noise is difficult to remove.

即使这种审核方法很容易设置，但也存在许多缺点，因此不适合使用。 主要问题与本机日志文件有关。 它们被回收，很难自动保存以备后用。 此外，无论是使用内置查看器查看日志还是查询日志，它们都包含许多与失败的登录无关的条目，并且这种噪声很难消除。

(The Trace technology and auditing failed logins)

SQL Server traces and Profiler can also be used for auditing failed logins, but as the feature is announced to be deprecated in future versions of SQL Server, it’s not recommended to use this approach moving forward.

SQL Server跟踪和Profiler也可以用于审核失败的登录，但是由于该功能宣布在SQL Server的未来版本中已弃用，因此不建议继续使用此方法。

(Using SQL Server Audit to capture failed logins)

The Audit feature in SQL Server is built on top of Extended Events. It’s the technology that will be additionally developed and expanded in future SQL Server versions. It was introduced in SQL Server 2008 and can be used on both instance and database level. Failed login auditing belongs to the instance level. Therefore, the limitation reflected in supporting database level auditing in the Enterprise and Developer editions only, does not affect our intention to audit failed logins using any SQL Server edition.

SQL Server中的审核功能是建立在扩展事件之上的。 这项技术将在将来SQL Server版本中进一步开发和扩展。 它是SQL Server 2008中引入的，可以在实例和数据库级别上使用。 登录审核失败属于实例级别。 因此，仅在Enterprise和Developer版本中支持数据库级别审核所反映的限制并不影响我们使用任何SQL Server版本审核失败的登录的意图。

To audit failed logins in SQL Server, we’ll create a new audit object for a SQL Server instance. Afterwards, we’ll create a server audit specification object tied to the audit object. Although the same audit object can be used by multiple server audit specifications, it’s a good practice to declare one audit object per auditing event type (e.g. failed logins) to avoid noise in captured information by other audited events. Also, this makes a maintenance of an auditing structure (audit and server audit specification objects along with corresponding log files) easy.

要审核SQL Server中失败的登录，我们将为SQL Server实例创建一个新的审核对象。 之后，我们将创建一个与审核对象绑定的服务器审核规范对象。 尽管同一审计对象可以由多个服务器审计规范使用，但是一种良好的做法是为每个审计事件类型（例如，失败的登录名）声明一个审计对象，以避免其他审计事件在捕获的信息中产生干扰。 同样，这使得维护审核结构（审核和服务器审核规范对象以及相应的日志文件）变得容易。

To declare the audit object:

声明审核对象：

1. Security node in 对象资源管理器中展开“ Object Explorer of SQL Server Management Studio and select the 安全性”节点，然后在“ New Audit option in the context menu of the 审核”子节点的上下文菜单中选择“ Audits sub-node新建审核”选项。
3. Type in the name for the audit object (e.g. AuditFailedLogins) and set other parameters per your needs. In this example, we’ll specify the folder where repository log file(s) will be saved. A number of log files and whether they will be rolled over and how, can be defined using the Audit File Maximum Limit section. If you don’t want to lose any captured information on failed logins, keep Maximum rollover files and Unlimited checked.
   AuditFailedLogins ），并根据需要设置其他参数。 在此示例中，我们将指定存储库日志文件的文件夹。 可以使用“ 审核文件最大限制”部分来定义许多日志文件，以及是否将其翻转以及如何进行。 如果您不想丢失任何有关失败登录的捕获信息，请选中“ 最大翻转文件”和“ 无限”
   



7. OK to save the newly declared audit object.确定”保存新声明的审核对象。

To declare the server audit specification for auditing failed logins and tie it to the previously created audit object:

要声明用于审核失败登录的服务器审核规范，并将其绑定到先前创建的审核对象：

1. New Server Audit Specification in the context menu of the 服务器审核规范”节点的上下文菜单中选择“ Server Audit Specifications node.新服务器审核规范”
2. FailedLoginsSpecification) and select the FailedLoginsSpecification ），然后从“ AuditFailedLogins audit object (previously declared) from the 审核”下拉菜单中选择Audit drop down menu. AuditFailedLogins审核对象（先前声明）。 Select the FAILED_LOGIN_GROUP value in the Audit Action Type field of the Actions grid. Note that the rest of the fields for the row cannot be set if the FAILED_LOGIN_GROUP value is set and they remain blank.
   “ 操作”网格的“ 审核操作类型”字段中选择FAILED_LOGIN_GROUP值。 请注意，如果设置了FAILED_LOGIN_GROU
   

   

   
   
3. OK to save the server audit specification确定”保存服务器审核规范

The created set of SQL Server Audit objects is enough to capture failed logins once they occur. The information about failed logins is stored in the logs as specified in the audit object. The logs can be reviewed using the View Audit Logs option in the context menu of the audit object which opens Log File Viewer.

创建SQL Server审核对象集足以捕获失败的登录。 有关失败登录的信息将存储在审核对象中指定的日志中。 可以使用审核对象的上下文菜单中的“查看审核日志”选项来查看日志，这将打开“日志文件查看器”。

Over time, the number of entries in the logs can increase and basic available filtering in Log File Viewer cannot be useful. To overcome this, the information about failed logins in log files can be queried:

随着时间的流逝，日志中的条目数量会增加，并且“日志文件查看器”中的基本可用过滤功能将不再有用。 为了解决这个问题，可以查询有关日志文件中登录失败的信息：

SELECT
       event_time AS Login_Time,
       server_instance_name ,
       statement AS Description
  FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE
      action_id = 'LGIF';

The LGIF value, as the condition, is used to filter out all failed logins from all logs in the specified folder, whether they contain failed logins only or not (e.g. the C:\AUDITs

LGIF值作为条件，用于从指定文件夹中的所有日志中过滤出所有失败的登录，无论它们是否仅包含失败的登录（例如，其他审核对象使用C：\ AUDITs文件夹）。 此外，您可以根据需要修改查询。

A downside of tracking failed logins via SQL Server Audit is that it does not provide the names of client applications used. In some cases, this information can be essential in locating the source of malicious login attempts.

通过SQL Server审核跟踪失败的登录的缺点是它不提供所使用的客户端应用程序的名称。 在某些情况下，此信息对于查找恶意登录尝试的来源可能至关重要。

To audit a client application name along with each failed login, use an Extended Events session.

要审核客户端应用程序名称以及每个失败的登录，请使用扩展事件会话。

(Using SQL Server Extended Events to capture failed logins)

Extended Events sessions can be set and used to capture failed logins as errors of Severity 14. Moreover, starting from SQL Server 2012, failed logins can be precisely pointed as the ones with the 18456 error number.

可以设置扩展事件会话，并将其捕获为严重级别14的错误，以捕获失败的登录。此外，从SQL Server 2012开始，可以将失败的登录精确地指出为错误编号为18456的登录。

To set up an Extended Events session:

设置扩展事件会话：

1. Expand the Management node and the Extended Events sub-node in Object Explorer. Select the New Session option in the context menu of the Sessions
2. 对象资源管理器”中展开“ 管理”节点和“ 扩展事件”子节点。 在“会话 ”节点的上下文菜单中选择“ 新建会话”选项。
3. Type in the session name (e.g. FailedLogins) and select the Events page. Move the error_reported item from the Event library grid to Selected events
   FailedLogins ），然后选择“ 事件”页面。 使用“>”按钮将error_reported项目从事件库网格移动到“ 选定的事件
   

   

   
   
4. As we are focused on failed logins only, click Configure to scroll to additional dialog screen where you should specify additional filters. Use the Global Fields (Actions) tab to check the additional information for the selected event: client_app_name, client_host_name, and nt_username.
   配置”以滚动到其他对话框，在其中应指定其他过滤器。 使用“ 全局字段（操作）”选项卡检查所选事件的其他信息： client_app_name， client_host_name和nt_username
   

   

   
   
6. Switch to the Filter (Predicate)
   过滤器（谓词）”选项卡以指定事件的过滤器值，并仅过滤捕获失败的登录信息。 使用以下屏幕快照中所示的字段，运算符和值：
   



9. 
   
10. OK to save the session, open the context menu of the session and enable it with 确定”保存会话，打开会话的上下文菜单并通过“ Start Session启动会话”启用它

To review the auditing information of the session, expand the session node and select the View Target Data option in the context menu of an event_file

event_file节点的上下文菜单中选择“ 查看目标数据”选项。

The application used to unsuccessfully login to the SQL Server instance audited is shown in the client_name_application

client_name_application字段中显示了用于无法成功登录已审核SQL Server实例的应用程序。

翻译自: https://www.sqlshack.com/using-extended-events-review-sql-server-failed-logins/