sql defrag 

Custom Intrusion Detection Reporting for SQL Server



By:   |   Read Comments   |   Related Tips: More > 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:






Security Control Audit Issue / Example
An Application Service Account can be only used by a specific Application
and nobody supposed to use this login for other activities. Non-authorized
access (SQL Server login).
Somebody used SQL Server login (an application service account) by logging
in from non-authorized application/server. Example: Developer connected
from Visual Studio using saved credentials in a database connection string.
DBAs need to run queries remotely with

SQL Server Management Studio (SSMS)
from their Desktops or Dedicated
Admin Servers
A DBA ran a query with SSMS by logging in to the Production SQL Server
machine.
Non-authorized access (Windows Login). A Junior DBA that has access only to the Test Servers tried to connect
to a Production SQL Server Instance.

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 (DOMAINTestDBA1, DOMAINTestDBA2) 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 ('DOMAINTestDBA1','10.1.1.4', 'DEMOSRV3');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAINTestDBA2','10.1.1.5', NULL);
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAINTestDBA2','10.1.1.6', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAINsvetlana','10.1.1.7', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAINsvetlana','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 (DOMAINTestDBA1) can connect only to one Test SQL Server (DEMOSRV3)
    from a specific workstation 10.1.1.4.
  • A Senior DBA (DOMAINTestDBA2) can connect to any SQL Server (NULL) from
    a workstation 10.1.1.5 and an Admin Server 10.1.1.6.
  • Another DBA (DOMAINsvetlana) 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.


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=’DOMAINsvetlana'”,”dtFrom=’2018-07-05′”,”dtTo=’2018-07-07′”

We have generated the report only for a specific login (DOMAINsvetlana).

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-03′”

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

audit report

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

  • Red records are connections from un-authorized hosts or failed logins.
  • 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:


Last Update:


next webcast button

next tip button

About the author

MSSQLTips author Svetlana Golovko

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

View all my tips


Related posts

Leave a Comment