SQL Server STIG – The Master Query

10 07 2013

I was recently tasked to apply the DISA Security Technical Implementation Guidelines (STIG) to all the Microsoft SQL Servers in our environment. This alone is a huge undertaking and to make matters worse the STIG’s for SQL Server have not been updated by DISA since the release of SQL Server 2005 STIG. Since there are multiple Microsoft SQL Servers in our environment a key to getting this job done is to automate as many as the tasks that are required as part of the STIG requirements. The first thing any database administrator applying the STIG to current versions of SQL Server is that there are alot of required queries to be ran against the database server. Typically the queries are copied out of the PDF format the STIG comes in and pasted into the SQL Server Management Studio (SSMS). Doing this once was more than I ever wanted to do in the first place, so I created what I call the Master Query which contains the vast majority of the required queries to be ran during the STIG process.

The query does not make any modifications to the SQL Server

/*
v 1.0
Master SQL Server STIG Query
Based off of SQL Server 2005 STIG

Created: 9 July 2013
Author: Christopher Provolt

Please change output to Text (Ctrl + T) or Query > Results To > Results To Text
Change output to be Tab delimited as opposed to column delimited.
*/


DECLARE @database VARCHAR(255)
DECLARE @cmd varchar(1000)


PRINT 'List all active databases'
SELECT name from master.sys.databases WHERE state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002424 / DG0060 DBMS shared account authorization'
SELECT name
FROM [master].sys.server_principals
WHERE type IN ('S', 'U')
AND sid <> 0x01
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002426 / DM0510 C2 audit mode'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'c2 audit mode'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002427 / DM0530 Fixed server role members'
EXEC SP_HELPSRVROLEMEMBER 'bulkadmin'
EXEC SP_HELPSRVROLEMEMBER 'dbcreator'
EXEC SP_HELPSRVROLEMEMBER 'diskadmin'
EXEC SP_HELPSRVROLEMEMBER 'processadmin'
EXEC SP_HELPSRVROLEMEMBER 'securityadmin'
EXEC SP_HELPSRVROLEMEMBER 'serveradmin'
EXEC SP_HELPSRVROLEMEMBER 'setupadmin'
EXEC SP_HELPSRVROLEMEMBER 'sysadmin'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002436 / DM0660 MS SQL Server instance name'
SELECT RTRIM(CONVERT(CHAR(20), SERVERPROPERTY('instancename')))
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002451 / DM1709 Guest user'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE name NOT IN ('master', 'temp') AND state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+'];
SELECT COUNT(grantee_principal_id)
FROM sys.database_permissions
WHERE grantee_principal_id = 2
AND state = ''G''
AND permission_name = ''CONNECT'''

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002457 / DM1715 Unauthorized object permission grants'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Type]
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN (''G'', ''W'')
AND (p.type NOT IN (''DL'', ''EX'', ''IN'', ''SL'', ''UP'')
OR u.name IN (''public'', ''guest''))
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002458 / DM1749 System table permissions'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], t.name AS [Table], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.tables t ON p.major_id = t.object_id
WHERE p.state_desc <> ''DENY''
AND t.is_ms_shipped = 1
ORDER BY u.name, t.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002461 / DM1758 xp_cmdshell option Query 1'
SELECT u.name
FROM [master].dbo.sysobjects o, [master].dbo.sysusers u, [master].dbo.sysprotects p
WHERE p.uid = u.uid
AND p.id = o.id
AND o.name = 'xp_cmdshell'
ORDER BY u.name
PRINT '<---------------------------------END------------------------------------>'

PRINT 'V0002461 / DM1758 xp_cmdshell option Query 2'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'xp_cmdshell'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002463 / DM1760 DDL permission assignments'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> ''DENY''
AND p.state IN (''G'', ''W'')
AND (p.type LIKE ''CR%'' OR p.type LIKE ''AL%'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002464 / DM1761 Scan for startup stored procedures option'
PRINT 'Config_Value should be a value of 0'
PRINT 'Config_Value should be a value of 1 if custom trace auditing is enabled'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'scan for startup procs'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002472 / DM2095 OLE automation procedures option Step 1'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'ole automation procedures'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002472 / DM2095 OLE automation procedures option Step 2'
SELECT USER_NAME(p.grantee_principal_id) 'User', o.name 'Object', p.permission_name 'Perm'
FROM [master].sys.system_objects o, [master].sys.database_permissions p
WHERE o.object_id = p.major_id
AND o.name like 'sp_OA%'
ORDER BY USER_NAME(p.grantee_principal_id), o.name, p.permission_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002473 / DM2119 Registry extended stored procedures access'
SELECT u.name AS [Principal], o.name AS [Extended Stored Procedure], u.type_desc AS [Type]
FROM [master].sys.system_objects o
JOIN [master].sys.database_permissions p ON o.object_id = p.major_id
JOIN [master].sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE ( o.name LIKE 'xp_reg%' OR o.name LIKE 'xp_instance_reg%')
AND p.type = 'EX'
ORDER BY o.name, u.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002485 / DM2142 Remote access option'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'remote access'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002487 / DM3566 Authentication mode' 
EXEC XP_LOGINCONFIG 'login mode'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002488 / DM3763 CmdExec or ActiveScripting jobs'
USE msdb
EXEC SP_ENUM_PROXY_FOR_SUBSYSTEM @subsystem_name = 'ActiveScripting'
EXEC SP_ENUM_PROXY_FOR_SUBSYSTEM @subsystem_name = 'CmdExec'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002498 / DM5144 WITH GRANT privilege assignments'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 

SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> ''DENY''
AND p.state IN (''W'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0002508 / DG0070 DBMS user account authorization'
SELECT name
FROM sys.server_principals
WHERE type IN ('S', 'U')
AND principal_id <> 1
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003335 / DM0900 SQL and database mail use Query 1'
PRINT 'Expected value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'sql mail xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003335 / DM0900 SQL and database mail use Query 2'
PRINT 'Expected value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'database mail xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003727 / DG0015 DBMS data definition language use'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT s.name AS [Schema], o.name AS [Object], u.type_desc AS [TYPE], o.create_date AS
[Create Date]
FROM sys.schemas s
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE u.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'')
AND o.is_ms_shipped = 0
ORDER BY s.name, o.name, o.create_date'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003808 / DG0051 Database job/batch queue monitoring Step 1'
SELECT name FROM [master].sys.procedures WHERE is_auto_executed = 1
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003808 / DG0051 Database job/batch queue monitoring Step 2'
SELECT distinct j.name from msdb.dbo.sysjobhistory h, msdb.dbo.sysjobs j WHERE h.job_id = j.job_id
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0003823 / DG0091 DBMS source code encoding or encryption'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT USER_NAME(o.schema_id) AS [Schema], o.name AS [Object], o.type_desc AS [Type],
o.create_date AS [Create Date]
FROM sys.all_objects o, sys.sql_modules s
WHERE o.object_id = s.object_id
AND s.definition IS NOT NULL
AND o.is_ms_shipped = 0
ORDER BY USER_NAME(o.schema_id), o.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0005683 / DG0004 DBMS application object owner accounts'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 

SELECT DISTINCT s.name AS [Schema], l.name AS [User]
FROM sys.all_objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN master.sys.sql_logins l ON u.sid = l.sid
WHERE o.is_ms_shipped = 0
AND l.is_disabled = 0'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0005686 / DG0032 DBMS audit record access'
SELECT u.name AS [User], o.name AS [Object], p.permission_name AS [Permission]
FROM [master].sys.all_objects o, [master].sys.database_principals u,
[master].sys.database_permissions p
WHERE p.grantee_principal_id = u.principal_id
AND o.object_id = p.major_id
AND (o.name = 'dm_exec_sessions' OR o.name = 'sysprotects')
ORDER BY u.name, o.name, p.permission_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015105 / DG0120 DBMS application user access to external objects'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT u.name AS [Principal], o.name AS [External Procedure Name], p.permission_name AS
[Permission], p.state_desc AS [State]
FROM master.sys.all_objects o
JOIN master.sys.database_permissions p ON p.major_id = o.object_id
JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE o.type = ''X''
ORDER BY o.name, u.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 1'
SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r,
[master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 9
AND m.member_principal_id <> 1
ORDER BY r.name, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 2'
SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r,
[master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 3
AND m.member_principal_id <> 1
ORDER BY r.name, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015107 / DG0063 DBMS restore permissions Step 3'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+']; 
SELECT p.name ''User'', r.name ''Role''
FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 16384
ORDER BY r.name, p.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015113 / DM6065 SQL Server replication agent accounts'
PRINT 'No results expected'
SELECT c.credential_identity, p.name
FROM [master].sys.credentials c, [msdb].dbo.sysproxies p, [msdb].dbo.sysproxysubsystem s
WHERE c.credential_id = p.credential_id
AND s.proxy_id = p.proxy_id
AND s.subsystem_id > 3
AND s.subsystem_id < 9
ORDER BY c.credential_identity, p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015125 / DM6045 SQL Server Agent permissions to proxies.'
PRINT 'No results expected'
USE msdb
EXEC sp_enum_proxy_for_subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015128 / DG0105 DBMS application user role privilege assignment'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
	
SET @cmd = 'USE ['+@database+'];
SELECT r.name, o.name, p.permission_name
FROM sys.database_principals r, sys.database_permissions p, sys.all_objects o
WHERE p.grantee_principal_id = r.principal_id
AND p.major_id = o.object_id
AND r.principal_id NOT IN (0, 2)
AND r.type IN (''A'', ''R'')
AND r.is_fixed_role = 0
ORDER BY r.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015130 / DG0074 DBMS inactive accounts'
SELECT SUBSTRING(name, 1, 30) AS [LoginName],
CASE is_disabled WHEN 1 THEN 'true' ELSE 'false' END AS [IsDisabled],
CASE CAST(LoginProperty(name, 'IsExpired') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS
[IsExpired],
CASE CAST(LoginProperty(name, 'IsLocked') AS int) WHEN 1 THEN 'true' ELSE 'false' END AS
[IsLocked]
FROM [master].sys.server_principals
WHERE type = 'S'
AND (is_disabled <> 1
AND CAST(LoginProperty(name, 'IsExpired') AS int) = 1
OR CAST(LoginProperty(name, 'IsLocked') AS int) = 1)
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015151 / DM0531 Fixed database role members'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name AS [Principal], g.name AS [Role]
FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g
WHERE r.role_principal_id = g.principal_id
AND r.member_principal_id = u.principal_id
AND g.is_fixed_role = 1
ORDER BY u.name, g.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015152	/ DG0079 DBMS password complexity'
SELECT name
FROM [master].sys.sql_logins
WHERE type = 'S'
AND is_policy_checked <> '1'
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015153 / DG0125 DBMS account password expiration'
SELECT name
FROM [master].sys.sql_logins
WHERE type = 'S'
AND is_expiration_checked <> '1'
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015154 / DG0190 DBMS remote system credential use and access'
PRINT 'Query should return 0 rows'
SELECT s.name AS [Local Link Server Name], SUSER_NAME(l.local_principal_id) AS [Server
Principal], l.remote_name AS [Remote User Name]
FROM [master].sys.servers s
JOIN [master].sys.linked_logins l ON s.server_id = l.server_id
WHERE l.server_id <> 0
ORDER BY l.server_id
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015159 / DM6175 Database Master key encryption password'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT COUNT(name)
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.name = ''##MS_DatabaseMasterKey##''
AND s.symmetric_key_id = k.key_id
AND k.crypt_type = ''ESKP'''

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015161 / DM6179 database master key encrypted by server'
PRINT 'No results should be returned'
SELECT name
FROM [master].sys.databases
WHERE is_master_key_encrypted_by_server = 1
AND owner_sid <> 1
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015162 / DM6180 Database Master key password storage'
SELECT COUNT(credential_id)
FROM [master].sys.master_key_passwords
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015164 / DM6184 Asymmetric keys specify DoD PKI'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, SUSER_SNAME(sid)
FROM sys.asymmetric_keys
ORDER BY name, SUSER_SNAME(sid)'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015165 / DM6128 service broker access'
PRINT 'This query should return no results'
SELECT name FROM [master].sys.service_broker_endpoints
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015166 / DM6150 ad hoc distributed queries option'
PRINT 'Config_Value should be a value of 0 '
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'ad hoc distributed queries'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015167 / DM6189 Dedicated data file directories Step 1'
SELECT physical_name, type_desc
FROM [master].sys.master_files
ORDER BY physical_name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015167 / DM6189 Dedicated data file directories Step 2'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT physical_name, type_desc
FROM sys.database_files
ORDER BY physical_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015168 / DM6183 Symmetric keys encrypting mechanism'
PRINT 'No results should be returned'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database
SET @cmd = 'USE ['+@database+'];
SELECT s.name, k.crypt_type_desc
FROM sys.symmetric_keys s, sys.key_encryptions k
WHERE s.symmetric_key_id = k.key_id
AND k.crypt_type IN (''KSKP'', ''ESKS'')
AND s.principal_id <> 1
ORDER BY s.name, k.crypt_type_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015172 / DM6196 DBMS object permission grants to PUBLIC or Guest'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission],
p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
JOIN sys.all_objects o ON p.major_id = o.object_id
WHERE p.state_desc <> ''DENY''
AND u.name IN (''guest'', ''public'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015173 / DM6189 Database TRUSTWORTHY status'
PRINT 'This query should return no results'
SELECT name
FROM [master].sys.databases
WHERE is_trustworthy_on = 1
AND name <> 'msdb'
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015178 / DM6070 Replication administration role privileges'
PRINT 'Config_Value should be a value of 0'
SELECT COUNT(name)
FROM [master].sys.databases
WHERE name = 'distribution'
AND state = 0
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015185 / DM6185 Asymmetric keys private key encryption type'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
WHERE pvt_key_encryption_type = ''PW''
ORDER BY name, pvt_key_encryption_type_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015196 / DM6145 Proxy account subsystem privileges'
SELECT p.name, sp.subsystem
FROM [msdb].dbo.sysproxies p, [msdb].dbo.sysproxysubsystem s, [msdb].dbo.syssubsystems sp
WHERE p.proxy_id = s.proxy_id
AND s.subsystem_id = sp.subsystem_id
ORDER BY p.name, sp.subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015197 / DM6140 SQL Server Agent dedicated proxy accounts'
SELECT c.name AS [Credential], c.credential_identity AS [Identity], p.name AS [Proxy],
b.subsystem AS [Subsystem]
FROM [master].sys.credentials c
JOIN [msdb].dbo.sysproxies p ON c.credential_id = p.credential_id
JOIN [msdb].dbo.sysproxysubsystem s ON s.proxy_id = p.proxy_id
JOIN [msdb].dbo.syssubsystems b ON s.subsystem_id = b.subsystem_id
JOIN (SELECT w.credential_identity
FROM [master].sys.credentials w
JOIN [msdb].dbo.sysproxies x ON w.credential_id = x.credential_id
JOIN [msdb].dbo.sysproxysubsystem y ON x.proxy_id = y.proxy_id
WHERE (y.subsystem_id < 4 OR y.subsystem_id > 8)
GROUP BY w.credential_identity HAVING COUNT(*) > 1) d
ON c.credential_identity = d.credential_identity
WHERE (s.subsystem_id < 4 OR s.subsystem_id > 8)
ORDER BY c.name, p.name, b.subsystem
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015198 / DM6130 Web assistance procedures option'
PRINT 'Config_Value should be a value of 0 or no results'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'web assistant procedures'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015201 / DM6130 Cross db ownership chaining option'
PRINT 'Config_Value should be a value of 0 '
SELECT CAST(value AS INT) AS [Config Value]
FROM [master].sys.configurations
WHERE name = 'cross db ownership chaining'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015202 / DM6123 clr_enabled parameter'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'clr enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015206 / DM6126 XML web service access'
PRINT 'This query should return no results'
SELECT name
FROM [master].sys.http_endpoints
WHERE (is_integrated_auth_enabled = 0
AND is_kerberos_auth_enabled = 0
AND is_ntlm_auth_enabled = 0)
AND state = 0
ORDER BY name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015210 / DM6198 Agent XPs option'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'agent xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015211 / DM6199 SMO and DMO XPs option'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'smo and dmo xps'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015607 / DG0008 DBMS application object ownership'
DECLARE DatabaseCursor CURSOR FOR
SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT DISTINCT s.name AS [Schema Name], u.name AS [Principal], u.type_desc AS [Type]
FROM sys.schemas s
JOIN sys.database_principals u ON s.principal_id = u.principal_id
JOIN sys.all_objects o ON s.schema_id = o.schema_id
WHERE u.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'')
ORDER BY s.name, u.name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015610 / DG0025 DBMS encryption compliance'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT name, algorithm_desc FROM sys.symmetric_keys
WHERE key_algorithm NOT IN (''D3'',''A1'',''A2'',''A3'')
ORDER BY name, algorithm_desc'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015614 / DG0084 DBMS residual data clearance'
SELECT CAST(value AS INT)
FROM [master].sys.configurations
WHERE name = 'common criteria compliance enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015618 / DG0099 DBMS access to external local executables'
SELECT o.name AS [External Procedure Name]
FROM master.sys.all_objects o
LEFT JOIN master.sys.database_permissions p ON p.major_id = o.object_id
LEFT JOIN master.sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE o.type = 'X' AND u.name IS NULL
ORDER BY o.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015626 / DG0116 DBMS privileged role assignments'
SELECT p.name
FROM [master].sys.server_principals p, [master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND m.member_principal_id <> 1
AND m.role_principal_id = 3
ORDER BY p.name
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015629 / DG0121 DBMS application user privilege assignment'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT u.name, o.name, p.permission_name
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN (''G'', ''W'')
AND u.type IN (''S'', ''U'')
ORDER BY u.name, o.name, p.permission_name'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015635 / DG0128 DBMS default passwords' 
SELECT name AS [User], type_desc AS [Type], create_date AS [Create Date]
FROM [master].sys.sql_logins
WHERE PWDCOMPARE ('', password_hash) = 1
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015638 / DG0131 DBMS default account names'
SELECT name
FROM [master].sys.sql_logins
WHERE name = 'sa'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015644 / DG0141 DBMS access control bypass'
EXEC XP_LOGINCONFIG 'audit level'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015645 / DG0142 DBMS privileged action audit'
PRINT 'Config_Value should be a value of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'default trace enabled'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015651 / DG0157 DBMS remote administration'
PRINT 'Config_Value should be a valus of 0'
SELECT CAST(value AS INT) 'Config_Value'
FROM [master].sys.configurations
WHERE name = 'remote admin connections'
PRINT '<---------------------------------END------------------------------------>'


PRINT 'V0015654 / DG0165 DBMS symmetric key management'
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE state = 0

OPEN DataBaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
	
select 'Database: ', @database

SET @cmd = 'USE ['+@database+'];
SELECT USER_NAME(grantee_principal_id)
FROM sys.database_permissions
WHERE class = 0
AND state IN (''G'', ''W'')
AND type = ''CL''
ORDER BY USER_NAME(grantee_principal_id)'

EXEC(@cmd)
FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
PRINT '<---------------------------------END------------------------------------>'
Advertisements

Actions

Information

19 responses

9 08 2013
Gary Rietmann

Thanks. Helped out alot. Waiting on DISA to release 2008R2 and 2012 STIGS with in the coming week
Gary

11 09 2013
cprovolt

Thanks for the heads up on the 2012 STIG I didn’t realize they were actually going to update finally… Just confirmed over at DISA’s site they do have the draft posted.

DISA STIGS A-Z

16 08 2013
DeAnn

Chris, I am also a DBA going through 2 different STIG documents (U_INS_sqlserver9_v8r1.7_Checklist_20100827.pdf & U_sqlserver9_v8r1.7_Checklist_20100827.pdf). After going through the first 1 or 2 in the 1st pdf, I decided with a few hundred DB’s, there was no way I was going to do this type of repetition on that many DBs on multiple servers; so I wrote a process that will run each STIG and return results for every DB on a particular server. Here is an example:

— DG0531
— U_DB_sqlserver9_v8r1.7_Checklist_20100827.pdf
— Fixed database role members
— Page 19-20

— code starts here

create table #hd_rc ( [ServerName] NVARCHAR(100), [DBName] VARCHAR(100),
[Principal] VARCHAR(100), [Role] VARCHAR(100) )
DECLARE @command varchar(1000)

SELECT @command = ‘IF EXISTS
(
SELECT name FROM [master].sys.databases WHERE state = 0
)
BEGIN USE ? EXEC(”
INSERT INTO #hd_rc
SELECT @@servername as [ServerName], DB_Name() as [DBName], u.name AS [Principal], g.name AS [Role]
FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g
WHERE r.role_principal_id = g.principal_id
AND r.member_principal_id = u.principal_id
AND g.is_fixed_role = 1
ORDER BY u.name, g.name
”)
END’
EXEC sp_MSforeachdb @command

select * from #hd_rc
drop table #hd_rc

— code stops here

The ONLY issue I have had is on the server where 2007 SharePoint DB’s are located, there are 2 different DB’s that have a dash/hyphen (-) as part of the name (default from installation before I worked here) and I have not yet been able to get around this problem. Still working on it. If you or anyone might have a way for me to get around it, I would be sooooo grateful!
One of the SharePoint DB names is: SharePoint_AdminContent_xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
DB_Name() produces an error that there is not a database with this name: SharePoint_AdminContent_xxxxxxxx. It only grabs the DB name up to the ‘-‘, therefore it doesn’t find that database listed in sys.databases and produces the error.

I have tried concantenating brackets and several other methods, but nothing works so far……any help would be appreciated!

Thanks!

11 09 2013
cprovolt

When ever I reference a database that has some shady characters in the name such as the dash I always make sure to reference it within brackets but it looks like that has faile dfor you as well. I am about to embark upon a sharepoint DB server in the next few days so I’ll see if I can get some results.

I’m not sure if this is what you are looking for as far as this particualr STIG ID goes but perhaps this will help

select rp.name as database_role, mp.name as database_user
from sys.server_role_members drm
join sys.server_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.server_principals mp on (drm.member_principal_id = mp.principal_id)
11 03 2014
cprovolt

Did you attempt to enclose the db name in brackets in the code? I know this works when referencing user accounts with underscores and hyphens.

26 10 2013
TE

Anyone have a suite of tools/scripts/etc for applying STIGs to SQL 2012?

26 10 2013
cprovolt

Once the STIG for SQL 2012 is released officially from DISA I’m certain we will see some tools/scripts come about. Once it is out I plan upgrading to 2012 and performing the STIG on those so most likely I will be writing some scripts for this and will publish them here.

28 10 2013
TE

Yea, I’m having to deal with the draft 2012 STIG right now.. It seems like they cut and pasted from the 2005 STIG and a lot of the scripts don’t work or are defective or incomplete.

I was hoping someone had already made a suite of tools in advance of slow DISA.

10 06 2014
cprovolt

Just posted the SQL Server 2012 Master Query today!

https://cprovolt.wordpress.com

19 04 2014
cprovolt

Currently working on the 2012 Master Query, keep an eye out for it over the next couple weeks!

10 06 2014
cprovolt

Just posted the SQL Server 2012 Master Query today!

https://cprovolt.wordpress.com

25 01 2014
Bang Quack

Not related to your queries but part of the checks in the STIG is the following:
EXEC sp_MSforeachdb ‘
IF (NOT ”?” IN (”master”, ”tempdb”, ”model”, ”msdb”))
BEGIN
USE ?
SELECT ”?” AS ”Database”
, su.name AS ”db Account Name”
, sp.name AS ”SQL Server Account Name”
FROM sys.sysusers su
LEFT JOIN sys.server_principals sp
ON su.sid = sp.sid
WHERE ( su.name like ”publ%”
OR sp.name like ”publ%” )
AND NOT su.sid = CONVERT(VARBINARY(85), 0x)
End’
Note that if you have Sharepoint Databases to deal with you must have the brackets around the ? in order to make it work:
Example USE [?]

11 03 2014
cprovolt

Thanks for the advice SQL Server does not tend to be fond of hyphens in the names of its objects, the brackets [] I’m guessing seem to make the string literal.

19 04 2014
DeAnn

Bang Quack, you have made my DAY, WEEK, MONTH and YEAR!! I don’t know how I overlooked that, but I did. Thank you for drawing my attention to the brackets around the question mark! That fixed the entire issue.

14 12 2016
Dave Myers

cprovolt – Excellent work on the STIG Master. I was looking at some way of incorporating this into Reporting Services. I would like to create a parameterized report with ServerName and a STIG number as the parameters to chose from the drop down when you run the report. FOR EXAMPLE – select one or more server names from a list of servers and then select one or more STIG numbers from a list of stig numbers. (DG0025, DG0008) – Once you have made your selections, then click on view report and a formatted report is produced with the result sets from the queries associated with the STIG numbers selected. I am not entirely sure How do to this effectively but I presume I would need to create a table with at least STIG Number, and Text columns and then use an @sqlcmd to execute the query against the respective Servername Parameter. I have been playing around with this a little but I haven’t been fully successful. I would appreciate any ideas or advice on producing this report (if it is possible the way I described it)

14 12 2016
cprovolt

David, thanks for checking out my blog! Creating a reporting services report is a really good idea and I would also approach this the same way as you have create a table with stig_id, description or short title, sql code and then use execute the statements as needed.

21 12 2016
Dave Myers

cprovolt – I know some of the queries have “commented” sections showing expected results for those queries. Is there a consolidated “expectations list” related to each of the STIG numbers? Where might I find that? I would like to create a column in the table incorporating this information. The table itself will house all the STIG information, listing the expected results,along with the query output. I believe that would be helpful when generating reports.

21 12 2016
cprovolt

When I created the the master query it was a very painful process going through each STIG item one at a time. I either gained that information from the STIG article or from experience.

23 12 2016
Dave Myers

I went through the STIG Viewer we page to determine each expected result (for the most part) – so I think I am good there. I can share that if you would like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: