# Author : Muhamad Fadzil Ramli
# Title : mssql audit script - 2013
# Installation:
# yum install freetds freetds-dev ruby ruby-dev
# gem install tiny_tds
# Notes:
# This script was created to assist me during mssql 2008 database security audit for a customer.
# Reference:
# CIS Security Benchmark
# http://www.mssqltips.com/sqlservertip/2887/sql-server-security-audit-part-2-scripts-to-help-you-or-where-can-you-find-more-information/
require 'rubygems'
require 'tiny_tds'
user = "sa"
pass = "password"
dbsvr = "192.168.1.129"
begin
client = TinyTds::Client.new(:username => user, :password => pass, :dataserver => dbsvr)
rescue TinyTds::Error => e
puts "[!] #{e}"
exit
end
sql = Array.new()
# A.1 / CIS.1.1 / SERVICE PACKS AND PATCHES
sql[0] = [%Q[A.1\t- Service pack and patches], %Q[
SELECT SERVERPROPERTY('ProductLevel') as SP_installed,
SERVERPROPERTY('ProductVersion') as Version
]]
# B.4 / CIS.4.1 / AUTH MODE
sql[4] = [%Q[B.4\t- Authentication mode - Windows], %Q[
xp_loginconfig 'login mode'
]]
# B.5 / C2 AUDIT MODE
sql[5] = [%Q[B.5\t- C2 audit mode - Enable], %Q[
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'c2 audit mode'
]]
# B.6 / SCAN STARTUP PROCS
sql[6] = [%Q[B.6\t- Scan for startup procedures - Disable], %Q[
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'
]]
# B.7 / NAMED PIPES
#sql[7] = [%Q[B.7\t- Named pipes disabled?], %Q[
#SELECT name, type_desc, state, state_desc,
#endpoint_id FROM sys.endpoints WHERE endpoint_id < 65536 AND protocol = 3
#]]
# B.7 / NAMED PIPES
sql[7] = [%Q[B.7\t- Named pipes - Disable], %Q[
DECLARE @NamedPipesEnabled int
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\\Microsoft\\MSSQLServer\\MSSQLServer\\SuperSocketNetLib\\Np',
N'Enabled',
@NamedPipesEnabled OUTPUT
SELECT @NamedPipesEnabled AS NamedPipesEnabled
]]
# B.8/B.9 - Works only in SQL 2008 SP1 & 2012
sql[8] = [%Q[B.8 - SQL services], %Q[
SELECT * FROM sys.dm_server_services
]]
# B.10
sql[10] = [%Q[B.10\t- Distributed Transaction Coordinator - Disable], %Q[
sp_configure 'remote proc trans'
]]
# B.11 / CIS-2.3 / CROSS DATABASE-OWNERSHI CHAINING
sql[11] = [%Q[B.11 - Cross db ownership chaining - Disbale], %Q[
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'
]]
# B.12 / REPLICATION
sql[12] = [%Q[B.12\t- Replication XPs - Disable], %Q[
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'Replication XPs'
]]
# B.13 / Local Administration Group Membership
sql[13] = [%Q[B.13\t- Local administration group membership - None], %Q[
EXEC master.sys.xp_logininfo 'BUILTIN\Administrators','members'
]]
# C.14 / CIS.2.14 # SA ENABLED?
sql[14] = [%Q[C.14\t- SA account - Disable], %Q[
SELECT name, is_disabled
FROM sys.server_principals
WHERE sid = 0x01
]]
# C.15 / LIST DATABASES
sql[15] = [%Q[C.15\t- Default database exist? PUBS, Northwind], %Q[
SELECT name
FROM master..sysdatabases
]]
#sql[15] = [%Q[C.15\t- List Database], %Q[
#SELECT name FROM master.sys.databases
#WHERE name IN ('pubs', 'Northwind') OR name LIKE 'Adventure Works%'
#]]
# C.16 / LIST STORED PROCEDURES
sql[16] = [%Q[C.16\t- Drop unnecessary stored procedures. e.g xp_cmdshell], %Q[
EXECUTE sp_helpextendedproc
]]
# D.17 / PASSWORD POLICY ENFORCEMENT
sql[17] = [%Q[D.17\t- Password Policy Enforcement - Enable], %Q[
SELECT SQLLoginName = sp.name,
PasswordPolicyEnforced = CAST(sl.is_policy_checked AS BIT)
FROM sys.server_principals sp
JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id
WHERE sp.type_desc = 'SQL_LOGIN'
]]
# E.18 / Enable Security Audit Events
sql[18] = [%Q[E.18\t- Security audit events - Enable], %Q[
select * from sys.database_audit_specification_details
]]
# E.19 / CIS.6.3 / LOGIN ATTEMPTS LOG
sql[19] = [%Q[D.19\t- Logging of login attemps - All], %Q[
XP_loginconfig 'audit level'
]]
# E.20 / MAXIMUM NUMBER OF LOGS
sql[20] = [%Q[D.20\t- Maximum numbers of error logs - 12], %Q[
DECLARE @MaxNumErrorLogs int
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\\Microsoft\\MSSQLServer\\MSSQLServer',
N'NumErrorLogs',
@MaxNumErrorLogs OUTPUT
SELECT @MaxNumErrorLogs AS MaxNumErrorLogs
]]
# F.21 / CIS.2.9 / SQL MAIL XPS
sql[21] = [%Q[F.21\t- SQL Mail XPs - Disable], %Q[
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'
]]
# G.22 / AD HOC REMOTE QUERIES
sql[22] = [%Q[G.22\t- Ad hoc remote queries - Disable], %Q[
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'
]]
# G.23 / CLR INTEGRATION
sql[23] = [%Q[G.23\t- CLR Integration - Disable], %Q[
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'clr enabled'
]]
# G.24 / DATABASE MAIL
sql[24] = [%Q[G.24\t-Database Mail - Disable], %Q[
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'
]]
# G.25 / NATIVE XML WEB SERVICES
sql[25] = [%Q[G.25\t- Native XML Web Services - None], %Q[
SELECT name, type_desc, state, state_desc,
endpoint_id FROM sys.endpoints WHERE endpoint_id < 65536 AND type = 1
]]
# G.26 / SERVICE BROKER
sql[26] = [%Q[G.26\t- Service Broker - None], %Q[
SELECT name, type_desc, state, state_desc,
endpoint_id FROM sys.endpoints WHERE endpoint_id < 65536 AND type = 3
]]
# G.27
sql[27] = [%Q[G.27\t- Web Assistant - None], %Q[
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'Web Assistant Procedures'
]]
# G.29 / ANONYMOUS CONNECTIONS
sql[29] = [%Q[G.29\t- Anonymous connections - Disable], %Q[
DECLARE @AnonConn INT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SYSTEM\\CurrentControlSet\\Control\\Lsa',
N'TurnOffAnonymousBlock',
@AnonConn OUTPUT
SELECT @AnonConn AS AnonConnections
]]
# G.30 / LINKED OBJECTS / http://support.microsoft.com/kb/203638
sql[30] = [%Q[G.30\t- Linked objects - Disable], %Q[
sp_linkedservers
]]
# G.31 / USER DEFINED FUNC
sql[31] = [%Q[G.31\t- User defined functions - None], %Q[
SELECT *
FROM sys.objects
WHERE type_desc = 'SQL_SCALAR_FUNCTION'
]]
# G.32 / WINDOWS INTEGRATED SECURITY
sql[32] = [%Q[G.32 - Windows integrated security - Enable], %Q[
SELECT CAST(SERVERPROPERTY ('IsIntegratedSecurityOnly') as int) as WinIntegratedSecurity
]]
# G.33 / VIEW PUBLIC PERMISSION
sql[33] = [%Q[G.33\t- List 'PUBLIC' permission], %Q[
sp_helprotect @username='public'
]]
count = 0
sql.each do |col|
if col.nil?
next
end
if client.active?
res = client.execute(col[1])
else
puts "[!] sql client is not active"
end
count += 1
puts "[#{count}] #{col[0]}"
res.each do |row|
puts row.to_s
end
puts ""
end
