Adding failed SQL Server Logon Support to a PLM SQL Server

[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:]

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:]

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)


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:



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 (1)

  1. mike says:

    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 ?

Skip to main content