Custom Intrusion Detection Reporting for SQL Server

By: Svetlana Golovko   |   Comments   |   Related: > Auditing and Compliance

Problem

Using Windows Authentication with SQL Server is the recommended (more secure) authentication mode. Windows Authentication uses Kerberos security protocol, passwords are not transferred through the network and Windows Authentication offers additional password policies that are not available for SQL Server logins. Some applications still require SQL Server logins for user authentication or for application service accounts. Read more about the advantages and disadvantages of SQL Server Authentication here.

SQL Server logins could be potentially misused (for example, the password is shared, the password is saved in clear text in an application connection string, login used where it’s not permitted, etc.).

Windows authentication is more secure, but we can face some challenges with this type of the authentication too. For example, when we want to restrict connections to the SQL Servers from specific hosts or when an account and password are shared (which is not a good practice).

We can use Windows firewall rules to allow connections from specific hosts. But what if we need setup a restriction for a specific login only? How can we monitor misused login attempts (both - Windows and SQL Server) and track logon anomalies?

Solution

You can setup a logon trigger to restrict specific users connections from dedicated hosts, but in some environments, there is no restriction setup on how users connect to SQL Server. There is potentially a policy in your company that says that users can’t connect in a specific way, but these security policies may not be enforced. Security monitoring is the best way to comply with the policies in these cases.

Every SQL Server should have at least a failed logins audit enabled. SQL Servers hosting databases for the critical applications should have both - failed and successful logins audit enabled. The successful logins audit may generate a huge amount of error log records that could be difficult to process.

Read this tip about reading multiple log files using T-SQL. 

In this tip we will provide you with scripts to generate a Custom Intrusion Detection Report. This report will read SQL Server error logs from multiple SQL Servers and filter them based on the list of allowed (“white-listed”) connections.

Here are some of the examples of potential security issues related to the logins misuse:

Prerequisites

• You need to have a list of SQL Servers with allowed logins and hosts. In our example we will be using a table on a Central Management Server (CMS) (we will be calling it the “White List” table).
• Monitored SQL Servers must have both – successful and failed logins audit enabled.
• CMS is configured and all SQL Servers that have to be monitored are registered there. Note, that you can modify the provided PowerShell script and read the list of SQL Servers from another source (for example, text file).
• CMS (or another SQL Server that will be running the PowerShell Script) needs to have the PowerShell version 4.0 or later (required to append records to a CSV file). This is not required if you are not going to use a PowerShell script (if you are going to use only T-SQL Script to run on a single SQL Server).
• We assume that there are no SQL Server tools (for example, SSMS) installed on non-database Application Servers and users don't run SQL queries on these servers. Current monitoring doesn't have filter by an application. So, we assume that any user connecting from an Application Server is an application user/service account.

Note, that the “White List” Table below has only IP addresses. Our monitoring example uses only a list of the connections using the TCP/IP protocol. We will provide later an example proving that the Named Pipes networking protocol could be difficult to monitor which might be a reason to limit the usage of it. Keep in mind that some applications still require the Named Pipes.

Read this tip that has a checklist with security recommendations for SQL Server configuration. Amongst with the recommendation to use Windows Authentication it has the recommendation to disable unused network protocols. Read this article to find out the difference between the Named Pipes and the TCP/IP Sockets.

The “White List” Table

Create the "White-list" table in a DB_Management database on CMS:

USE DB_Management
GO
CREATE   TABLE dbo.sec_AllowedConnections(
   LoginID nvarchar(100) NULL,
   AllowedIP varchar(20) NULL,
   AllowedSQLServer nvarchar(100) NULL
) ON [PRIMARY]
GO 

The columns are:

• LoginID -allowed login name that will be compared to the error log's records (from a login failed or succeeded event).
• AllowedIP - source IP address, a machine that is "white-listed" (user is allowed to connect from this machine to SQL Server). When this column's value is "NULL" it means that a user can connect from any IP address (this might be applicable for DBAs, but it's better to have a list of permitted hosts).
• AllowedSQLServer - destination SQL Server, the server to which user connects to. When this column's value is "NULL" it means that a user can connect to any SQL Server.

We will insert our test rules for the application accounts (svcTestApp1, svcTestApp2) and DBAs (DOMAIN\TestDBA1, DOMAIN\TestDBA2) into the White List:

USE DB_Management
GO
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp1','10.1.1.1','DEMOSRV1');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp1','10.1.1.2','DEMOSRV1');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp2','10.1.1.3','DEMOSRV2');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA1','10.1.1.4', 'DEMOSRV3');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','10.1.1.5', NULL);
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','10.1.1.6', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\svetlana','10.1.1.7', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\svetlana','10.1.1.6', NULL);
GO

Here is how we read the rules we have created:

• The Application Service Account svcTestApp1 can connect to DEMOSRV1 SQL Server from application servers 10.1.1.1 and 10.1.1.2.
• The Application Service Account svcTestApp2 can connect to DEMOSRV2 from an application server 10.1.1.3.
• A Junior DBA (DOMAIN\TestDBA1) can connect only to one Test SQL Server (DEMOSRV3) from a specific workstation 10.1.1.4.
• A Senior DBA (DOMAIN\TestDBA2) can connect to any SQL Server (NULL) from a workstation 10.1.1.5 and an Admin Server 10.1.1.6.
• Another DBA (DOMAIN\svetlana) can connect to any SQL Server (NULL) from a workstation 10.1.1.7 and an Admin Server 10.1.1.6.

PowerShell Script

The PowerShell script has the following steps:

1. Resets or creates an empty output CSV file ($OutputCSV variable).
2. Gets the list of monitored SQL Servers from the CMS ($CMS variable).
3. Creates a table for each monitored SQL Server in the TempDB database that will hold SQL Server specific white-listed connection rules.
4. Populates the table created in the previous step on each SQL Server with white-list rules. These are records from the CMS "White List" table filtered by the AllowedSQLServer column (AllowedSQLServer is current SQL Server's name).
5. Executes T-SQL Script ($InputSQL variable) with input parameters from the $SQLCmdVar variable. The results are appended to a CSV output file. Note, that the T-SQL Script will also drop at the end of the execution the table created in step 3.

$CMS = "DEMOCMS"
$InputSQL = "D:\DBA_Scripts\IDR_ReadErrorLog.sql"
$OutputCSV = "D:\DBA_Scripts\output\IDR_Report.csv"
$OutputErrorLog = "D:\DBA_Scripts\IDR_PSErrorLog.txt"
$SQLCmdVar = "LoginID='DOMAIN\TestDBA2'","dtFrom='2017-11-01'","dtTo='2017-12-29'"
$Inst = ""

# recreate output CSV file
$resetCSV = 'start'
SELECT $resetCSV  | Export-CSV ${OutputCSV}

# get all registered SQL Server names from CMS
Try
{
    $Result =  Invoke-Sqlcmd -ServerInstance ${CMS} -Database msdb -Query "select distinct server_name from msdb.dbo.sysmanagement_shared_registered_servers" -ErrorAction Stop
}
Catch 
{
 $ErrTime=Get-Date
    "Error getting list of servers from the CMS $CMS server at $ErrTime" | out-file $OutputErrorLog –append
}

# on each SQL Server create a table that will be removed later. 
#This table will hold server specific rules 

foreach($item in $Result)
 {
  $Inst=$item.server_name
 # Write-Host $Inst
  Try
  {
     Invoke-Sqlcmd -ServerInstance ${Inst} -Database tempdb -Query "IF (SELECT OBJECT_ID('sec_AllowedConnections')) > 0 BEGIN DROP TABLE dbo.sec_AllowedConnections END CREATE    TABLE dbo.sec_AllowedConnections(LoginID nvarchar(100) NULL, AllowedIP varchar (20) NULL)" -ErrorAction SilentlyContinue

Try{
    #Get rules for a specific SQL Server from CMS "White List" table 
    $IPList = Invoke-Sqlcmd -ServerInstance ${CMS} -Database DB_Management -Query "SELECT DISTINCT LoginID, AllowedIP FROM dbo.sec_AllowedConnections WHERE AllowedSQLServer = '${$Inst}' OR AllowedSQLServer  IS NULL" -ErrorAction Stop
}
Catch 
{
 $ErrTime=Get-Date
    "Error getting IPs list from the CMS $CMS server at $ErrTime" | out-file $OutputErrorLog –append
 Break
}
    #insert into temp table on each SQL Server IP and login
    foreach($IP in $IPList )
     { 
       $Login=$IP.LoginID
       $IP=$IP.AllowedIP

       Invoke-Sqlcmd -ServerInstance ${Inst} -Database tempdb -Query "INSERT INTO sec_AllowedConnections VALUES  ('${Login}','${IP}')"  -ErrorAction SilentlyContinue

     }
    # Output to the CSV file (append records).
    Invoke-Sqlcmd -ServerInstance ${Inst} -Database master -InputFile $InputSQL -Variable $SQLCmdVar -ErrorAction SilentlyContinue | Export-CSV -Append -NoTypeInformation ${OutputCSV}  
   
  }
  Catch 
  {
    $ErrTime=Get-Date
    "Error processing error logs on $Inst at $ErrTime " | out-file $OutputErrorLog -append 
  }
 }
 

T-SQL Script

The following T-SQL script is used by the PowerShell script above. The path to the script is specified in the PowerShell script's $InputSQL variable.

The T-SQL script takes the following parameters from the PowerShell script:

• $(LoginID) - to search SQL Server error logs for a specific login (if specified) or don't use this filter (pass an empty string). If you want to run T-SQL script using CMS or a single SQL Server (without the PowerShell Script) - replace "$(LoginID)" with your value (string).
• $(dtFrom) - search the error logs from a specific date. Replace with your value if don't use PowerShell script.
• $(dtTo) - search the error logs to a specific date. Replace with your value if don't use PowerShell script.

Date parameters used to limit the error logs processed using the output from the sp_enumerrorlogs stored procedure.

After that, each identified error log file is cleaned up and only "logon" events are listed in the result set.

Allowed connections rules are applied and these records are excluded from the final results that are combined with login failed events (unfiltered, as we want to see all of them). 

SET NOCOUNT ON

-- temp table to keep filtered logs
CREATE TABLE #audit 
          (LogDate     DATETIME, 
           ProcessInfo VARCHAR(100), 
           ErrorText   VARCHAR(1000), 
           LogID       INT)
  
DECLARE @Logs_Info  TABLE 
          (archiveNo SMALLINT, 
           MaxDate   VARCHAR(20), 
           LogSize   BIGINT)
 
DECLARE @login_to_audit NVARCHAR(100), 
        @dt_from        DATETIME, 
        @dt_to            DATETIME, 
        @NumErrorLogs    INT,   
        @ErrorLogID        INT, 
        @rowcount        INT,
        @ErrorLogStart    INT,
        @ErrorLogEnd    INT
 
-- PowerShell parameters: login to audit and dates from-to
SELECT  @login_to_audit =  ISNULL($(LoginID), '') , 
        @dt_from = CONVERT(DATETIME, $(dtFrom), 120) ,
        @dt_to = CONVERT(DATETIME, $(dtTo), 120) 
 
-- Get all available error logs
INSERT INTO @Logs_Info 
    EXEC sys.sp_enumerrorlogs
 
-- Find required error logs numbers based on the provided dates ("from" and "to")
SELECT @ErrorLogStart = MAX(archiveNo) 
    FROM @Logs_Info WHERE MaxDate >= @dt_from
 
SELECT @ErrorLogEnd = MIN(archiveNo) 
    FROM @Logs_Info WHERE MaxDate <= @dt_to
 
SET @ErrorLogID = @ErrorLogStart
   
WHILE @ErrorLogID > = @ErrorLogEnd
  BEGIN  
 
-- insert login audit events (filtered) from each applicable error log
     INSERT INTO #audit   (LogDate, ProcessInfo, ErrorText)
     EXEC [master].dbo.xp_readerrorlog @ErrorLogID,  1, N'login', @login_to_audit, @dt_from, @dt_to
 
      UPDATE #audit SET LogID = @ErrorLogID WHERE LogID IS NULL
      SET @ErrorLogID  = @ErrorLogID - 1
   
  END  
 
-- remove non-logon related events
DELETE FROM #audit WHERE ProcessInfo <> 'Logon'
 
-- Final Results
; WITH a AS
(
    SELECT CAST (LogDate as DATE) AS LogDateNoTime,  -- date only to roll-up the results
        a.LogID, 
        a.ErrorText,
        REPLACE(REPLACE(RIGHT(ErrorText, LEN(ErrorText)-CHARINDEX('[', ErrorText , 1) ),
         'CLIENT: ' , ''), ']', '') AS IPAddress,
        LEFT(RIGHT(ErrorText, LEN(ErrorText)-CHARINDEX('''', ErrorText)), 
         CHARINDEX('''', RIGHT(ErrorText, LEN(ErrorText)-CHARINDEX('''', ErrorText)))-1) as LoginID
    FROM #audit a 
    WHERE  ErrorText LIKE '%Login [fs]%for user%' -- login failed or succeeded
)
, b AS 
(
    SELECT @@SERVERNAME AS Server_Name,  
            a.LogDateNoTime,  
            a.LogID, 
            REPLACE(REPLACE(a.ErrorText, CHAR(13), ''), CHAR(10), '')  AS ErrorText,
            REPLACE(REPLACE(a.IPAddress, CHAR(13), ''), CHAR(10), '')  AS IPAddress, 
            a.LoginID
    FROM a LEFT JOIN tempdb.dbo.sec_AllowedConnections ip 
        ON a.IPAddress = ip.AllowedIP 
            AND a.LoginID = ip.LoginID 
    WHERE  ip.AllowedIP IS NULL 
 
    UNION ALL
    
    SELECT @@SERVERNAME AS Server_Name,   
            CAST (LogDate as DATE) AS LogDateNoTime, 
            a.LogID, 
            REPLACE(REPLACE(a.ErrorText, CHAR(13), ''), CHAR(10), '')  AS ErrorText,
            REPLACE(REPLACE(REPLACE(REPLACE(RIGHT(ErrorText, LEN(ErrorText)-
            CHARINDEX('[', ErrorText, 1) ), 'CLIENT: ', ''), ']', ''), CHAR(13), ''), CHAR(10), '') 
              as IPAddress,
            'Uknown' as LoginID
    FROM  #audit a LEFT JOIN tempdb.dbo.sec_AllowedConnections ip 
        ON REPLACE(REPLACE(RIGHT(ErrorText, LEN(ErrorText)-
             CHARINDEX('[' ,ErrorText , 1) ), 'CLIENT: ', ''), ']', '') = ip.AllowedIP  
    WHERE ErrorText NOT LIKE '%Login [fs]%for user ''%'  -- other than login failed or succeeded events
        AND ip.AllowedIP IS NULL
)
SELECT COUNT(LogID) AS NoOfEvents,
        Server_Name,  
        LogDateNoTime,  
        LogID, 
        ErrorText, 
        IPAddress, 
        LoginID 
FROM b
WHERE   ErrorText NOT LIKE '%SQL Server service is paused%' AND 
        ErrorText NOT LIKE '%Only administrators may connect at this time%' 
         /* 
         AND ErrorText NOT LIKE '%%' -- Add other filters if required
        */
GROUP BY Server_Name,  LogDateNoTime,  LogID, ErrorText, IPAddress, LoginID 
ORDER BY LogDateNoTime DESC, LoginID
 
DROP  TABLE #audit
 
-- Drop the table created by the PowerShell script
IF (SELECT OBJECT_ID ('tempdb.dbo.sec_AllowedConnections')) IS NOT NULL
BEGIN
    DROP TABLE tempdb.dbo.sec_AllowedConnections
END 

SQL Server Intrusion Detection Sample Reports

In our example we will run a report against a couple of new demo SQL Servers.

Sample Report 1 - With LoginID Filter

In this example we used the following filters:

$SQLCmdVar = "LoginID='DOMAIN\svetlana'","dtFrom='2018-07-05'","dtTo='2018-07-07'"

We have generated the report only for a specific login (DOMAIN\svetlana).

We can see that multiple connections were made from the new workstation (10.XX.XX.51) which is not in the White-List table. Based on the audit/security requirements we can add this workstation to the White-List or notify the DBA that she is supposed to use only dedicated machines for the SQL Servers connections. 10.XX.XX.20 is also not permitted and shows up on this report as a suspicious connection.

Sample Report 2 - Without LoginID Filter

In this example we used the following filters:

$SQLCmdVar = "LoginID=''","dtFrom='2018-07-15'","dtTo='2018-07-16'"

The report was created for all logins who tried to login during the specified timeframe.

Here is how we reviewed the results of our report above:

• Red records are connections from un-authorized hosts or failed logins.
• The records highlighted in yellow are connections made using the Named Pipes. As you can see there is not much useful information that we can for the report's filtering. That's why we prefer to limit our protocols to TCP/IP.
• Other records have to be added to the White-List table as these are all allowed connections

Next Steps

• Check out all security tips here.
• Read this tip about security audit/reviews.
• This tip has scripts to help you with your security reviews.
• Use this Checklist to harden your new SQL Server security.
• Find more information about setting up SQL Server alerts here.
• Read these tips about SQL Server error logs:
  • Best Practice - Increase the Number of SQL Server Error Logs
  • Simple way to find errors in SQL Server error log
  • SQL Server 2005 Error Log Management
  • Search multiple SQL Server Error Logs at the same time

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips