Test ID

Test Target

Test Goal

Automation Script

Expected Setting

1

SQL Server Security Context

Verifies that SQL Server Service Account doesn't use LocalSystem as security context.

EXEC master..xp_regread
'HKEY_LOCAL_MACHINE'
,'SYSTEM\CurrentControlSet\Services\MSS
QLSERVER','ObjectName', @LocalSystem
OUTPUT

NT_ACCOUNT

2

Version#

Verifies that the latest service pack has been installed.

SELECT @ProductVersion = cast( SERVER
PROPERTY('ProductVersion') as
varchar(64))

>=8.00.2039

3

Authentication Mode

Verifies that login mode is set to Windows-only Authentication.

EXECUTE master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQL
Server',N'LoginMode',@LoginMode
OUTPUT

0

4

AuditLevel

Verifies that auditing includes both successful and failed login attempts.

EXECUTE master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQL
Server\MSSQLServer',N'AuditLevel', @AuditLevel
OUTPUT

3

5

MSDTC

Verifies that the MSDTC service is disabled.

EXECUTE master..xp_instance_regread
N'HKEY_LOCAL_MACHINE' ,
N'SYSTEM\CurrentControlSet\Services\MS
DTC', N'Start',@MSDTC OUTPUT

3

6

MSSEARCH

Verifies that the MSSEARCH service is disabled (if installed).

EXECUTE master..xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MS
SEARCH', N'Start', @MSSEARCH
OUTPUT

3

7

Pubs

Verifies that the Pubs sample database is not installed.

IF (select name from master..sysdatabases
WHERE name = 'Pubs') IS NOT NULL

Doesn't exist

8

Remote access

Verifies that remote access is disabled so that other SQL Server systems can't connect

IF (SELECT Value FROM
master..sysconfigures where comment =
'Allow remote access') <> 0

0

9

Update system tables

Verifies that system tables can't be changed.

IF (SELECT top 1 value FROM
master..sysconfigures where comment =
'Allow updates to system tables') <> 0

0

10

Net-Libs

Verifies that TCP is the only network library.

EXECUTE master.xp_regread
N'HKEY_LOCAL_MACHINE',N'SOFTWARE
\Microsoft\MSSQLServer\MSSQLServer\Su
perSocketNetLib', N'ProtocolList'

TCP only

11

Guest login

Verifies that Guests are restricted from the Northwind database.

select name from sysusers where name = 'guest'

No Guest login

12

PUBLIC access:SQLAgentJOB procs

Verifies that PUBLIC can't create, alter, delete or access SQLAgent jobs.

IF (SELECT count(*) from
msdb..sysprotects p join msdb..sysusers u
on p.uid= u.uid WHERE u.name = 'public'
and action = 224 and object_name(p.id) like
'%job%') > 0

No access

13

PUBLIC access:DTS procs

Verifies that PUBLIC can't execute dts procedures.

IF (SELECT count(*) from msdb..sysprotects p join msdb..sysusers u on p.uid= u.uid WHERE u.name = 'public' and action = 224 and object_name(p.id) like '%dts%') > 0

No access

14

PUBLIC access:mswebtasks

Verifies that PUBLIC can't update/delete mswebtasks table.

IF (SELECT count(*) from
msdb..sysprotects p join msdb..sysusers u
on p.uid= u.uid WHERE u.name = 'public'
and action in (195,196,197) and
object_name(p.id) = 'rtbldbmprops') > 0

No access

15

PUBLIC access:regread

Verifies that PUBLIC can't run sensitive procedures.

IF (SELECT count(*) from
master..sysprotects p join master..sysusers
u on p.uid= u.uid WHERE u.name = 'public'
and action = 224 and object_name(p.id) in
('sp_runwebtask','xp_regread',
'xp_instance_regread ')) > 0

No access