Custom Intrusion Detection Reporting for SQL Server

By:   |   Comments   |   Related: > Auditing and Compliance


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?


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.


  • 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
CREATE   TABLE dbo.sec_AllowedConnections(
   LoginID nvarchar(100) NULL,
   AllowedIP varchar(20) NULL,
   AllowedSQLServer nvarchar(100) NULL

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
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp1','','DEMOSRV1');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp1','','DEMOSRV1');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('svcTestApp2','','DEMOSRV2');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA1','', 'DEMOSRV3');
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','', NULL);
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\svetlana','', NULL); 
INSERT INTO  dbo.sec_AllowedConnections VALUES ('DOMAIN\svetlana','', NULL);

Here is how we read the rules we have created:

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

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.
$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
    $Result =  Invoke-Sqlcmd -ServerInstance ${CMS} -Database msdb -Query "select distinct server_name from msdb.dbo.sysmanagement_shared_registered_servers" -ErrorAction Stop
    "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)
 # Write-Host $Inst
     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

    #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
    "Error getting IPs list from the CMS $CMS server at $ErrTime" | out-file $OutputErrorLog –append
    #insert into temp table on each SQL Server IP and login
    foreach($IP in $IPList )

       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}  
    "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). 


-- temp table to keep filtered logs
          (LogDate     DATETIME, 
           ProcessInfo VARCHAR(100), 
           ErrorText   VARCHAR(1000), 
           LogID       INT)
          (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
    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
-- 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
-- remove non-logon related events
DELETE FROM #audit WHERE ProcessInfo <> 'Logon'
-- Final Results
    SELECT CAST (LogDate as DATE) AS LogDateNoTime,  -- date only to roll-up the results
        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,  
            REPLACE(REPLACE(a.ErrorText, CHAR(13), ''), CHAR(10), '')  AS ErrorText,
            REPLACE(REPLACE(a.IPAddress, CHAR(13), ''), CHAR(10), '')  AS IPAddress, 
    FROM a LEFT JOIN tempdb.dbo.sec_AllowedConnections ip 
        ON a.IPAddress = ip.AllowedIP 
            AND a.LoginID = ip.LoginID 
    WHERE  ip.AllowedIP IS NULL 
    SELECT @@SERVERNAME AS Server_Name,   
            CAST (LogDate as DATE) AS LogDateNoTime, 
            REPLACE(REPLACE(a.ErrorText, CHAR(13), ''), CHAR(10), '')  AS 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
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
    DROP TABLE tempdb.dbo.sec_AllowedConnections

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).

audit report

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.

audit report

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

get free sql tips
agree to terms