使用SQL监视器检查失败的服务器登录、服务器错误和警告


仅配备PowerShell和SQL监视器的作者提供了一种解决方案,该解决方案可以快速提醒您一系列Windows错误、警告和关键事件,包括在托管SQL Server实例的Windows Server上伴随暴力密码攻击而失败的服务器登录尝试。

每当我听到又一次SQL Server攻击是由对Windows Server主机的暴力密码攻击引发的时,我的标准反应是激动地在空中挥舞双手,同时解释需要持续自动检查服务器日志中的警告。

最终,是时候停止挥手,拿出解决方案了。我使用Get-WinEvent cmdlet,并在哈希表的帮助下,使用PowerShell脚本读取Windows事件日志数据并对其进行过滤操作。我将数据保存在SQL Server数据库中,这样我就可以在那里进行扫描,并确保拥有事件的永久记录。我编写了一个简单的SQL监视器自定义度量,它返回在过去10分钟内记录的Windows事件数。SQL监视器将按计划收集此数据,并在发生这些事件时向我发出警报。

有了这样的解决方案,您将在5分钟内收到安全日志和您选择的任何其他日志中的一系列错误、警告和关键事件的警告,并且您将看到伴随暴力密码攻击的大量失败登录尝试。我希望这个解决方案对读者仍然有用,即使他们使用不同的警报系统,因为大多数任务都是共同的。

获取Windows安全日志事件

有许多Windows安全日志事件;它们是listed and explained here。许多对此毫无兴趣,但有些却是监控安全的瑰宝。有人可能认为安全事件的级别将是其重要性的指标,但事实并非如此。失败的登录尝试被认为是“信息性的”,这在任何暴力攻击中都是可以预料到的。不过,通常情况下,日志错误和警告也很有用,并在几个情况下挽救了我的生命。

通过PowerShell读取Windows事件和经典日志

SQL不适合此任务,因为只能通过读取SQL Server错误日志和SQL代理日志xp_readerrorlog。您可以在SSMS中使用Log File Viewer来读取经典日志,这对于特别调查很合适。有几种方法可以在PowerShell中获取此信息,如Laerte Junior的The PoSh DBA — Reading and Filtering Errors

Get-WinEventcmdlet可能是从本地服务器上的一百多个事件日志(包括经典系统日志、安全日志和应用程序日志)中获取信息的最佳工具。它还读取由后者生成的事件日志Windows Event Log technology以及由Event Tracing for Windows。单个日志可以有数千个条目。

此cmdlet允许您(如果您是以Admin用户身份运行)列出和调查日志并读取内容。这里有很多信息,过滤是必不可少的,特别是当您要一个接一个地读取多个服务器上的日志时。当您明智地应用过滤器时,Get-WinEvent可以快速工作。您可能需要进行一些试验,才能找到一种搜索模式,使其能够找到在日志中进行搜索的最佳方式。如果你做对了,它会非常快。

Get-WinEvent查询

有三种方式可以指定您的查询。您可以使用XPath查询或结构化XML查询。对于像我这样的普通人来说,有一些简单的哈希表过滤器,它们的工作方式与散列参数非常相似,只是参数并没有全部公开。关于如何使用它的最好解释在下面的Use FilterHashTable to Filter Event Log with PowerShell

有几种不同的方法来切这一大堆活动的披萨。您可以从日志名称、提供程序名称、关键字枚举值、事件ID、严重级别列表中进行选择,也可以从归档传统日志的路径列表中进行选择。您可以指定时间段的开始和结束时间段或生成错误的用户ID。这种多功能性很适合我们。

创建散列表过滤很简单。在这里,我们只查找在过去24小时内发生的任何具有严重、错误或警告级别的可怕“经典”事件。如果您需要扫描其他日志,只需将它们添加到列表中即可。然后我们只需将过滤用作FilterHashTable的参数Get-WinEvents

$Search = @{
    LogName = 'application', 'security', 'system';
    # we search the application, security and system logs
    Level = 1, 2, 3;
    # Verbose 5, Informational 4, Warning 3, Error 2, Critical 1, LogAlways 0 
    # we do events and errors of warning, error and critical levels 
    StartTime = (get-date) - (New-TimeSpan -Days 1)
    # We go just one day back as we are calling this regularly
  };
  Get-WinEvent  -FilterHashTable $Search -MaxEvents 1000 |
    Select Id, TimeCreated, Level, LevelDisplayName, Message, ProviderName, LogName

虽然这会给您带来基本的警告和错误,但它不会让您登录失败。这些不是警告,而是信息性事件。这需要再次搜索我们想要监视的任何特定安全事件,但这些事件不被视为警告。

$FailedLogins = @{
    LogName = 'security';
    ID = 4625; #add any other security events you want that are informational
    Level = 0;
    StartTime = (get-date) - (New-TimeSpan -Days 1)
  }
  Get-WinEvent -MaxEvents 1000 -FilterHashTable $FailedLogins |
    Select Id, TimeCreated, level, LevelDisplayName, Message, ProviderName, LogName

我们会到此为止,但你可能会看到其他security events that you're interested in。我还没有演示它,但是您可以通过管道连接一大堆散列表来进行大量快速搜索Get-WinEvent。您甚至可以在服务器列表上运行搜索。

PowerShell脚本

现在,我们只需要按计划执行这些查询,并将结果存储在SQL Server数据库(称为ServerEvents在我的示例中)。您必须创建数据库,但是这些表是一个名为的临时表EventsStaging而目标表称为Events,都是由计划进程创建的。临时表是通过使用-force属性的参数Write-SqlTableDatacmdlet。

该脚本使用sqlserver提供程序执行以下枯燥的工作:将包含事件日志数据的PowerShell对象复制到临时表中,然后在服务器上执行SQL代码,该代码将创建目标表,并将临时表中找到的尚未存储在目标中的任何事件添加到其中。

您必须在与运行脚本的服务器相同的服务器上运行该脚本ServerEvents数据库。如果您指定,可以将其作为的参数远程运行Get-WinEvent,您正从其监视事件的服务器。

分配给运行此计划任务的管理员用户必须是SQL Server登录名,并且该登录名是dbo角色仅限于ServerEvents数据库。我们的脚本需要执行获取该管理员用户凭据的例行工作,该凭据以加密形式存储在Windows用户配置文件目录中,其位置通过环境变量引用$env:USERPROFILE。只有当您希望避免将SQL Server登录名分配给本地Windows用户时,才有必要这样做。第一次运行该脚本时,系统将要求您输入用户的密码。希望对于任何用户,这只会发生一次。

或者,如果使用Windows身份验证,则可以向分配给任务的用户授予此登录名和dbo用户角色,而您只需将$SQLUserName空白的。

$SQLUserName = 'PhilFactor'
  $SQLInstance = 'MyServer'
  $SQLDatabase = 'ServerEvents'
  $StagingTableName = 'EventsStaging'
  $DestinationTableName = 'Events'
  $Logfile = 'C:\Scripts\GetWindowErrors.log'
  $Errors = @()
  "$(get-date): started out getting errors">>$Logfile
  Import-Module sqlserver -DisableNameChecking     `
          -ErrorAction silentlycontinue    `
          -ErrorVariable +Errors #load the SQLPS functionality
  set-psdebug -strict
  $ErrorActionPreference = "stop"
  $SqlEncryptedPasswordFile = `
  "$env:USERPROFILE\$($SqlUserName)-$($SQLInstance).txt"
  # test to see if we know about the password in a secure string stored in the user area
  if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
  {
    #has already got this set for this login so fetch it
    $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString
    $SqlCredentials = `
    New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)
  }
  else #then we have to ask the user for it
  {
    #hasn't got this set for this login
    $SqlCredentials = get-credential -Credential $SqlUserName
    $SqlCredentials.Password | ConvertFrom-SecureString |
    Set-Content $SqlEncryptedPasswordFile
  }
  "$(get-date): got credentials for $SqlUserName">>$Logfile
  <# 
  #>
  $Search = @{
    LogName = 'application', 'security', 'system';
    # we search the application, security and system logs
    Level = 1, 2, 3;
    # we do events and errors of warning, error and critical 
    StartTime = (get-date) - (New-TimeSpan -Days 1)
    # We go just one day back
  };
  $FailedLogins = @{
    LogName = 'security';
    ID = 4625; #add any other security events you want that are
    Level = 0;
    StartTime = (get-date) - (New-TimeSpan -Days 1)
  }
  try
  {
    $Events = Get-WinEvent -MaxEvents 1000 -FilterHashTable $Search    `
                 -ErrorAction silentlycontinue    `
                 -ErrorVariable +Errors |
    Select Id, TimeCreated, Level, LevelDisplayName, Message, ProviderName, LogName
  }
  Catch
  {
    $ThisError = "$(get-date): Failed to get WinEvent $($_.ErrorDetails.Message) "
    $errors += $ThisError
    $ThisError>>$Logfile
    >>$Logfile
  }
  try
  {
    $Events += Get-WinEvent -MaxEvents 1000 -FilterHashTable $FailedLogins   `
                -ErrorAction silentlycontinue    `
                -ErrorVariable +Errors |
    Select Id, TimeCreated, level, LevelDisplayName, Message, ProviderName, LogName
  }
  Catch
  {
    $ThisError = "$(get-date): Failed to get WinEvent Security log $($_.ErrorDetails.Message) "
    $errors += $ThisError
    $ThisError>>$Logfile
  }
  if ($errors[0] -ilike "*No events were found*" -and $errors.Count -eq 1)
  { $Errors = @() }
  if (($Events.Count -gt 0) -and ($Errors.count -eq 0))
  {
    Write-SqlTableData -inputData $Events `
               -ServerInstance $SQLInstance -database $SQLDatabase `
               -Credential $SQLCredentials ` -SchemaName "dbo" -TableName $StagingTableName -Force    `
               -ErrorAction silentlycontinue    `
               -ErrorVariable +Errors
  }
  $sql =@"
  USE [$SQLDatabase]
  GO
  IF Object_Id('dbo.$DestinationTableName') IS NULL
    begin
    CREATE TABLE [dbo].[$DestinationTableName](
      [Id] [INT] NOT NULL,
      [TimeCreated] [DATETIME2](7) not NULL,
      [Level] [TINYINT] NOT NULL,
      [LevelDisplayName] [NVARCHAR](40) not NULL,
      [Message] [NVARCHAR](4000) NOT NULL,
      [ProviderName] [NVARCHAR](40) NOT NULL,
      [LogName] [NVARCHAR](40) NOT NULL
    ) ON [PRIMARY]
    END
  GO
  INSERT INTO [$DestinationTableName](Id, TimeCreated, [Level], LevelDisplayName, [Message],
         ProviderName, LogName)
  SELECT $StagingTableName.Id, $StagingTableName.TimeCreated, $StagingTableName.Level,
         $StagingTableName.LevelDisplayName, Left($StagingTableName.Message,4000),
         Left($StagingTableName.ProviderName,40), Left($StagingTableName.LogName,40)
       FROM $StagingTableName
  LEFT OUTER JOIN [$DestinationTableName]
  ON $DestinationTableName.Id = $StagingTableName.Id
  AND $DestinationTableName.TimeCreated = $StagingTableName.TimeCreated
  WHERE $DestinationTableName.message IS null
  TRUNCATE TABLE $StagingTableName --because you don't need that data again.
  "@
  if ($Errors.count -eq 0)
  {
    Invoke-Sqlcmd -Query $sql -ServerInstance $SQLInstance  `
            -Credential $SQLCredentials -database $SQLDatabase   `
            -ErrorAction silentlycontinue    `
            -ErrorVariable +Errors
  }
  <# We collect all the soft errors and deal with them here.#>
  if ($errors.Count -gt 0)
  {
    $errors | foreach {
      "$((Get-Date).ToString()): $($_) the task was aborted">>$Logfile;
    }
  };
  "$(get-date): checked for errors, found $($events.Count)">>$Logfile

我将此设置为Windows任务调度程序每5分钟运行一次,这就是为什么运行此脚本的所有错误和警告都通过管道传输到本地错误日志文件(否则您不会看到它们!)

这是一个最好分几个阶段完成的过程,始终进行测试。首先,在服务器上的PowerShell ISE中运行任务,在计划程序中以您将分配来运行PowerShell任务的用户ID登录。然后,以同一用户身份使用PowerShell控制台和命令控制台,最后在调度程序上,每隔5分钟运行一次,并检查本地错误日志是否有任何错误和报告。

要确保此功能正常工作,最明显的测试是尝试使用错误的ID和/或密码登录到服务器,并查看events桌子。

您还可以添加各种虚假错误,以确保记录这些错误。

New-EventLog –LogName Application –Source PowerShellScript
  Write-EventLog -LogName "Application"    `
         -Source PowerShellScript   `
         -EventID 3001 -EntryType Warning     `
         -Message "A melancholy misadventure has struck my code."
  Write-EventLog -LogName "Application"    `
         -Source PowerShellScript   `
         -EventID 3001 -EntryType Error     `
         -Message "My code has been sadly struck by misfortune."

您需要在五分钟后检查SSMS中的事件查看器和SQL Server数据库,以确保所有事件都已传输。

一旦您确定这是有效的,您就可以执行SQL监视器端的操作了。

创建SQL监视器自定义度量

下面的SQL简单地统计在过去10分钟内写入事件表的Windows事件的数量。我们计划的PowerShell作业每五分钟运行一次。

USE ServerEvents
  SELECT Count(*) 
    FROM events 
      WHERE TimeCreated > DateAdd(MINUTE, -10, GetDate())

或者,您可能希望创建两个指标,一个简单地只计算失败的登录,另一个计算其他所有情况。以下是仅获取失败登录的SQL:

USE ServerEvents
  SELECT Count(*) 
    FROM events 
      WHERE TimeCreated > DateAdd(MINUTE, -10, GetDate())
      AND providername LIKE 'Microsoft-Windows-Security-Auditing'

此外,您还可以获得其他类似的活动:

USE ServerEvents
  SELECT Count(*) 
    FROM events 
      WHERE TimeCreated > DateAdd(MINUTE, -10, GetDate())
      AND NOT providername LIKE 'Microsoft-Windows-Security-Auditing'

我只使用了一个度量,因为无论如何签入SSM都是非常有用的,不管是什么原因,但是无论您选择怎样做,您现在都可以使用这些查询来create your custom metrics在SQL监视器中,加上关联的警报。显然,这不是数据库级别的指标。您应该指定仅在上运行它master

然后,您可以通过虚假尝试登录服务器并运行PowerShell脚本,将错误和警告写入您正在监视的各种日志,从而检查它是否正常工作

哎呀,有人试图暴力破解我的一个登录!呃,没有。在这种情况下,我只是检查以确保“水通过所有的管道”。您还将检测到的不仅仅是失败的登录。当所有这些都在运行时,系统升级在我用作测试床的服务器上运行。出了这么多问题,现在都被发现了。

我通常要检查所有的日志吗?我是不是.见鬼。现在,我有足够的证据确认监视器工作正常,而且我很感谢那种用腰带支撑的方法来收集数据,从而获得一整天的数据。

对于像我这样的SQL Server开发人员来说,将错误日志放在表中是一个非常棒的想法,因为我现在可以分割所有数据并搜索消息。例如,我可以搜索特定字符串:

SELECT Message, LogName FROM Events WHERE Message LIKE '%Vlad The Impaler%';

给予:

message 
  -------------------------------
  An account failed to log on.
  Subject:
    Security ID:        S-1-0-0
    Account Name:       -
    Account Domain:     -
    Logon ID:       0x0
  Logon Type:           3
  Account For Which Logon Failed:
    Security ID:        S-1-0-0
    Account Name:       Vlad the Impaler
    Account Domain:
   Security
  (1 row affected)

我可以按严重程度细分错误:

SELECT Convert(CHAR(11), TimeCreated, 113) AS The_date,
    --Verbose 5,Informational 4,Warning 3,Error 2, Critical 1 LogAlways  0
    Sum(CASE WHEN Level = 5 THEN 1 ELSE 0 END) AS Verbose,
    Sum(CASE WHEN Level = 4 OR Level = 0 THEN 1 ELSE 0 END) AS Informational,
    Sum(CASE WHEN Level = 3 THEN 1 ELSE 0 END) AS Error,
    Sum(CASE WHEN Level = 2 THEN 1 ELSE 0 END) AS Critical,
    Sum(CASE WHEN Level = 1 THEN 1 ELSE 0 END) AS LogAlways, Count(*) AS total
    FROM Events
    GROUP BY Convert(CHAR(11), TimeCreated, 113)
    ORDER BY Min(TimeCreated);

或者由提供程序查看提供程序每天的错误数量。

DECLARE @variable NVARCHAR(MAX) =
    'Select Convert(CHAR(11), TimeCreated, 113),'
    +
      (
      SELECT 
        String_Agg
         (Convert
            (NVARCHAR(MAX),
             'sum(Case when Providername like '''
              + providername + ''' then 1 else 0 end) as ['
              + providername + ']'
             ), ', '
          )
        FROM (SELECT ProviderName FROM Events GROUP BY ProviderName) AS f(providername)
      )
    + ',count(*) as [total] 
    FROM Events
    GROUP BY Convert(CHAR(11), TimeCreated, 113)
    ORDER BY Min(TimeCreated);';
  EXECUTE sp_executesql @variable;

我可以修复特定的问题,而不必通过日志进行混乱的搜索。此外,因为我可以将数据保存在SQL Server数据库中,所以我可以保留更长的历史记录。

扩展解决方案

至于如何将其扩展到一组服务器,我还是让它发挥想象力。SQL监视器端很简单,但要使其正常工作,需要将Windows事件存储在每个实例上。您需要决定是从中心位置进行收集,并在收集记录时为每台服务器编写记录副本,还是在每台服务器上运行脚本。我估计这取决于SQL Server遗产的大小,但这是一个判断问题。

中央错误收集器的优势在于检查它。这个Get-WinEvent如果您告诉cmdlet要从服务器获取日志记录,那么它可以通过网络收集日志记录,因此脚本和数据模型并不复杂,尽管凭据方面可能会变得杂乱无章。

结论

如果一个外星宇航员在飞翔上向我询问有关关系数据库系统的问题,我将很难向他解释SQL Server Security。作为数据库人员,我们密切关注实例,但对托管它的服务器的关注要少得多。当我告诉外星人我不容易收到关键或错误Windows事件的警报时,我几乎可以看到外星人脸上的好奇表情。

幸运的是,假设我能得到运营人员的同意来运行我预定的PowerShell任务,那么当来自太空的访问者询问数据库安全在地球上是如何工作的时,我现在就没有这种社交尴尬的风险了。这里希望DevOps的合作精神也可以扩展到允许我收集这些任务的脚本,从而将我从星际尴尬中解救出来。

尽管Azure SQL数据库就像一只满意的昆虫幼虫一样,处于一个良好的、托管的茧中,但对于网络上托管的数据库来说,情况并不一定如此。因此,SQL Server的许多成功渗透(如暴力密码攻击)都依赖于宿主服务器的安全问题,因此,在服务器级别了解安全警报和警告似乎是正确的。事实上,我们需要了解所有的事件。不只是恶意入侵者会使Windows服务器步履蹒跚,自动升级或断电也是如此。

至少现在,在SQL Monitor的帮助下,我可以获得失败登录和其他Windows安全事件发生的警报和基线,然后深入到SQL Server数据库中的详细信息。是的,了解服务器级别正在发生什么错误、警报和警告以及它们发生的时间是非常有用的!