CIS benchmark automation

The beauty of this CIS benchmark automation created by me is pure SQL. It also allows you to add and change the verification rules.

USE [master]
GO

/****** Object:  Table [dbo].[ErrorLog]    Script Date: 22/04/2024 11:42:32 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ErrorLog](
    [errorTime] [datetime] NOT NULL,
    [errorMsg] [nvarchar](2048) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_errorTime]  DEFAULT (getdate()) FOR [errorTime]
GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[error_handler_sp]    Script Date: 22/04/2024 11:43:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER   PROC [dbo].[error_handler_sp] AS
BEGIN
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
          
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
      
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' +
                       ltrim(str(@errno)) + ': ' + @errmsg
   END

   INSERT ErrorLog (errorMsg) VALUES (@errmsg);

   RAISERROR('%s', @severity, @state, @errmsg)
END
GO

USE [master]
GO

/****** Object:  Table [dbo].[CISBenchmark]    Script Date: 22/04/2024 11:45:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CISBenchmark](
    [ruleNumber] [varchar](5) NOT NULL,
    [description] [varchar](200) NOT NULL,
    [verifyQuery] [nvarchar](4000) NOT NULL,
    [expectedValue] [varchar](100) NULL,
    [remediation] [nvarchar](4000) NULL,
    [comparison] [varchar](10) NOT NULL,
    [manual] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CISBenchmark] ADD  CONSTRAINT [DF_CISBenchmark_resultLike]  DEFAULT ('equal') FOR [comparison]
GO

ALTER TABLE [dbo].[CISBenchmark] ADD  CONSTRAINT [DF_CISBenchmark_manual]  DEFAULT ((0)) FOR [manual]
GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_CheckCISBenchmark]    Script Date: 22/04/2024 11:47:13 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE OR ALTER   PROC [dbo].[usp_CheckCISBenchmark]
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #CISResults (ruleNumber varchar(5), queryResult nvarchar(4000));
DECLARE @tblCurrQryResult table (queryResult nvarchar(4000));
DECLARE @ruleNumber varchar(5), @verifyQuery nvarchar(4000);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT ruleNumber, verifyQuery FROM CISBenchmark ORDER BY ruleNumber;
OPEN cur
WHILE 1 = 1
BEGIN
    FETCH cur INTO @ruleNumber, @verifyQuery;
    IF @@FETCH_STATUS <> 0 BREAK;
   
    BEGIN TRY
    BEGIN TRAN
    
    DELETE @tblCurrQryResult;
    INSERT @tblCurrQryResult EXEC sp_executesql @verifyQuery;
    INSERT #CISResults SELECT @ruleNumber, queryResult FROM @tblCurrQryResult;

    COMMIT
    END TRY
    BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    EXEC error_handler_sp;
    END CATCH
END
CLOSE cur;
DEALLOCATE cur;

WITH CTE AS (
SELECT R.ruleNumber, B.[description], B.verifyQuery, R.queryResult, B.expectedValue,
CASE
WHEN B.manual = 1 THEN 'MANUAL'
WHEN B.comparison = 'like' THEN
    CASE WHEN R.queryResult LIKE '%'+B.expectedValue+'%' THEN 'PASS'
    ELSE 'FAIL'
    END
WHEN B.comparison = '>' THEN
    CASE WHEN CAST(R.queryResult AS float) > CAST(B.expectedValue as float) THEN 'PASS'
    ELSE 'FAIL'
    END
WHEN B.comparison = '>=' THEN
    CASE WHEN CAST(R.queryResult AS float) >= CAST(B.expectedValue as float) THEN 'PASS'
    ELSE 'FAIL'
    END
WHEN B.comparison = '<' THEN
    CASE WHEN CAST(R.queryResult AS float) < CAST(B.expectedValue as float) THEN 'PASS'
    ELSE 'FAIL'
    END
WHEN B.comparison = '<=' THEN
    CASE WHEN CAST(R.queryResult AS float) <= CAST(B.expectedValue as float) THEN 'PASS'
    ELSE 'FAIL'
    END
ELSE
    CASE WHEN R.queryResult  = B.expectedValue THEN 'PASS'
    ELSE 'FAIL'
    END
END
AS pass,
B.comparison,
B.remediation
FROM #CISResults AS R JOIN CISBenchmark AS B ON R.ruleNumber = B.ruleNumber
) SELECT ruleNumber, [description], verifyQuery, queryResult, expectedValue AS expectedResult, pass, comparison,
CASE pass WHEN 'PASS' THEN '' ELSE remediation END AS remediation
FROM CTE
ORDER BY CAST(LEFT(ruleNumber, CHARINDEX('.', ruleNumber)-1) AS int), CAST(RIGHT(ruleNumber, LEN(ruleNumber) - CHARINDEX('.', ruleNumber)) AS int);
END;
GO


USE [master]
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'1.1', N'Ensure Latest SQL Server Cumulative and Security Updates are
Installed (Manual)', N'SELECT @@VERSION;', N'CU25', N'Identify the current version and patch level of your SQL Server instances and ensure they
contain the latest security fixes.', N'like', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.1', N'Ensure ''Ad Hoc Distributed Queries'' Server Configuration Option is
set to ''0'' (Automated)', N'SELECT CAST(value_in_use AS bit) FROM sys.configurations WHERE [name] = ''Ad Hoc Distributed Queries'';', N'0', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''Ad Hoc Distributed Queries'', 0;
RECONFIGURE;
GO
14 | P a g e
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.2', N'Ensure ''CLR Enabled'' Server Configuration Option is set to ''0''', N'SELECT CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''clr enabled'';', N'0', N'EXECUTE sp_configure ''clr enabled'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.3', N'Ensure ''Cross DB Ownership Chaining'' Server Configuration Option is
set to ''0'' (Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''cross db ownership chaining'';', N'0', N'EXECUTE sp_configure ''cross db ownership chaining'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.4', N'Ensure ''Database Mail XPs'' Server Configuration Option is set to ''0''
(Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''Database Mail XPs'';', N'0', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''Database Mail XPs'', 0;
RECONFIGURE;
GO
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.5', N'Ensure ''Ole Automation Procedures'' Server Configuration Option is
set to ''0'' (Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''Ole Automation Procedures'';', N'0', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''Ole Automation Procedures'', 0;
RECONFIGURE;
GO
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.7', N'Ensure ''Remote Admin Connections'' Server Configuration Option is
set to ''0'' (Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''remote admin connections'';', N'0', N'EXECUTE sp_configure ''remote admin connections'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.8', N'Ensure ''Scan For Startup Procs'' Server Configuration Option is set to
''0'' (Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''scan for startup procs'';', N'0', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''scan for startup procs'', 0;
RECONFIGURE;
29 | P a g e
GO
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.9', N'Ensure ''Trustworthy'' Database Property is set to ''Off'' (Automated)', N'SELECT COUNT(1) AS trustworthyDb
FROM sys.databases
WHERE is_trustworthy_on = 1
AND name != ''msdb'';', N'0', N'ALTER DATABASE [<database_name>] SET TRUSTWORTHY OFF;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.11', N'Ensure SQL Server is configured to use non-standard ports
(Automated)', N'SELECT COUNT(1) FROM sys.dm_exec_connections WHERE local_tcp_port = 1433;', N'0', N'Change the TCP Port field from 1433 to a non-standard port', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.6', N'Ensure ''Remote Access'' Server Configuration Option is set to ''0''
(Automated)', N'SELECT
CAST(value_in_use AS bit) as value_in_use
FROM sys.configurations
WHERE name = ''remote access'';', N'0', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''remote access'', 0;
RECONFIGURE;
GO', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.12', N'Ensure ''Hide Instance'' option is set to ''Yes'' for Production SQL
Server instances (Automated)', N'DECLARE @getValue INT;
EXEC master.sys.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'',
@value_name = N''HideInstance'',
@value = @getValue OUTPUT;
SELECT @getValue;', N'1', N'1. In SQL Server Configuration Manager, expand SQL Server Network
Configuration, right-click Protocols for <InstanceName>, and then select
Properties.
2. On the Flags tab, in the Hide Instance box, select Yes, and then click OK to close the
dialog box. The change takes effect immediately for new connections.', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.14', N'Ensure the ''sa'' Login Account has been renamed (Automated)', N'SELECT COUNT(1)
FROM sys.server_principals
WHERE sid = 0x01 AND [name] = ''sa'';', N'0', N'ALTER LOGIN sa WITH NAME = ''dbadmin'';', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.13', N'Ensure the ''sa'' Login Account is set to ''Disabled'' (Automated)', N'SELECT COUNT(1)
FROM sys.server_principals
WHERE sid = 0x01
AND is_disabled = 0;', N'0', N'USE [master]
GO
DECLARE @tsql nvarchar(max)
SET @tsql = ''ALTER LOGIN '' + SUSER_NAME(0x01) + '' DISABLE''
EXEC (@tsql)
GO', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.16', N'Ensure no login exists with the name ''sa'' (Automated)', N'SELECT COUNT(1)
FROM sys.server_principals
WHERE name = ''sa'';', N'0', N'USE [master]
GO
 -- If principal_id = 1 or the login owns database objects, rename the sa
login
ALTER LOGIN [sa] WITH NAME = <different_name>;
GO
-- If the login owns no database objects, then drop it
-- Do NOT drop the login if it is principal_id = 1
DROP LOGIN sa', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.17', N'Ensure ''clr strict security'' Server Configuration Option is set to ''1''
(Automated)', N'SELECT
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = ''clr strict security'';', N'1', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''clr strict security'', 1;
RECONFIGURE;
GO
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.15', N'Ensure ''AUTO_CLOSE'' is set to ''OFF'' on contained databases
(Automated)', N'SELECT COUNT(1)
FROM sys.databases
WHERE containment <> 0 and is_auto_close_on = 1;', N'0', N'ALTER DATABASE <database_name> SET AUTO_CLOSE OFF;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.1', N'Ensure ''Server Authentication'' Property is set to ''Windows
Authentication Mode'' (Automated)', N'SELECT CAST(SERVERPROPERTY(''IsIntegratedSecurityOnly'') AS int) AS [login_mode];
', N'1', N'1. Open SQL Server Management Studio.
2. Open the Object Explorer tab and connect to the target database instance.
3. Right click the instance name and select Properties.
4. Select the Security page from the left menu.
5. Set the Server authentication setting to Windows Authentication Mode.', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.2', N'Ensure CONNECT permissions on the ''guest'' user is Revoked within
all SQL Server databases excluding the master, msdb and tempdb
(Automated)', N'CREATE TABLE ##CIS_3_2 (dbname nvarchar(100), username nvarchar(100), permission nvarchar(100), state_desc nvarchar(100));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_3_2 SELECT DB_NAME() AS DatabaseName, ''''guest'''' AS Database_User,
[permission_name], [state_desc]
FROM sys.database_permissions
WHERE [grantee_principal_id] = DATABASE_PRINCIPAL_ID(''''guest'''')
AND [state_desc] LIKE ''''GRANT%''''
AND [permission_name] = ''''CONNECT''''
AND DB_NAME() NOT IN (''''master'''',''''tempdb'''',''''msdb'''');'';
SELECT COUNT(1) FROM ##CIS_3_2;
DROP TABLE ##CIS_3_2;', N'0', N'USE <database_name>;
GO
REVOKE CONNECT FROM guest;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.11', N'Ensure the public role in the msdb database is not granted access
to SQL Agent proxies (Automated)', N'USE [msdb];
SELECT COUNT(1)
FROM dbo.sysproxylogin spl
JOIN sys.database_principals dp
ON dp.sid = spl.sid
JOIN sysproxies sp
ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID(''public'');', N'0', N'1. Ensure the required security principals are explicitly granted access to the proxy
(use sp_grant_login_to_proxy).
2. Revoke access to the <proxyname> from the public role.
USE [msdb]
GO
EXEC dbo.sp_revoke_login_from_proxy @name = N''public'', @proxy_name =
N''<proxyname>'';
GO', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'4.3', N'Ensure ''CHECK_POLICY'' Option is set to ''ON'' for All SQL
Authenticated Logins (Automated)', N'SELECT COUNT(1)
FROM sys.sql_logins
WHERE is_policy_checked = 0
AND is_disabled <> 1;', N'0', N'/* list violated login(s) */
SELECT name
FROM sys.sql_logins
WHERE is_policy_checked = 0
AND is_disabled <> 1;
/* Set CHECK_POLICY = ON */
ALTER LOGIN [<login_name>] WITH CHECK_POLICY = ON;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.3', N'Ensure ''Orphaned Users'' are Dropped From SQL Server Databases
(Automated)', N'CREATE TABLE ##CIS_3_3 (username nvarchar(100), userid nvarchar(100));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_3_3
    select p.name,p.sid
    from sys.database_principals p
    where p.type in (''''G'''',''''S'''',''''U'''')
    and p.sid not in (select sid from sys.server_principals)
    and p.name not in (
        ''''dbo'''',
        ''''guest'''',
        ''''INFORMATION_SCHEMA'''',
        ''''sys'''',
        ''''MS_DataCollectorInternalUser''''
    );''
SELECT COUNT(1) FROM ##CIS_3_3;
DROP TABLE ##CIS_3_3;', N'0', N'USE <database_name>;
GO
DROP USER <username>;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.4', N'Ensure SQL Authentication is not used in contained databases
(Automated)', N'CREATE TABLE ##CIS_3_4 (name nvarchar(100));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_3_4 SELECT name AS DBUser
FROM sys.database_principals
WHERE name NOT IN (''''dbo'''',''''Information_Schema'''',''''sys'''',''''guest'''')
AND type IN (''''U'''',''''S'''',''''G'''')
AND authentication_type = 2;
'';
SELECT COUNT(1) FROM ##CIS_3_4;
DROP TABLE ##CIS_3_4;', N'0', N'SELECT name AS DBUser
FROM sys.database_principals
WHERE name NOT IN (''''dbo'''',''''Information_Schema'''',''''sys'''',''''guest'''')
AND type IN (''''U'''',''''S'''',''''G'''')
--AND authentication_type = 2;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'2.10', N'Ensure Unnecessary SQL Server Protocols are set to ''Disabled''
(Manual)', N'SELECT 0;
/*Open SQL Server Configuration Manager; go to the SQL Server Network Configuration.
Ensure that only required protocols are enabled.*/', N'0', N'Open SQL Server Configuration Manager; go to the SQL Server Network Configuration.
Ensure that only required protocols are enabled. Disable protocols not necessary.', N'equal', 1)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.7', N'Ensure the SQL Server’s Full-Text Service Account is Not an
Administrator (Manual)', N'SELECT 0;
/*
Verify that the SQL Full-Text service account (in case of a local or AD account) and service SID are not
members of the Windows Administrators group.members of the Windows Administrators group.
*/', N'0', N'In the case where LocalSystem is used, use SQL Server Configuration Manager to change
to a less privileged account. Otherwise, remove the account or service SID from the
Administrators group. You may need to run the SQL Server Configuration Manager if underlying permissions had been changed or if SQL Server Configuration Manager was
not originally used to set the service account.', N'equal', 1)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'4.1', N'Ensure ''MUST_CHANGE'' Option is set to ''ON'' for Newly Created SQL
Authenticated Logins (Manual)', N'SELECT 1; /* SELECT LOGINPROPERTY(name, ''IsMustChange'') FROM sys.sql_logins WHERE name = ''<new sql login>''; */', N'1', N'ALTER LOGIN <login_name> WITH PASSWORD = ''<new_password_value>'' MUST_CHANGE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'5.2', N'Ensure ''Default Trace Enabled'' Server Configuration Option is set to
''1'' (Automated)', N'SELECT CAST(value_in_use AS int)
FROM sys.configurations
WHERE name = ''default trace enabled'';', N'1', N'EXECUTE sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXECUTE sp_configure ''default trace enabled'', 1;
RECONFIGURE;
GO
EXECUTE sp_configure ''show advanced options'', 0;
RECONFIGURE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'6.2', N'Ensure ''CLR Assembly Permission Set'' is set to ''SAFE_ACCESS'' for All
CLR Assemblies (Automated)', N'CREATE TABLE ##CIS_6_2 (name nvarchar(255));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_6_2
SELECT name
FROM sys.assemblies
WHERE is_user_defined = 1 AND permission_set_desc <> ''''SAFE_ACCESS'''';
'';
SELECT COUNT(1) FROM ##CIS_6_2;
DROP TABLE ##CIS_6_2;
', N'0', N'USE <database_name>;
GO
ALTER ASSEMBLY <assembly_name> WITH PERMISSION_SET = SAFE;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'7.1', N'Ensure ''Symmetric Key encryption algorithm'' is set to ''AES_128'' or
higher in non-system databases (Automated)', N'CREATE TABLE ##CIS_7_1 (name nvarchar(255));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_7_1
SELECT DB_NAME()
FROM sys.symmetric_keys
WHERE algorithm_desc NOT IN (''''AES_128'''', ''''AES_192'''', ''''AES_256'''')
AND DB_ID() > 4;
'';
SELECT COUNT(1) FROM ##CIS_7_1;
DROP TABLE ##CIS_7_1;', N'0', N'Refer to Microsoft SQL Server Books Online ALTER SYMMETRIC KEY entry:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-symmetric-key-transact-sql', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.5', N'Ensure the SQL Server’s MSSQL Service Account is Not an
Administrator (Manual)', N'SELECT 0; /* Verify that the MSSQL service account (in case of a local or AD account) and service SID are not
members of the Windows Administrators group. */', N'0', N'In the case where LocalSystem is used, use SQL Server Configuration Manager to change
to a less privileged account. Otherwise, remove the account or service SID from the
Administrators group. You may need to run the SQL Server Configuration Manager if underlying permissions had been changed or if SQL Server Configuration Manager was
not originally used to set the service account.', N'equal', 1)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.6', N'Ensure the SQL Server’s SQLAgent Service Account is Not an
Administrator (Manual)', N'SELECT 0;
/*
Verify that the SQL Agent service account (in case of a local or AD account) and service SID are not
members of the Windows Administrators group.
*/', N'0', N'In the case where LocalSystem is used, use SQL Server Configuration Manager to change
to a less privileged account. Otherwise, remove the account or service SID from the
Administrators group. You may need to run the SQL Server Configuration Manager if
underlying permissions had been changed or if SQL Server Configuration Manager was
not originally used to set the service account.', N'equal', 1)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.8', N'Ensure only the default permissions specified by Microsoft are
granted to the public server role (Automated)', N'SELECT COUNT(1)
FROM master.sys.server_permissions
WHERE (grantee_principal_id = SUSER_SID(N''public'') and state_desc LIKE
''GRANT%'')
AND NOT (state_desc = ''GRANT'' and [permission_name] = ''VIEW ANY DATABASE'' and
class_desc = ''SERVER'')
AND NOT (state_desc = ''GRANT'' and [permission_name] = ''CONNECT'' and
class_desc = ''ENDPOINT'' and major_id = 2)
AND NOT (state_desc = ''GRANT'' and [permission_name] = ''CONNECT'' and
class_desc = ''ENDPOINT'' and major_id = 3)
AND NOT (state_desc = ''GRANT'' and [permission_name] = ''CONNECT'' and
class_desc = ''ENDPOINT'' and major_id = 4)
AND NOT (state_desc = ''GRANT'' and [permission_name] = ''CONNECT'' and
class_desc = ''ENDPOINT'' and major_id = 5);', N'0', N'USE [master]
GO
REVOKE <permission_name> FROM public;
GO', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.9', N'Ensure Windows BUILTIN groups are not SQL Logins (Automated)', N'SELECT COUNT(1)
FROM sys.server_principals pr
JOIN sys.server_permissions pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name like ''BUILTIN%'';', N'0', N'USE [master]
GO
DROP LOGIN [BUILTIN\<name>]
GO', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'3.10', N'Ensure Windows local groups are not SQL Logins (Automated)', N'SELECT COUNT(1)
FROM sys.server_principals pr
JOIN sys.server_permissions pe
ON pr.[principal_id] = pe.[grantee_principal_id]
WHERE pr.[type_desc] = ''WINDOWS_GROUP''
AND pr.[name] like CAST(SERVERPROPERTY(''MachineName'') AS nvarchar) + ''%'';', N'0', N'1. For each LocalGroupName login, if needed create an equivalent AD group containing
only the required user accounts.
2. Add the AD group or individual Windows accounts as a SQL Server login and grant it
the permissions required.
3. Drop the LocalGroupName login', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'7.2', N'Ensure Asymmetric Key Size is set to ''greater than or equal to 2048''
in non-system databases (Automated)', N'CREATE TABLE ##CIS_7_2 (name nvarchar(255));
EXEC sp_MSforeachdb ''USE [?];
INSERT ##CIS_7_2
SELECT DB_NAME()
FROM sys.asymmetric_keys
WHERE key_length < 2048
AND db_id() > 4;
'';
SELECT COUNT(1) FROM ##CIS_7_2;
DROP TABLE ##CIS_7_2;
', N'0', N'Refer to Microsoft SQL Server Books Online ALTER ASYMMETRIC KEY entry:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-asymmetric-key-transactsql', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'4.2', N'Ensure ''CHECK_EXPIRATION'' Option is set to ''ON'' for All SQL
Authenticated Logins Within the Sysadmin Role (Automated)', N'SELECT SUM(cnt) FROM (
SELECT COUNT(*) cnt
FROM sys.sql_logins AS l
WHERE IS_SRVROLEMEMBER(''sysadmin'',name) = 1
AND l.is_disabled <> 1
AND l.is_expiration_checked <> 1
UNION ALL
SELECT COUNT(*) cnt
FROM sys.sql_logins AS l
JOIN sys.server_permissions AS p
ON l.principal_id = p.grantee_principal_id
WHERE p.type = ''CL'' AND p.state IN (''G'', ''W'')
AND l.is_disabled <> 1
AND l.is_expiration_checked <> 1
) AS T;', N'0', N'/* 1. List violated logins */
SELECT l.[name], ''sysadmin membership'' AS ''Access_Method''
FROM sys.sql_logins AS l
WHERE IS_SRVROLEMEMBER(''sysadmin'',name) = 1
AND l.is_expiration_checked <> 1
UNION ALL
SELECT l.[name], ''CONTROL SERVER'' AS ''Access_Method''
FROM sys.sql_logins AS l
JOIN sys.server_permissions AS p
ON l.principal_id = p.grantee_principal_id
WHERE p.type = ''CL'' AND p.state IN (''G'', ''W'')
AND l.is_expiration_checked <> 1;
/* 2. Set CHECK_EXPIRATION = ON */
ALTER LOGIN [<login_name>] WITH CHECK_EXPIRATION = ON;', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'5.1', N'Ensure ''Maximum number of error log files'' is set to greater than or
equal to ''12'' (Automated)', N'DECLARE @NumErrorLogs int;
EXEC master.sys.xp_instance_regread
N''HKEY_LOCAL_MACHINE'',
N''Software\Microsoft\MSSQLServer\MSSQLServer'',
N''NumErrorLogs'',
@NumErrorLogs OUTPUT;
SELECT ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles];', N'12', N'EXEC master.sys.xp_instance_regwrite
N''HKEY_LOCAL_MACHINE'',
N''Software\Microsoft\MSSQLServer\MSSQLServer'',
N''NumErrorLogs'',
REG_DWORD,
<NumberAbove12>;', N'>=', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'5.3', N'Ensure ''Login Auditing'' is set to ''failed logins'' (Automated)', N'SELECT ''Failed logins only'';', N'Failed logins only', N'1. Open SQL Server Management Studio.
2. Right click the target instance and select Properties and navigate to the Security
tab.
3. Select the option Failed logins only under the Login Auditing section and click OK.
4. Restart the SQL Server instance.', N'equal', 1)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'5.4', N'Ensure ''SQL Server Audit'' is set to capture both ''failed'' and
''successful logins'' (Automated)', N'SELECT COUNT(1) FROM (
SELECT DISTINCT
S.name AS ''Audit Name''
, CASE S.is_state_enabled
WHEN 1 THEN ''Y''
WHEN 0 THEN ''N'' END AS ''Audit Enabled''
, S.type_desc AS ''Write Location''
, SA.name AS ''Audit Specification Name''
, CASE SA.is_state_enabled
WHEN 1 THEN ''Y''
WHEN 0 THEN ''N'' END AS ''Audit Specification Enabled''
, SAD.audit_action_name
, SAD.audited_result
FROM sys.server_audit_specification_details AS SAD
JOIN sys.server_audit_specifications AS SA
ON SAD.server_specification_id = SA.server_specification_id
JOIN sys.server_audits AS S
ON SA.audit_guid = S.audit_guid
WHERE SAD.audit_action_id IN (''CNAU'', ''LGFL'', ''LGSD'')
) AS T;', N'3', N'1. Expand the SQL Server in Object Explorer.
2. Expand the Security Folder
3. Right-click on the Audits folder and choose New Audit...
4. Specify a name for the Server Audit.
5. Specify the audit destination details and then click OK to save the Server Audit.
6. Right-click on Server Audit Specifications and choose New Server Audit
Specification...
7. Name the Server Audit Specification
8. Select the just created Server Audit in the Audit drop-down selection.
9. Click the drop-down under Audit Action Type and select AUDIT_CHANGE_GROUP.
10. Click the new drop-down Audit Action Type and select FAILED_LOGIN_GROUP.
11. Click the new drop-down under Audit Action Type and select
SUCCESSFUL_LOGIN_GROUP.
12. Click OK to save the Server Audit Specification.
13. Right-click on the new Server Audit Specification and select Enable Server Audit
Specification.
14. Right-click on the new Server Audit and select Enable Server Audit.', N'equal', 0)
GO
INSERT [dbo].[CISBenchmark] ([ruleNumber], [description], [verifyQuery], [expectedValue], [remediation], [comparison], [manual]) VALUES (N'6.1', N'Ensure Database and Application User Input is Sanitized (Manual)', N'SELECT 0; /* Check with the application teams to ensure any database interaction is through the use of
stored procedures and not dynamic SQL. Revoke any INSERT, UPDATE, or DELETE privileges
to users so that modifications to data must be done through stored procedures. Verify that
there''s no SQL query in the application code produced by string concatenation.*/', N'0', N'The following steps can be taken to remediate SQL injection vulnerabilities:
• Review TSQL and application code for SQL Injection
• Only permit minimally privileged accounts to send user input to the server
• Minimize the risk of SQL injection attack by using parameterized commands and
stored procedures
• Reject user input containing binary data, escape sequences, and comment
characters
• Always validate user input and do not use it directly to build SQL statements', N'equal', 1)
GO

Troubleshooting SQL Server Network Error

 When you got below error in SQL Server error log:

Run the following query on SSMS:
;WITH RingBufferConnectivity as
( SELECT
records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],
records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost]
FROM
( SELECT CAST(record as xml) AS record_data
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'
) TabA
CROSS APPLY record_data.nodes('//Record') AS records (record)
)
SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
RBC.Error = M.message_id AND M.language_id = 1033
WHERE RBC.RecordType='Error' and RBC.Error=4014
ORDER BY RBC.RecordTime DESC

Check the column of 'RemoteHost' and find one of the most frequent IP, and you will need to collect Network Monitor between the client and SQL Server.

And please follow the steps to collect network package:

NetMon

Capturing Network Traffic via NetMon UI is documented in Microsoft Docs.

When you open NetMon before you start the capture it's very important to select the specific interface for which you would like to capture the traffic, Missing out the required interface will yield the capture to be unusable - Capturing additional interfaces will make the capture huge and you have to add filters to isolate data.

For Analysis it's very important to make sure the correct parser profile is selected "Windows" as it aligns the traffic as per Microsoft Open protocol specifications.

Following is a sample of capture where applied some important filters to capture/review Kerberos traffic between 2 clients, we can drill this down further if we want and if we know through which port specific conversation might be made in case if there are multiple applications on the same machine talking to AD.

Analyze Availability Group Latency in SSMS

SSMS > Object Explorer > Always On High Availability > Availability Groups > right-click a AG group > Reports > Standard Reports > there are 3 reports.

When you open the 3 reports, they will tell you need to start "Collect Latency Data" and then "Analyze Log Block Latency" before opening them.

Open the availability group dashboard, click "Collect Latency Data".

In agent job activity monitor, you can see a "AlwayOn_Latency_Data_Collection" job is running.

Wait around 2 to 3 minutes to let this job completed, then click "Analyze Log Block Latency" in the availability group dashboard.

Now you can open the Primary Replica Latency report and Log Block Latency report in the primary replica, and open the Secondary Replica Latency report in a secondary replica.

Ref.: New in SSMS - Always On Availability Group Latency Reports

Receive Side Scaling issues with SQL Server

Last month the application team asked me to take a look on an error raised from their PowerShell script, which calls BCP to import large chunks of data from CSV files into SQL Server. Error messages got by powershell:

SQLState = 08S01, NativeError = 10054
Error = [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.

SQLState = 08S01, NativeError = 10054
Error = [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure
SQLState = 08S01, NativeError = 0

In sql server error log, error messages:

Error: 4014, Severity: 20, State: 3.

A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 0, output error: 0).

I did some googling, got some suggestions to try disabling TCP Chimney Offload and Receive Side Scaling. Below are the procedures:

1. Open Administrator: Command Prompt, run netsh int tcp show global to check the current settings.

 

As you see above, Receive-Side Scaling State and Chimney Offload State are enabled.

2. Run below commands to disable TCP Chimney and Receive Side Scaling:

• netsh int tcp set global chimney=disabled
• netsh int tcp set global rss=disabled

3. Run netsh int tcp show global again to check the new settings.

4.  Expand Network adapters, right-click the network adapter, and then click Properties.
On the Advanced tab in the network adapter properties, locate the settings for TCP Chimney Offload and Receive-side scaling, set them to disabled. Different network adapter manufacturers may use different terms to describe the network settings on the Advanced properties page of the network adapter.

Reference: Disable TCP offloading and RSS settings

SQL Server Audit Retention

In on-premises SQL Server, the builtin retention policy of SQL Server Audit can only based on size and number of audit files. But almost all companies require to retain security log by days rather than log size. Below picture illustrates the builtin retention that provided by SQL Server.

So how can you set the retention by days? It can only be done by creating a custom agent job to clear the old audit files. Below sql script is the creation script of that job:

USE [master]
GO
ALTER SERVER AUDIT [Audit_DB] WITH (STATE = OFF);
GO
ALTER SERVER AUDIT [Audit_Server] WITH (STATE = OFF);
GO
ALTER SERVER AUDIT [Audit_DB] TO FILE (MAX_ROLLOVER_FILES = 2147483647);
GO
ALTER SERVER AUDIT [Audit_Server] TO FILE (MAX_ROLLOVER_FILES = 2147483647);
GO
ALTER SERVER AUDIT [Audit_DB] WITH (STATE = ON);
GO
ALTER SERVER AUDIT [Audit_Server] WITH (STATE = ON);
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_delete_job @job_name=N'Delete Old Audit Files', @delete_unused_schedule=1;
GO

/****** Object:  Job [Delete Old Audit Files]    Script Date: 23/11/2023 4:43:39 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 23/11/2023 4:43:39 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Delete Old Audit Files',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Delete Old Audit Files',
        @category_name=N'Database Maintenance',
        @owner_login_name=N'dbadmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [1]    Script Date: 23/11/2023 4:43:39 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXEC sp_configure ''xp_cmdshell'', 1;
RECONFIGURE;
EXEC xp_cmdshell N''ForFiles /P "D:\SqlAudit" /C "cmd /c DEL @PATH" /D -365'';
EXEC sp_configure ''xp_cmdshell'', 0;
RECONFIGURE;
EXEC sp_configure ''show advanced options'', 0;
RECONFIGURE;',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Delete Old Audit Files',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20231123,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959,
        @schedule_uid=N'6b104d29-a60c-43b5-b247-8fcb5c7916af'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The above script first set the number of audit files to unlimited. Then it create the agent job which deletes audit files older than 365 days, by using the forfiles and del windows commands. Beware that this agent job disables xp_cmdshell when it finished, so if your SQL Server uses xp_cmdshell in another part, you may need to change my job creation script.

SQL Server Service Account "Log on as a service" right in GPO

In the past month, one of my clients called me for an emergency problem, saying a production SQL Server down and cannot be started. When I check the Windows event log, I found the error as depicted below:

Saying the MSSQLSERVER service account does not have the required user right "Log on as a service.".
It's quite strange, as the "Log on as a service" right was being assigned automatically by the installer to the sql service account when the sql server was installed, and that sql server had been running well for a while.
Then I immediately solved that problem by adding the service account back to the allowed list in Local Security Policy > Local Policies > User Rights Assignment > Log on as a service:

But on the next day morning, my clients called me again, saying the same problem occurred again. I found the sql service account disappeared again in the Log on as a service allowed list.

Then I checked the Microsoft KB, I found below article:
Error 1069 occurs when you start SQL Server Service
Below sentences catch my eye:
If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.
Ah-ha, that's a clue. I asked the domain administrator when the GPO associated with this server removed the right or not. First time the domain admin told me there's no any GPO which removes any user right. Then I insisted him to give me the GPO associated with that server. The domain admin gave me below screenshot:

Then I told the domain admin to add the sql service account into the above GPO Log on as a service allowed list. The problem resolved and doesn't occur anymore.

Statistics Reporter SSMS Extension

This month blog post I would like to introduce an SSMS extension created by someone that you can get it from the internet, what you need to do is just donate any amount you like. The Statistics Reporter SSMS extension is a handy tool that helps you to read the SET STATISTICS IO, TIME ON result returned from a query/batch execution. In a long ago previous blog post I introduce a free online tool Statistics Parser which does exactly the same thing. But while StatisticsParser.com is a web page that you need to copy your STATISTICS IO and TIME result from your SSMS into the web page, Statistics Reporter SSMS extension is embedded in SSMS so you can see the well formatted result in a new SSMS tab "Statistics Report". Installation of this SSMS extension only involve a few click on "Next" buttons, so I don't bother to show it here. Below screenshot illustrates how cool is this tool: