Enabling Ad Hoc Distributed Queries allows users to query data and execute statements on external data sources.

• This is Disabled by default. Enable only if fully justified.

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'Ad Hoc Distributed Queries';

Enable

sp_configure 'show advanced options', 1; 

RECONFIGURE; 

GO 

sp_configure 'Ad Hoc Distributed Queries', 1; 

RECONFIGURE; 

GO

Disable

EXECUTE sp_configure 'show advanced options', 1; 

RECONFIGURE; 

EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0; 

RECONFIGURE; 

GO 

EXECUTE sp_configure 'show advanced options', 0; 

RECONFIGURE;

Bibliography

Enabling Cross DB Ownership Chaining allows a member of the db_owner role to gain access to objects owned by a login an another database.

This is Disabled by default. Do not enable at instance level, enable only for specific databases as necessary and only when fully justified.

Check

SELECT name, 

      CAST(value as int) as value_configured, 

      CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'cross db ownership chaining';

If disabled, both values will show 0

Disable

EXECUTE sp_configure 'cross db ownership chaining', 0; 

RECONFIGURE; 

GO

Bibliography

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/cross-db-ownership-chaining-server-configuration-option

Enabling Databse Mail XPs allows the ability to generate and transmit email messages from SQL Server

This is Disabled by default. Enable only if fully justified.

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'Database Mail XPs';

Disable

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;

• Enabling SQL Mail XPs provides a mechanism to send, receive, delete and process email messages using SQL Server

• This is Disabled by default. Enable only if fully justified.

NOTE: SQL Mail is deprecated in favor of Database Mail. See also: Database Mail XPs

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'SQL Mail XPs';

Disable

EXECUTE sp_configure 'show advanced options', 1; 

RECONFIGURE; 

EXECUTE sp_configure 'SQL Mail XPs', 0; 

RECONFIGURE; 

GO 

EXECUTE sp_configure 'show advanced options', 0; R

ECONFIGURE;

Bibliography

• Enabling Ole Automation Procedures enables the ability to instantiate OLE Automation objects within T-SQL batches.

• These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server.

• This is Disabled by default. Enable only if fully justified.

Check

SELECT name, 

      CAST(value as int) as value_configured, 

      CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'Ole Automation Procedures';

or

EXEC sp_configure 'Ole Automation Procedures'; 

GO 

Enable

sp_configure 'show advanced options', 1; 

GO 

RECONFIGURE; 

GO 

sp_configure 'Ole Automation Procedures', 1; 

GO 

RECONFIGURE; 

GO 

Disable

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;

Bibliography

• Enabling Remote Access enables the execution of local stored procedures on remote servers or remote stored procedures on local server.

• This is Enabled by default. Enable only if fully justified.

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'remote access';

Disable

EXECUTE sp_configure 'show advanced options', 1; 

RECONFIGURE; 

EXECUTE sp_configure 'remote access', 0; 

RECONFIGURE; 

GO 

EXECUTE sp_configure 'show advanced options', 0; 

RECONFIGURE;

Bibliography

• Enabling 'Scan For Startup Procs' causes SQL Server to scan for and automatically run all stored procedures that are set to execute upon service startup.

• This is Disabled by default. Enable only if fully justified.

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'scan for startup procs';

If both values are 0 then this option is disabled.

Disable

EXECUTE sp_configure 'show advanced options', 1; 

RECONFIGURE; 

EXECUTE sp_configure 'scan for startup procs', 0; 

RECONFIGURE; 

GO 

EXECUTE sp_configure 'show advanced options', 0; 

RECONFIGURE;

Bibliography

• The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. 

• This property could allow database objects to access objects in other databases under certain circumstances.

• It is OFF (0) by default (except for MSDB database where it MUST be ON). Set it to ON only if fully justified.

Check

SELECT name 

FROM sys.databases 

WHERE is_trustworthy_on = 1 

AND name != 'msdb'; 

Disable

ALTER DATABASE [<database_name>] SET TRUSTWORTHY OFF;

Repeat for all databases except MSDB

Bibliography

• All network protocols are installed by SQL Server Setup, but may or may not be enabled.

• Use SQL Server Configuration Manager to enable or disable network protocols. 

• The Database Engine must be stopped and restarted for the change to take effect.

Bibliography

• Designating an instance as hidden prevents advertisement by the SQL Server Browser service.

• Instances are not hidden by default.

• Hiding an instance does not prevent users from connecting to server if they know the instance name and port. 

Check

DECLARE @getValue INT; 

EXEC master..xp_instance_regread 

      @rootkey = N'HKEY_LOCAL_MACHINE', 

      @key = N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', 

      @value_name = N'HideInstance', 

      @value = @getValue OUTPUT; 

SELECT @getValue;

A return value of 1 indicates that the instance i hidden.

Enable

EXEC master..xp_instance_regwrite 

      @rootkey = N'HKEY_LOCAL_MACHINE', 

      @key = N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', 

      @value_name = N'HideInstance', 

      @type = N'REG_DWORD', 

      @value = 1;

You can also change the value using the SSCM Window shown in the Check section above.

Bibliography

• Enabling 'xp_cmdshell' allows the xp_cmdshell extended stored procedure to be used by an authenticated SQL Server user to execute operating-system command shell commands and return results as rows within the SQL client

• This is Disabled by default. Enable only if fully justified.

Check

SELECT name, 

       CAST(value as int) as value_configured, 

       CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'xp_cmdshell';

Disable

EXECUTE sp_configure 'show advanced options', 1; 

RECONFIGURE; 

EXECUTE sp_configure 'xp_cmdshell', 0; 

RECONFIGURE; 

GO 

EXECUTE sp_configure 'show advanced options', 0; 

RECONFIGURE;

Bibliography

There are two types of Server Authentication...

• Windows Authentication Mode

• Mixed Mode - A combination of Windows Authentication Mode and SQL Server Authentication Mode

The default is Windows Authentication Mode. Enable SQL Server Authentication only if fully justified.

The CIS Microsoft SQL Server 2019 Benchmark considers this a Level 1 requirement with this rationale...

"Windows provides a more robust authentication mechanism than SQL Server authentication."

The CIS benchmark also suggests use of a Centralized Point of Authentication. For SQL Server this would generally mean using Windows Authentication Mode in combination with Active Directory users rather than local Windows accounts...

"Configure access for all accounts through as few centralized points of authentication as possible, including network, security, and cloud systems."

Check

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') as [login_mode]; 

Set

USE [master] 

GO 

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1 

GO

Bibliography

• CONNECT Privileges should be REVOKED from GUEST for all databases except for MASTER, MSDB and TEMPDB to ensure that a login is not able to access database information without explicit access to do so.

Check

USE [<database_name>]; 

GO 

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');

REVOKE

USE [<database_name>]; 

GO 

REVOKE CONNECT FROM guest;

Bibliography

• The MSSQL Service Account name should own the MS-SQL Services

• The service account (in case of a local or AD account) and service SID should not be members of the Windows Administrators group. 

Check

• Start "Computer Management" (compmgmt.msc)

• Select "Local Users and Groups"

• Select "Groups"

• Double-click the "Administrators" group

• The MSSQL Service Account name should not appear in the list of members...

By default, the Service Account (or Service SID) is not a member of the Administrators group

To address this...

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.

The public role is a special fixed server role containing all logins.

By default, the public server role is granted VIEW ANY DATABASE permission and the CONNECT permission on the default endpoints (TSQL Local Machine, TSQL Named Pipes, TSQL Default TCP, TSQL Default VIA). The VIEW ANY DATABASE permission allows all logins to see database metadata, unless explicitly denied.

Default Permissions

Any permissions granted to this role will be available to all logins.

Therefore in almost all scenarios any Grants would be better made to individual users or user-defined Roles.

To check if any extra permissions have been granted to the public server role...

SELECT * 

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

The query should not return any rows. Any rows returned are extra permissions that you should consider revoking...

USE [master] 

GO 

REVOKE <permission_name> FROM public; 

GO

SQL Agent Proxies

The PUBLIC role, by default, does not have access to any proxy. Granting access to SQL Agent proxies for the public role would allow all users to utilize the proxy which may have high privileges. This would likely break the principle of least privileges.

To check if access to any proxies has been granted to the msdb databases's public role...

USE [msdb] 

GO 

SELECT sp.name AS proxyname 

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'); 

GO

To revoke access...

Use sp_grant_login_to_proxy to explicitly grant access to the proxy to the required security principals then...

USE [msdb] 

GO 

EXEC dbo.sp_revoke_login_from_proxy @name = N'public', @proxy_name = N''; 

GO

Bibliography

https://support.microsoft.com/en-us/help/2160741/best-practices-in-configuring-sql-server-agent-proxy-account

To expire a session that has been idle for more than 15 minutes... 

Create the following procedure and schedule it to be executed every minute by a SQL Server Agent Job...

CREATE PROC p_SessionTimeOut

AS

SET NOCOUNT ON

DECLARE @Now DATETIME

DECLARE @Cmd nvarchar(40)

DECLARE @SpId int

SET @Now = GetDate()

CREATE TABLE #Who2(

    [SPID] int, 

    [Status] SysName NULL,

    [Login] SysName NULL, 

    [HostName] SysName NULL,

    [BlkBy] SysName NULL,

    [DBName] SysName NULL,

    [Command] SysName NULL,

    [CPUTime] int NULL,

    [DiskIO] int NULL,

    [LastBatch] SysName NULL,

    [ProgramName] SysName NULL,

    [SPID2] int NULL,

    [RequestId] int NULL)     

INSERT #Who2 exec sp_Who2

DELETE FROM #Who2

    WHERE Login = 'sa'

    OR HostName='.'

ALTER TABLE #Who2

        ADD LastDate DateTime

IF Month(@Now)=1 And Day(@Now)=1

BEGIN

    UPDATE #Who2

        SET LastDate=

            CASE WHEN LastBatch Like '12%'

                THEN Cast( Substring(LastBatch,1,5)+ '/'+ 

                            Cast(Year(@now)-1 As varchar(4)) +' '+ 

                            Substring(LastBatch,7,8) as DateTime)

            ELSE

                           Cast( Substring(LastBatch,1,5)+ '/'+ 

                           Cast(Year(@now) As varchar(4))+' ' + 

                          Substring(LastBatch,7,8) as DateTime)

            END    

END    

ELSE

BEGIN

    UPDATE #Who2

        SET LastDate=Cast( Substring(LastBatch,1,5)+ '/'+ 

                Cast(Year(@now) As varchar(4))+' ' + 

                Substring(LastBatch,7,8) as DateTime)

END

DECLARE Hit_List CURSOR FOR 

SELECT SPID FROM #Who2 Where Abs(DateDiff(mi,LastDate,@Now)) > 15

OPEN Hit_List

FETCH NEXT FROM Hit_List into @SpId

WHILE @@FETCH_STATUS=0

BEGIN

    SET @Cmd='KILL '+Cast(@SpId as nvarchar(11))

    EXEC(@Cmd)

    FETCH NEXT FROM Hit_List into @SpId

END

CLOSE Hit_List

DEALLOCATE Hit_List

DROP TABLE #Who2

GO

There are three required password policy settings...

• MUST_CHANGE

• CHECK_EXPIRATION

• CHECK_POLICY

All three settings are the default when creating users through SSMS.

Run the following T-SQL statement to find sysadmin or equivalent logins with CHECK_EXPIRATION = OFF. No rows should be returned...

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;

Use the following code to determine the status of SQL Logins and if their password complexity is enforced...

SELECT name, is_disabled 

FROM sys.sql_logins 

WHERE is_policy_checked = 0;

If no rows are returned then either no SQL Authenticated logins exist or they all have CHECK_POLICY ON.

Setting during manual user creation...

CREATE LOGIN WITH PASSWORD = '' MUST_CHANGE, CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

Setting during manual password reset...

ALTER LOGIN WITH PASSWORD = '' MUST_CHANGE;

This setting controls the number of SQL Error Log Files that will be retained on disk.

Default is 6

The standard incremental daily TSM backup schedule should mean that these files are not lost but for PCI databases it is recommended to increase the setting to 12.or above.

Check

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

This can also be checked (and changed) through SSMS.

Change

Change through SSMS , or...

EXEC master.sys.xp_instance_regwrite 

      N'HKEY_LOCAL_MACHINE', 

      N'Software\Microsoft\MSSQLServer\MSSQLServer', 

      N'NumErrorLogs', 

      REG_DWORD, 

      12;

Once the max number of error logs is reached, the oldest error log file is deleted each time SQL Server restarts or sp_cycle_errorlog is executed. 

Bibliography

• The default trace provides audit logging of database activity including account creations, privilege elevation and execution of DBCC commands.

• Default is 1 (ON)

• Do not disable this setting without full justification.

Check

SELECT name, 

      CAST(value as int) as value_configured, 

      CAST(value_in_use as int) as value_in_use 

FROM sys.configurations 

WHERE name = 'default trace enabled';

Enable

In the event that this option is disabled, you can re-enable it using...

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;

See also

Extended Events

Bibliography