Post Views 471 views
- Auditing in SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server trace to audit events (fn_trace_gettable)
- SQL Server – Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)
- SQL Server 2016 - How to "time travel" using the Temporal Tables feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes in tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to avoid brute force attacks on your database
- SQL Server – Security Checklist – An SP with over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
Hey guys!
In this post, I would like to show you how to create a login audit using instance logs, which is a native SQL Server option and does not require the creation of any additional resources such as trigger, Extended Events, Audit, etc..
I already published the article SQL Server - How to implement login auditing and control (Logon Trigger) using a trigger and it turned out to be a very interesting solution, but which, like all login triggers, can pose some risks if it is not well implemented and prevent user logins.
I also showed a similar solution in the article SQL Server - How to avoid brute force attacks on your database, but the focus was only connection failures by nonexistent user or incorrect password.
How to enable auditing of logins in SQL Server
To activate auditing of SQL Server logins, first open the instance in the “Object Explorer”, right-click and select the “Properties” option
Click on the “Security” tab and then in the “Login Auditing” category, check the option “Both failed and successful logins”
Another way to enable this is using this T-SQL script:
|
1 2 3 4 |
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3 GO |
After making this change, restart the SQL Server service through SQL Server Configuration Manager.
Important: In case the SQL Server Configuration Manager is not appearing for you in the start menu, read the article SQL Server Configuration Manager gone? Learn how to retrieve the shortcut.
How to read collected audit data
To read the collected audit data, we will use the internal procedure xp_readerrorlog, which reads data from SQL Server logs.
To learn more about this procedure and other internal SQL Server procedures, read the article The Undocumented SQL Server Extended Procedures.
Below is the script you can use to capture this data from SQL Server logs and store it in a physical table in your database, to keep history.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
-------------------------------------------------------------- -- Create the temporary tables -------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Arquivos_Log') IS NOT NULL) DROP TABLE #Arquivos_Log CREATE TABLE #Arquivos_Log ( [idLog] INT, [dtLog] NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AI, [tamanhoLog] INT ) IF (OBJECT_ID('tempdb..#Audit_Login') IS NOT NULL) DROP TABLE #Audit_Login CREATE TABLE #Audit_Login ( [LogNumber] TINYINT NULL, [LogDate] DATETIME NOT NULL, [ProcessInfo] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL, [Username] VARCHAR(200) NULL, [AuthenticationType] VARCHAR(20) NULL, [Result] VARCHAR(20) NULL, [IP] VARCHAR(100) NULL, [Reason] VARCHAR(500) NULL, [Text] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ) IF (OBJECT_ID('dbo.Login_Audit') IS NULL) BEGIN -- DROP TABLE [dbo].[Login_Audit] CREATE TABLE [dbo].[Login_Audit] ( [LogNumber] TINYINT NOT NULL, [LogDate] DATETIME NOT NULL, [ProcessInfo] NVARCHAR(50) NOT NULL, [Username] VARCHAR(200) NOT NULL, [AuthenticationType] VARCHAR(20) NULL, [Result] VARCHAR(20) NULL, [IP] VARCHAR(100) NOT NULL, [Reason] VARCHAR(500) NULL ) WITH(DATA_COMPRESSION=PAGE) CREATE CLUSTERED INDEX SK01_Login_Audit ON dbo.Login_Audit(LogDate) WITH(DATA_COMPRESSION=PAGE) END -------------------------------------------------------------- -- Import the ERRORLOG files -------------------------------------------------------------- INSERT INTO #Arquivos_Log EXEC sys.sp_enumerrorlogs -------------------------------------------------------------- -- loop to look for login attempts -------------------------------------------------------------- DECLARE @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Arquivos_Log), @Ultima_Hora VARCHAR(19) = FORMAT(ISNULL((SELECT DATEADD(SECOND, 1, MAX([LogDate])) FROM dbo.Login_Audit), '1990-01-01'), 'yyyy-MM-dd HH:mm:ss'), @Agora VARCHAR(19) = CONVERT(VARCHAR(19), GETDATE(), 121) WHILE(@Contador < @Total) BEGIN -- Login failure search INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login failed for user ', NULL, @Ultima_Hora, @Agora -- Search for successful login attempt INSERT INTO #Audit_Login (LogDate, ProcessInfo, [Text]) EXEC master.dbo.xp_readerrorlog @Contador, 1, N'Login succeeded for user ', NULL, @Ultima_Hora, @Agora -- Update log file number UPDATE #Audit_Login SET LogNumber = @Contador WHERE LogNumber IS NULL SET @Contador += 1 END ------------------------------------------------------- -- Populate additional columns ------------------------------------------------------- UPDATE [#Audit_Login] SET [Username] = (CASE WHEN [Text] LIKE '%Login failed%' THEN LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(REPLACE([Text], 'Login failed for user ''', ''), 1, CHARINDEX('. Reason:', REPLACE([Text], 'Login failed for user ''', '')) - 2), CHAR(10), ''), CHAR(13), ''))) ELSE LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(REPLACE([Text], 'Login succeeded for user ''', ''), 1, CHARINDEX('''', REPLACE([Text], 'Login succeeded for user ''', '')) - 1), CHAR(10), ''), CHAR(13), ''))) END), [IP] = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE((SUBSTRING([Text], CHARINDEX('[CLIENT: ', [Text]) + 9, LEN([Text]))), ']', ''), CHAR(10), ''), CHAR(13), ''))) UPDATE [#Audit_Login] SET [AuthenticationType] = (CASE WHEN [Text] LIKE '%Connection made using Integrated authentication.%' THEN 'Windows' WHEN [Text] LIKE '%Connection made using SQL Server authentication.%' THEN 'SQL Server' ELSE (CASE WHEN [Username] NOT LIKE '%@%' AND [Username] NOT LIKE '%\%' THEN 'SQL Server' ELSE 'Windows' END) END), [Result] = (CASE WHEN [Text] LIKE 'Login failed for user%' THEN 'Fail' WHEN [Text] LIKE 'Login succeeded for user%' THEN 'Sucess' END) UPDATE [#Audit_Login] SET [Reason] = (CASE WHEN [Text] LIKE '%Login failed%' THEN LTRIM(RTRIM(SUBSTRING([Text], CHARINDEX('. Reason:', [Text]) + 9, LEN([Text])))) END) WHERE [Result] = 'Fail' UPDATE [#Audit_Login] SET [Reason] = SUBSTRING([Reason], 1, CHARINDEX(' [CLIENT', [Reason]) - 1) WHERE [Result] = 'Fail' INSERT INTO dbo.Login_Audit SELECT [LogNumber], [LogDate], [ProcessInfo], [Username], [AuthenticationType], [Result], [IP], [Reason] FROM [#Audit_Login] |
Resultado da auditoria:
With this data, you can now create various monitoring, analyses, follow the number of logins, check if you are having a brute force attack or know if a certain user is logging in or not in the database.
I hope you enjoyed this tip and until next time!
Portuguese 
English