Adding failed SQL Server Logon Support to a PLM SQL Server

Artikel
03/07/2011

[Prior Post in Series] [Next Post in Series]

In my prior post on securing PLM systems (Enovia and Windchill) I cited that SQL Server does not disable accounts after N failed attempts, allowing a malicious agent to do a brute force attack on a known account like “sa”. In many cases, just walking a list of common passwords will allow access. Disabling such accounts is one good strategy to prevent unauthorized access. A complimentary strategy is to be aware of the attempts and then seek the cause with a goal of eliminating the source. If there is a malicious agent on your network, you want to know it, identify how it got in and eliminate it.

In this post I will look at three facets of Payment Card Industry (PCI) Data Security Standard Requirements and Security Assessment Procedures Version 2.0 (PCIDSS)

Login Support
- Limit repeated access attempts by locking out the user ID after not more than six attempts
- Set the lockout duration to a minimum of 30 minutes or until administrator enables the user ID
- Remove or disable inactive user accounts at least every 90 days

The Simplest Solution

SQL Server provides the ability to audit failed logons; it is normally off (None) after a standard installation. With ISV systems using a common login to access SQL Server, there is little benefit from auditing a successful logon because these will not be user logons, rather the logon used by the JAVA, .Net or C++ components. A failed logon indicates someone is attempting to access SQL Server.

Step 1: Right click on the SQL Server in SSMS and select [Properties]. Click on the [Security] page.

Step 2: Select on [Failed logons only]. Then click [OK] to close the dialog.

The failed logon information is recorded but this is a weak solution because:

Someone must check the log (human-ware is the weakest security point always)
No one is notified when repeated failed attempts occur
Accounts under attack are not automatically disabled (assuming they are not on the production white list).

An example of the failed logon information is shown below. The log records a very important bit of information, the IP address of where the request originated.

Error: 18456, Severity: 14, State: 11.

Logon failed for user 'SERVER2008X64\Ken.Lassesen'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 172.16.0.17]

Error: 18456, Severity: 14, State: 58.

Logon failed for user 'sa'. Reason: An attempt to logon using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 172.16.0.17]

A Better Solution

Most SQL Developers are familiar with triggers. Triggers come in two varieties: Data Definition Language (DDL) and Data Manipulation Language (DML). Tables use DML triggers. DDL triggers have database or server scope. For a list of triggering events, see DDL Events on MSDN. There is a minor problem because there is a successful logon event but no failed logon event (a future version of SQL Server may add a failed logon event).

The first part is to get logon failures from the log cited above and place them in a reference table [Security.dbo.LogonSecurity]. I would advocate that the reference table be in a separate database [Security] to insure that access to it is restricted. The key is to use an ‘undocumented’ extended stored procedure xp_readerrorlog. The term ‘undocumented’ means that it officially does not exist – use at your own risk, but you can find documentation for it. For example, doing a web search finds it cited over 10,000,000 times. Executing the command below returns only logon failures.

 exec xp_readerrorlog 0, 1, 'Logon failed for user'

as show below.

The next step is to capture the information into a table and extract the user name and the IP Address. The following code illustrates how to do it.

 DECLARE @quote varchar(1)
SET @quote=''''
CREATE TABLE #FailedLog(FailedAt DateTime,ProcessInfo sysname, FailMessage nvarchar(max))
INSERT #FailedLog exec xp_readerrorlog 0, 1, 'Logon failed for user' 
ALTER TABLE #FailedLog
    ADD UserName Sysname
ALTER TABLE #FailedLog    
    ADD IPAddress Sysname
UPDATE #FailedLog    
SET UserName=
            Substring(FailMessage,
            CharIndex(@quote,FailMessage,0)+1,
            CharIndex(@quote,FailMessage,CharIndex(@quote,FailMessage,0)+1)
            -CharIndex(@quote,FailMessage,0)-1 )
,IPAddress=Substring(FailMessage,
            CharIndex('[',FailMessage,0)+1,
            CharIndex(']',FailMessage,CharIndex(@quote,FailMessage,0)+1)
            -CharIndex('[',FailMessage,0)-1 )
Select * FROM #FailedLog 
drop table  #FailedLog

Producing the following result:

The Coding Solution

The approach that I am going to use is to create a stored procedure that would be scheduled to execute regularly (perhaps every 2 minutes) and a DDL Trigger. From this framework you should be able to create a solution specific to what your security officer expects. This is an illustration framework only.

First, we create a persistent table with [IPAddress] + [LogonName] being the primary key.

 Create table LogonSecurity (
[LogonName] varchar(120) NOT NULL,
[IPAddress] varchar(24) NOT NULL,
[FailedCount] int Default(0),
[LastFailed] DateTime Default(GetDate()-365),
[SuccessCount] int Default(0),
[LastSuccess] DateTime Default(GetDate()-365))

The following captures failed logons since the last successful logon (if any):

 CREATE PROC p_ImportFailedLogon AS
DECLARE @quote varchar(1)
SET @quote=''''
SET NOCOUNT ON;
BEGIN
    CREATE TABLE #FailedLog(FailedAt DateTime,ProcessInfo varchar(120), FailMessage nvarchar(max))
    INSERT #FailedLog exec xp_readerrorlog 0, 1, 'Logon failed for user' 
    ALTER TABLE #FailedLog
        ADD UserName varchar(120)
    ALTER TABLE #FailedLog    
        ADD IP varchar(24)
    UPDATE #FailedLog    
    SET UserName=
                Substring(FailMessage,
                CharIndex(@quote,FailMessage,0)+1,
                CharIndex(@quote,FailMessage,CharIndex(@quote,FailMessage,0)+1)
                -CharIndex(@quote,FailMessage,0)-1 )
            ,IP=Substring(FailMessage,
                CharIndex('[',FailMessage,0)+9,
                CharIndex(']',FailMessage,CharIndex(@quote,FailMessage,0)+1)
                -CharIndex('[',FailMessage,0)-9 )
    --Add Missing Logons/Clients
    Insert LogonSecurity(LogonName,IPAddress)
    Select DISTINCT UserName,IP FROM #FailedLog 
    LEFT JOIN LogonSecurity
    ON IP COLLATE Latin1_General_CI_AS=IPADDRESS 
    AND UserName COLLATE Latin1_General_CI_AS=LogonName
    WHERE IPAddress IS NULL
    -- Update the Failed Count
    UPDATE LogonSecurity
        SET FailedCount=FailedCount+Fails, LastFailed = LastDate
    FROM LogonSecurity JOIN         
    (SELECT Count(1) As Fails,Max(FailedAt) As LastDate, IP,UserName FROM LogonSecurity JOIN #FailedLog
    ON IP COLLATE Latin1_General_CI_AS=IPADDRESS 
    AND UserName COLLATE Latin1_General_CI_AS=LogonName
    WHERE FailedAt > LastFailed
    AND FailedAt > LastSuccess
    GROUP BY IP,UserName) Upd
    ON IP COLLATE Latin1_General_CI_AS=IPADDRESS 
    AND UserName COLLATE Latin1_General_CI_AS=LogonName    
END

Executing the above and examining our table [LogonSecurity] shows:

Our next code is the DDL Trigger that records success (or denies the logon if more than three failed attempts have occurred).

 CREATE TRIGGER Logon_Security
ON ALL SERVER 
FOR LOGON
AS
BEGIN
DECLARE @UserName varchar(120)
DECLARE @IP varchar(24)
DECLARE @PostTime DateTime
Declare @Data Xml
Set @Data=EVENTDATA()
SET @IP=@Data.value('(EVENT_INSTANCE/ClientHost)[1]','varchar(24)')
SET @UserName=@Data.value('(EVENT_INSTANCE/LogonName)[1]','varchar(120)')
SET @PostTime=@Data.value('(EVENT_INSTANCE/PostTime)[1]','DateTime')
IF EXISTS(Select 1 from Security.dbo.LogonSecurity 
WHERE @IP=IPAddress 
AND @UserName=LogonName 
AND [FailedCount] > 3)
    BEGIN
        ROLLBACK 
        RETURN
    END
IF NOT Exists(Select 1 from Security.dbo.LogonSecurity WHERE @IP=IPAddress AND @UserName=LogonName)
    BEGIN
        INSERT INTO Security.dbo.LogonSecurity(LogonName,IPAddress,[SuccessCount],[LastSuccess])
        VALUES(@UserName,@IP,1,@PostTime)
    END
ELSE
    BEGIN
        UPDATE Security.dbo.LogonSecurity
        SET [SuccessCount]=[SuccessCount]+1,
            [LastSuccess]=@PostTime,
            [FailedCount]=0
        WHERE LogonName=@UserName AND IPAddress=@IP
    END
END

The table is updated on every successful connection, as shown below:

The result if a valid logon is done with more than 3 failed logons recorded.

Locking out for 30 minutes

The above code locks out the account until the account is enabled by setting [FailedCount] to zero. We may modify it to allow retries after 30 minutes with the following code. The main advantage of this variation is less administrator involvement.

 IF EXISTS(Select 1 from Security.dbo.LogonSecurity 
WHERE @IP=IPAddress 
AND @UserName=LogonName 
AND [FailedCount] > 3 
AND GetDate() > DateAdd(mm,30,LastFailed))
    BEGIN
        ROLLBACK 
        RETURN
    END
IF EXISTS(Select 1 from Security.dbo.LogonSecurity 
WHERE @IP=IPAddress 
AND @UserName=LogonName 
AND [FailedCount] > 30)
    BEGIN
        ROLLBACK 
        RETURN
    END

Locking out if unused for 90 days

This may be done by changing the Security.dbo.LogonSecurity table definition to:

 [LastSuccess] DateTime Default(GetDate()))

And adding this code to the trigger:

 IF NOT EXISTS(Select 1 from Security.dbo.LogonSecurity
     WHERE @UserName=LoginName 
    AND GetDate() < DateAdd(dd,90,LastSuccess))    
BEGIN
        ROLLBACK 
        RETURN
    END

There is no need to filter by IP address.

Locking out after six attempts

The above code does a lockout after 3 attempts, so adjust the value appropriate for your situation. The gotcha is that we are detecting the failed logons via p_ImportFailedLogon. This means that you need to run it frequently, I would suggest every minute. If you are not familiar with creating SQL Server Agent jobs see How to: Create a SQL Server Agent Job (Transact-SQL)

Summary

While there is no native SQL Server support exists for disabling an account after multiple failed attempts, implementing it in SQL Server is not difficult. The above framework can be easily modified to do many things, for example:

Sending an email when the failed count reaches a certain threshold
Disabling the account at the SQL Server level (the above solution disables the account when used from one specific client only)
Disabling logons from a specific client (IP Address) if more than N successive failed logons occur (regardless of logon)

A variation of the above would be to create a white list of IP addresses in a table and fail every logon attempt not found in the white list table.

Caution is Advised

If you blow the coding of the trigger, you may be unable to make ANY new connections to the database or SQL Server. Always keep at least two connected instances of SSMS open when you are defining and testing DDL triggers involving the logon. I have accidentally closed SSMS during debugging frustrations and put myself into a very awkward situation.

If you encounter problems with one of your open connections, execute:

 DROP TRIGGER Logon_Security ON ALL SERVER

If you forget the “ON ALL SERVER”, you may get very confused because the simpler form will work but new connections may still fail.

 DROP TRIGGER Logon_Security -- This is not sufficient

The reason to do “ALL” is simple – once a connection is made, the database may be changed on the connection – specifying the trigger on just one database allows the trigger to be bypassed.

Comments

Anonymous
March 04, 2013
Very good article ! On point however about the Trigger insertion (INSERT INTO security.dbo.LogonSecurity...). The insertion is executed by each user who open a connection. By default, the table is not accessible to any users leading therefore to a trigger issue. You can grant the access (GRANT INSERT,SELECT,UPDATE ON [master].[dbo].[logonsecurity] TO [public]) but it still doesnt work for accounts which only have a Login and not a User account. An idea would be to start the trigger as another user which only have the good rights on this table (Executed as...) but I'm not sure it is realizable so easily within SQLServer. Any idea how to solve it ?

Freigeben über