Automations

SQL Server STIG Logic

Here’s some logic which may help you tackle some of those Database STIG requirements for SQL Server. I created this for a particular SQL Database Server which hadn’t yet been hardened according to all the STIG requirements.

This is heavily notated, and all client information of course has been removed. Although most of it will work fine in your environment I wouldn’t recommend it as it only made this server compliant in accordance with the Application system it supported. You may find the notations particularly helpful.

Keep in mind that while I posted the logic here ( for ease of use ) I will not be uploading a .sql file to download. My only hope is the blog formatting doesn’t butcher the logic too badly as that does tend to happen from time to time.

------------------------
------------------------
/*
		NOTE:	THE STIG CHECKS AND CONFIGURATION CHANGES CARRIED OUT IN THIS SCRIPT TO
				MEET COMPLIANCE WERE CREATED IN ACCORDANCE WITH THIS APPLICATION DATABASE
				ENVIRONMENT. SOME FUNCTIONALITY AND SERVICES WHICH ARE NOT DEPLOYED HERE
				WILL SIMPLY BE EXEMPT FROM STIG
				COMPLIANCE IN THOSE AREAS THEREFORE THIS SCRIPT WILL NOT CHECK FOR,	NOR MAKE 
				CHANGES TO SQL SERVER CONFIGURATIONS WHICH ARE NOT USED HERE.		
*/
------------------------
------------------------
PRINT ''
PRINT '------------------------'
PRINT '------------------------'
PRINT ''
PRINT ' THE STIG CONFIGURATIONS HAVE NOW STARTED FOR SQL SERVER AND'
PRINT ' THE SQL SERVER DATABASES.'
PRINT ''
PRINT '------------------------'
PRINT '------------------------'
PRINT ''
PRINT ''
PRINT ''
------------------------
------------------------

use master
go

set nocount on
------------------------
------------------------
------------------------ SETTING VARIABLES
declare @mydatabase01			varchar(100)
declare @mydatabase02			varchar(100)
declare @mydatabase03			varchar(100)
declare @mydatabase04			varchar(100)
declare	@mydatabase05			varchar(100)
declare	@mydatabase06			varchar(100)
declare	@mydatabase07			varchar(100)
declare	@mydatabase08			varchar(100)
declare	@mydatabase09			varchar(100)
declare @mydatabase10			varchar(100)

declare @sqlaccount01			varchar(100)
declare @sqlaccount02			varchar(100)
declare @sqlaccount03			varchar(100)
declare	@sqlaccount04			varchar(100)
declare @sqlaccount05			varchar(100)
declare @sqlaccount06			varchar(100)
declare @sqlaccount07			varchar(100)
declare @sqlaccount08			varchar(100)
declare @sqlaccount09			varchar(100)
declare @sqlaccount10			varchar(100)

declare @sqlaccount01_password	varchar(100)
declare	@sqlaccount02_password	varchar(100)
declare @sqlaccount03_password	varchar(100)
declare	@sqlaccount04_password	varchar(100)
declare @sqlaccount05_password	varchar(100)
declare @sqlaccount06_password	varchar(100)
declare @sqlaccount07_password	varchar(100)
declare @sqlaccount08_password	varchar(100)
declare @sqlaccount09_password	varchar(100)
declare @sqlaccount10_password	varchar(100)

declare @domainaccount01		varchar(100)
declare @domainaccount02		varchar(100)
declare @domainaccount03		varchar(100)
declare @domainaccount04		varchar(100)
declare @domainaccount05		varchar(100)
declare @domainaccount06		varchar(100)
declare @domainaccount07		varchar(100)
declare @domainaccount08		varchar(100)
declare	@domainaccount09		varchar(100)
declare @domainaccount10		varchar(100)

set		@mydatabase01			= 'mydatabase_01'
set		@mydatabase02			= 'mydatabase_02'
set		@mydatabase03			= 'mydatabase_03'
set		@mydatabase04			= 'mydatabase_04'
set		@mydatabase05			= 'mydatabase_05'
set		@mydatabase06			= 'mydatabase_06'
set		@mydatabase07			= 'mydatabase_07'
set		@mydatabase08			= 'mydatabase_08'
set		@mydatabase09			= 'mydatabase_09'
set		@mydatabase10			= 'mydatabase_10'

set		@sqlaccount01			= 'sqlvariableaccount01'
set		@sqlaccount02			= 'sqlvariableaccount02'
set		@sqlaccount03			= 'sqlvariableaccount03'
set		@sqlaccount04			= 'sqlvariableaccount04'
set		@sqlaccount05			= 'sqlvariableaccount05'
set		@sqlaccount06			= 'sqlvariableaccount06'
set		@sqlaccount07			= 'sqlvariableaccount07'
set		@sqlaccount08			= 'sqlvariableaccount08'
set		@sqlaccount09			= 'sqlvariableaccount09'
set		@sqlaccount10			= 'sqlvariableaccount10'

set		@sqlaccount01_password	= 'password'
set		@sqlaccount02_password	= 'password'
set		@sqlaccount03_password	= 'my_new_complex_password'
set		@sqlaccount04_password	= 'my_new_complex_password'
set		@sqlaccount05_password	= 'my_new_complex_password'
set		@sqlaccount06_password	= 'my_new_complex_password'
set		@sqlaccount07_password	= 'my_new_complex_password'
set		@sqlaccount08_password	= 'my_new_complex_password'
set		@sqlaccount09_password	= 'my_new_complex_password'
set		@sqlaccount10_password	= 'my_new_complex_password'

set		@domainaccount01		= '\DOMAINACCOUNT01'
set		@domainaccount02		= '\DOMAINACCOUNT02'
set		@domainaccount03		= '\DOMAINACCOUNT03'
set		@domainaccount04		= '\DOMAINACCOUNT04'
set		@domainaccount05		= '\DOMAINACCOUNT05'
set		@domainaccount06		= '\DOMAINACCOUNT06'
set		@domainaccount07		= '\DOMAINACCOUNT07'
set		@domainaccount08		= '\DOMAINACCOUNT08'
set		@domainaccount09		= '\DOMAINACCOUNT09'
set		@domainaccount10		= '\DOMAINACCOUNT10'

------------------------------------------------------------
------------------------------------------------------------
/*  GENERIC VARIABLE SETS FOR TESTING */
------------------------
------------------------
------------------------CREATE FAILSAFE SA

use [master]
go
create login [my_new_sa] 
with 
  password= 'my_new_complex_password'
, default_database=[master]
, check_expiration=off
, check_policy=off
go
exec master..sp_addsrvrolemember 
  @loginame = 'my_new_sa'
, @rolename = 'sysadmin'
go

------------------------
------------------------
------------------------DROP SQL LOGINS
/*

-- drop from database
use [mydatabase_01]
go
drop user [sqlaccount01]
go

-- drop from sql engine
use [master]
go
drop login [sqlaccount01]
go

*/
------------------------
------------------------
------------------------DROP DOMAIN LOGINS
/*

-- drop from database
use [mydatabase_01]
go
drop user [\admin]
go

-- drop from SQL engine
use [master]
go
drop login [\admin]
go

*/
------------------------
------------------------
------------------------CREATE SQL LOGINS

--if exists ( select name from master.sys.sql_logins where name = 'sqlaccount01' )
--	begin
--		print 'The SQL Login sqlaccount01 already exists.'
--	end
--else
--	begin
--		create login sqlaccount01 with 
--		  password= 'password'
--		, default_database=[master]
--		, check_expiration=off
--		, check_policy=off
--		print 'The SQL Login sqlaccount01 has been created'
--	end
--go
		
/*
create login sqlaccount01 with 
	  password= 'password'
	, default_database=[master]
	, check_expiration=off
	, check_policy=off
go
*/

--use [mydatabase_01]
--go
--create user [sqlaccount01] for login [sqlaccount01]
--go
--exec sp_addrolemember 'db_owner', 'sqlaccount01'
--go

------------------------
------------------------
------------------------CREATE DOMAIN LOGINS

--use [master]
--go
--create login [\admin] from windows with default_database=[master]
--go

--use [mydatabase_01]
--go
--create user [\admin] for login [\admin]
--go
--exec sp_addrolemember 'db_datareader', '\admin'
--go
--exec sp_addrolemember 'db_datawriter', '\admin'
--go
--exec sp_addrolemember 'db_owner', '\admin'
--go

------------------------
------------------------
------------------------CONFIGURE LOGINS 


/*
	Check to see which SQL Logins have their 'Check Policy', and 'Check Expiration'
	configurations enabled.
*/

/*
select  
	  name
	, is_policy_checked
	, is_expiration_checked
	, is_disabled 
from master.sys.sql_logins
where name not like '%#%'
*/


/*  
	The following script will enable the 'Check Policy', and 'Check Expiration' configurations for all SQL Logins 
*/

set nocount on
declare		@enable_check_policy	varchar (2000)
select		@enable_check_policy	= coalesce	
(@enable_check_policy + 'use master; alter login ' + name + ' with check_policy = on, check_expiration = on;' + CHAR(10), '')
from mydatabase_01.sys.sql_logins where name not like '%#%'

select (@enable_check_policy)
--exec   (@enable_check_policy)
go

------------------------
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0005683
Short Name:			DBMS application object owner accounts
Long Name:			Application object owner accounts should be disabled when not 
					performing installation or maintenance actions.
					
					Object ownership provides all database object permissions to 
					the owned object. Access to the application object owner accounts 
					requires special protection to prevent unauthorized access and 
					use of the object ownership privileges. In addition to the high 
					privileges to application objects assigned to this account, it is 
					also an account that, by definition, is not accessed interactively 
					except for application installation and maintenance. This reduced 
					access to the account means that unauthorized access to the account 
					could go undetected. To help protect the account, it should be 
					enabled only when access is required.
*/

/*  The following script checks the password and expiration policies per account per database. */

sp_msforeachdb 
'use [?]

select 
	  name as ''SQL Account Name''
	, case is_disabled
		when ''0'' then ''Enabled''
		when ''1'' then ''Disabled''
		end
		as ''Is Disabled''
	, case is_policy_checked 
		when ''0'' then ''Password Policy is OFF''
		when ''1'' then ''Password Policy is ON''
		end
		as ''Password Policy''
	, case is_expiration_checked 
		when ''0'' then ''Expiration Policy is OFF''
		when ''1'' then ''Expiration Policy is ON''
		end
		as ''Expiration Policy''
from sys.sql_logins where name not like ''%#%''
'

------------------------
-------------SYNTAX REFERENCE
/*

To create a new sql account:
create login mysqlaccount with password ='mypassword'

To alter an existing sql account:
alter login MyLogin with password = 'newpassword'
alter login MyLogin with check_policy = on
alter login MyLogin with check_expiration = off
go

*/
------------------------
------------------------

PRINT '***    STIG VULNERABILITY KEY:  V0005683	HAS BEEN SATISIFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY: V0003727
Short Name:         DBMS data definition language use
Long Name:          Database applications should be restricted from using static DDL 
					statements to modify the application schema.
					
					Application users by definition and job function require only the 
					permissions to manipulate data within database objects and execute 
					procedures within the database. The statements used to define objects 
					in the database are referred to as Data Definition Language (DDL) 
					statements and include the CREATE, DROP, and ALTER object statements 
					(DDL statements do not include CREATE USER, DROP USER, or ALTER USER). 
					This requirement is included here as a production system would by 
					definition not support changes to the data definitions. Where object 
					creation is an indirect result of DBMS operation or dynamic object 
					structures are required by the application function as is found in 
					some object-oriented DBMS applications, this restriction does not 
					apply. Re-use of static data structures to recreate temporary data 
					objects are not exempted.  Database applications are not restricted 
					from using static DDL statements to modify the application schema.
					
					Review the objects and creation dates as generated. Confirm the objects 
					are documented, authorized to remain and accept as Documentable in VMS.
					
					View the list of objects listed by the queries above. If any object 
					creation dates do not coincide with the software maintenance and 
					upgrade logs or are not objects documented as supporting dynamic object 
					creation functions, investigate the circumstances under which the object 
					was created. If the object is created using static definitions to store 
					temporary data or indicates that the application uses unauthorized DDL 
					statements, this is a Finding.  The results of these queries will just 
					give an indication of what objects were created since the database 
					installation or its most recent upgrade. It should not be used as a 
					complete result. For example, using the queries listed above, 
					application objects created with the database installation will not 
					be reported as well as objects owned by default application owner accounts.
					
					If the creation dates are more recent than the installation or latest 
					upgrade of the application, the application may be using DDL statements.
*/

/* Run the following under each 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

/*  This is not a finding based on operational application parameters. */

PRINT '***    STIG VULNERABILITY KEY:  V0003727	HAS BEEN SATISIFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015128
Short Name:         DBMS application user role privilege assignment
Long Name:          DBMS application user roles should not be assigned unauthorized 
					privileges.
					
					Unauthorized access to the data can lead to loss of confidentiality 
					and integrity of the data.  Review the accounts, objects and 
					permissions as generated. Confirm the permissions for the objects 
					are documented, authorized and accept as Documentable in VMS.
					Compare privileges assigned to database application user roles to 
					those defined in the System Security Plan. Use the grant and revoke 
					commands to assign the authorized privileges as listed in the System
					Security Plan to custom database application or application user roles.
					Note: Default privileges assigned to fixed data roles are considered 
					authorized by default.
*/

/*  The following script will list all roles, and associated permissions against each database. */

exec sp_msforeachdb
'
use [?]

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
'

------------------------
-------------SYNTAX REFERENCE
/*

Creating Server Roles, and granting permissions on those roles.

use master;
create server role itdevadmin ;
create server role itdevelopers ;
grant alter any database to itdevadmin with grant option ;
grant alter any database to itdevelopers as itdevadmin ;
go

Granting Select permissions to a user.

use MyDatabase;
grant select on MyTable to MyUser;
go

Graingint Execute permissions to a Role.

use MyDatabase; 
grant execute on MyProcedure to MyRole;
go 

*/
------------------------
------------------------

/*  This is not a finding based on operational application parameters. */

PRINT '***    STIG VULNERABILITY KEY:  V0015128	HAS BEEN SATISIFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015629
Short Name:         DBMS application user privilege assignment
Long Name:          Application users privileges should be restricted to assignment 
					using application user roles.  
					
					Privileges granted outside the role of the application user job 
					function are more likely to go unmanaged or without oversight for 
					authorization. Maintenance of privileges using roles defined for 
					discrete job functions offers improved oversight of application 
					user privilege assignments and helps to protect against unauthorized 
					privilege assignment.  Application users privileges have not been 
					restricted to assignment using application user roles.
					
					Review the accounts, objects and permissions as generated. Confirm 
					the permissions for the objects are documented, authorized and 
					accept as Documentable in VMS.  If any names are listed, this is a 
					Finding.
					
					Revoke permissions assigned directly to user accounts and grant them
					instead to the appropriate group account.  Document any exceptions 
					to privileges that cannot be assigned via database roles in the 
					System Security Plan.

*/

/*  
The system master principle ( ##MS_PolicyEventProcessingLogin## ) is not a Finding.  
*/

--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
	
/*  
There are no 'application user roles' created, or managed for this application 
system.  Permissions\Rights are carried through specific application accounts which
are utilized ONLY by the application system, and related services.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015629	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015151
Short Name:         Fixed database role members
Long Name:          Fixed Database roles should have only authorized users or groups 
					as members.
					
					Fixed database roles provide a mechanism to grant groups of 
					privileges to users. These privilege groupings are defined by the 
					installation or upgrade of the SQL Server software at the discretion 
					of Microsoft. Memberships in these roles granted to users should be 
					strictly controlled and monitored. Privileges assigned to these roles 
					should be reviewed for change after software upgrade or maintenance 
					to ensure that the privileges continue to be appropriate to the 
					assigned members.  Fixed Database roles have unauthorized users or 
					groups as members.
					
					Review the Database, Principal and Role as generated. An example 
					entry is in the form: master guest db_accessadmin This is interpreted 
					as: The GUEST account in the MASTER database is granted membership to 
					the fixed database role DB_ACCESSADMIN. Confirm the account(s) and 
					role assignments are documented, authorized and accept as Documentable 
					in VMS.
					
					The DBO membership in the db_owner fixed database role does not 
					require explicit authorization and is Not a Finding.  Verify fixed 
					database role assignments for each Principal listed in the System 
					Security Plan. If any Principals are not authorized, this is a Finding.
					Any fixed database role assignments to the Guest Principal is a finding.
					
					Revoke unauthorized accounts from fixed database roles. From the SQL 
					Server Management Studio GUI:
					To deassign roles:
						1. Expand [instance name]
						2. Expand Databases
						3. Expand [database type]
						4. Expand [database name]
						5. Expand Security
						6. Expand Roles
						7. Expand Database Roles
						8. Double-click the role to be removed from the assigned user
						9. Select the user's account under Role Members
						10. Click on the Remove button
					Document fixed database role grants in the System Security Plan and 
					authorize with the IAO.
*/

/*  
Logins which have been granted access via fixed database roles have been designated as
'Required' by vendor documentation.  This has been documented.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015151	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015172
Short Name:         Guest user
Long Name:          The guest user account should be disabled.

					The guest user ID in a database allows access by all Windows login 
					IDs without requiring an individual database account. This allows 
					unauthorized access to the database.
					
					Revoke connect permission from all databases except master and tempdb.
					If any value other than a 0 is returned, this is a Finding.
					
					Revoke connect permission from all databases except master and tempdb. 
					Example:  
							use mydb
							revoke connect from guest
							go
					
*/

------------------------
/************** CHECK

select count(grantee_principal_id)
from sys.database_permissions
where grantee_principal_id = 2
	and state = 'g'
	and permission_name = 'connect'
------------------------
------------------------


------------------------
------------------------SOLUTION
/*
	Note:	This script will automatically revoke rights to the Guest account under each 
			individual database.
*/

declare @revokeconnectionfromguest nvarchar(2000) 

select	@revokeconnectionfromguest = 
coalesce(@revokeconnectionfromguest + 'use [' + name + '];' + char(10) + 'revoke connect from guest;', '') 
from master..sysdatabases where dbid >4
exec 	(@revokeconnectionfromguest) 

------------------------
------------------------

PRINT '***    STIG VULNERABILITY KEY:  V0002451	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015172
Short Name:         DBMS object permission grants to PUBLIC or Guest
Long Name:          Object permissions should not be assigned to PUBLIC or GUEST.
					The guest account is available to users that do not have authorized 
					accounts on the database. The PUBLIC role is granted to all users of 
					the database regardless of assigned job function. Assignment of object 
					privileges to unauthorized users can compromise data integrity and/or 
					confidentiality.  Object permissions are assigned to PUBLIC or GUEST.
					
					Review the Database, Principal, Object, Permission and State as 
					generated. 
					
					If any results listed are unauthorized, this is a Finding.  If any 
					results listed are not documented in the System Security Plan and 
					authorized by the IAO, this is a Finding.  Note: Some permissions 
					assigned to PUBLIC within the master database may require the 
					'Allow modifications to be made directly to the system catalogs' 
					database setting be temporarily be enabled.

*/

------------------------
/************** CHECK
--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

------------------------
------------------------

PRINT '***    STIG VULNERABILITY KEY:  V0015172	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0005658
Short Name:         DBMS version support
Long Name:          Vendor supported software is evaluated and patched against newly 
					found vulnerabilities.
					
					The version of MS SQL Server must be listed by Microsoft as a 
					supported version. Microsoft discontinues fixes for unsupported 
					versions on reported dates. In order to maintain a secure 
					environment, the installed version must continue to receive fixes 
					for reported vulnerabilities.  
					
					Where format is in major.minor.build and we only concern ourselves 
					with the major version.


*/

/*  Note:	At the time the SQL STIG guide was written the current SQL Server version
			was for SQL Server 2005.  Our installation, and configurations are based
			on SQL Server 2008.  The STIG we are to follow is the latest approved
			version according to the Vendor.  Versions are NOT TO BE UPGRADED based on
			Microsoft recommendations regardless of how critical the Patch/Hotfix is.
			ALL PATCH/HOTFIX/UPDATES ARE TO BE CLEARED THROUGH THE VENDOR FOR COMPLIANCE.
*/

/*  Run the following statement to determine Edition and build number.  */

use master
go

select 
	  serverproperty ('edition')		as 'edition'
	, serverproperty ('productlevel')	as 'service pack'
	, serverproperty ('productversion') as 'build'


/*  
	Note:  The following list is provided as a reference.
	
	------------------------
	------------------------
	------------------------
	SQL SERVER 2012:
	11.0.2316	 SQL Server 2012 CU1  	12 Apr 2012
	11.0.2100.6	 SQL Server 2012 RTM  	7 Mar 2012
	------------------------
	------------------------
	SQL SERVER 2008 R2:
	10.50.2811	 SQL Server 2008 R2 SP1 CU6 16 Apr 2012
	10.50.2806	 SQL Server 2008 R2 SP1 CU5 22 Feb 2012
	10.50.2796 	 SQL Server 2008 R2 SP1 CU4 9 Jan 2012
	10.50.2789	 SQL Server 2008 R2 SP1 CU3 17 Oct 2011
	10.50.2772	 SQL Server 2008 R2 SP1 CU2 16 Aug 2011
	10.50.2769	 SQL Server 2008 R2 SP1 CU1 16 Sep 2011
	10.50.2500	 SQL Server 2008 R2 SP1  	11 Jul 2011
	10.50.1815	 SQL Server 2008 R2 CU13  	17 Apr 2012
	10.50.1810	 SQL Server 2008 R2 CU12  	21 Feb 2012
	10.50.1809	 SQL Server 2008 R2 CU11  	9  Jan 2012
	10.50.1807	 SQL Server 2008 R2 CU10  	19 Oct 2011
	10.50.1804	 SQL Server 2008 R2 CU9  	23 Aug 2011
	10.50.1797	 SQL Server 2008 R2 CU8  	16 Sep 2011
	10.50.1777	 SQL Server 2008 R2 CU7  	16 Jun 2011
	10.50.1765	 SQL Server 2008 R2 CU6  	21 Feb 2011
	10.50.1753	 SQL Server 2008 R2 CU5  	20 Dec 2010
	10.50.1746	 SQL Server 2008 R2 CU4  	18 Oct 2010
	10.50.1734	 SQL Server 2008 R2 CU3  	17 Aug 2010
	10.50.1720	 SQL Server 2008 R2 CU2  	25 Jun 2010
	10.50.1702	 SQL Server 2008 R2 CU1  	18 May 2010
	10.50.1600 	 SQL Server 2008 R2 RTM		12 Apr 2010
	------------------------
	------------------------
*/

PRINT '***    STIG VULNERABILITY KEY:  V0005658	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0005659
Short Name:         DBMS security patch level
Long Name:          The latest security patches should be installed.
					If any update has been released that is deemed by Microsoft to 
					be a critical update, this check should be assigned a 
					Severity Category I.
					
					Maintaining the currency of the software version protects the 
					database from known vulnerabilities.
*/				
					
/*  
	The previous STIG ( STIG VULNERABILITY KEY:  V0005658 ) also addressed this 
	issue.  The following note still applies.

	Note:	At the time the SQL STIG guide was written the current SQL Server version
			was for SQL Server 2005.  Our installation, and configurations are based
			on SQL Server 2008.  The STIG we are to follow is the latest approved
			version according to the Vendor.  Versions are NOT TO BE UPGRADED based on
			Microsoft recommendations regardless of how critical the Patch/Hotfix is.
			ALL PATCH/HOTFIX/UPDATES ARE TO BE CLEARED THROUGH THE VENDOR FOR COMPLIANCE.
*/

/*  Run the following statement to determine Edition and build number.  */

use master
go

select 
	  serverproperty ('edition')		as 'edition'
	, serverproperty ('productlevel')	as 'service pack'
	, serverproperty ('productversion') as 'build'

PRINT '***    STIG VULNERABILITY KEY:  V0005659	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0006756
Short Name:         DBMS administration OS accounts
Long Name:          Only necessary privileges to the host system should be granted to 
					DBA OS accounts.
					
					Database administration accounts are frequently granted more 
					permissions to the local host system than are necessary. This allows 
					inadvertent or malicious changes to the host operating system.
					Unnecessary privileges to the host system have been granted to 
					DBA OS accounts.
					
					Review host system privileges assigned to the DBA accounts. If any 
					are granted host system administrator privileges or other system 
					privileges not required for DBMS administration, this is a Finding.
					The DBA should have only the OS Users group, custom SQLServer DBA 
					group, SQL Server service groups and custom SQL Server Users 
					groups assigned.
					
					Revoke any host system privileges from DBA accounts not required 
					DBMS administration. Revoke any OS group memberships that assign 
					excess privileges to DBA accounts.  Remove any directly applied 
					permissions or user rights from the DBA account.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0006756	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0004754
Short Name:         DBMS software storage location
Long Name:          Database software directories including DBMS configuration files 
					are stored in dedicated directories.
					
					Multiple applications can provide a cumulative negative effect. A 
					vulnerability and subsequent exploit to one application can lead to 
					an exploit of other applications sharing the same security context. 
					For example, an exploit to a web server process that leads to 
					unauthorized administrative access to host system directories can 
					most likely lead to a compromise of all applications hosted by the 
					same system. Database software not installed using dedicated 
					directoriies both threatens and is threatened by other hosted 
					applications. Access controls defined for one application may by 
					default provide access to the other application’s database objects or 
					directories. Any method that provides any level of separation of 
					security context assists in the protection between applications.
					This is a finding if the Database software directories including 
					DBMS configuration files are not stored in dedicated directories 
					separate from the host OS and other applications.
					
					Any custom subdirectories within the SQL Server software library 
					directory.  If any directories or files not installed with the SQL 
					Server software exist with the SQL Server software directory, this 
					is a Finding.
*/

/*  
	Note:	The database environment has it's own dedicated default database folder structure
			and does not colocate or share it's path with other applications, or services.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0004754	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015609
Short Name:         DBMS demonstration and sample databases
Long Name:          Default demonstration and sample database objects and applications 
					should be removed.
					
					Demonstration and sample database objects and applications present 
					publicly known attack points for malicious users. These demonstration 
					and sample objects are meant to provide simple examples of coding 
					specific functions and are not developed to prevent vulnerabilities 
					from being introduced to the DBMS and host system.
					Default demonstration and sample database objects and applications 
					have not been removed.
					
					Drop sample or demonstration databases from production instances.
					Verify that no production objects have been stored in demonstration 
					or sample databases prior to dropping.
						
						Example:
						drop database [database name]
						
						drop database [northwind]
						drop database [pubs]
						drop database [adventureworks]
						drop database [adventureworksdw]	
						drop database [adventureworksas]
						drop database [dataencryptdemo]
						
*/

------------------------
-------------SYNTAX REFERENCE
/*

select name from master..sysdatabases
where name in 
		(
		  'northwind'
		, 'pubs'
		, 'adventureworks'
		, 'adventureworksdw'
		, 'adventureworksas'
		, 'dataencryptdemo'
		)
*/
------------------------
------------------------

/*  Note:	We do not incorporate sample databases within the SQL Server environment.
			However; if those database happen to exists this script will automatically
			remove them.		
*/

------------------------
------------------------SOLUTION

if exists ( select name from master..sysdatabases where name = 'northwind' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'northwind' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [northwind]
	end
go
if exists ( select name from master..sysdatabases where name = 'pubs' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'pubs' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [pubs]
	end
go
if exists ( select name from master..sysdatabases where name = 'adventureworks' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'adventureworks' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [adventureworks]
	end
go
if exists ( select name from master..sysdatabases where name = 'adventureworksdw' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'adventureworksdw' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [adventureworksdw]
	end
go
if exists ( select name from master..sysdatabases where name = 'adventureworksas' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'adventureworksas' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [adventureworksas]
	end
go
if exists ( select name from master..sysdatabases where name = 'dataencryptdemo' )
	begin
		set nocount on
		declare		@processkiller varchar(max)
		select		@processkiller = coalesce 
					( @processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
		from master..sysprocesses 
		where 
		db_name(dbid) = 'dataencryptdemo' 
		and spid <> @@spid	
		and spid <> 0
		exec (@processkiller)
		drop database [dataencryptdemo]
	end
go

------------------------
------------------------

PRINT '***    STIG VULNERABILITY KEY:  V0015609	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0005685
Short Name:         Database auditing
Long Name:          Required auditing parameters for database auditing should be set.
					
					Auditing provides accountability for changes made to the DBMS 
					configuration or its objects and data. It provides a means to 
					discover suspicious activity and unauthorized changes. Without 
					auditing, a compromise may go undetected and without a means to 
					determine accountability.  If C2 Auditing is enabled this check 
					is Not a Finding.
					
					C2 audit mode data is saved in a file in the default data directory 
					of the instance. If the audit log file reaches its size limit of 200 
					megabytes (MB), SQL Server will create a new file, close the old 
					file, and write all new audit records to the new file. This process 
					will continue until the audit data directory fills up or auditing is 
					turned off. To determine the status of a C2 trace, query the 
					sys.traces catalog view.
					
				    Below you will find a syntax reference to enabling the C2 AUDITING.
										
					Warning:
					C2 audit mode data is saved in a file in the default data directory 
					of the instance. If the audit log file reaches its size limit of 200 
					megabytes (MB), SQL Server will create a new file, close the old 
					file, and write all new audit records to the new file. This process 
					will continue until the audit data directory fills up or auditing is 
					turned off. To determine the status of a C2 trace, query the 
					sys.traces catalog view.
					
					C2 audit mode saves a large amount of event information to the log 
					file, which can grow quickly. If the data directory in which logs 
					are being saved runs out of space, SQL Server will shut itself down. 
					If auditing is set to start automatically, you must either restart 
					the instance with the -f flag (which bypasses auditing), or free up 
					additional disk space for the audit log.
					
					Note: ( Regarding C2 Auditing Deprication )
					This feature will be removed in a future version of Microsoft 
					SQL Server. Avoid using this feature in new development work, 
					and plan to modify applications that currently use this feature. 
					The C2 security standard has been superseded by Common Criteria 
					Certification. See the common criteria compliance enabled Server 
					Configuration Option.
					
					Below you will find a syntax reference to enabling 
					the COMMON CRITERIA COMPLIANCE.
					
	Note:  
	The following script returns the 'Engine Edition' has a number ID.  Those
	ID's are the following:
		
	1 = Personal or Desktop Engine 
		(Not available in SQL Server 2005 and later versions.)
	2 = Standard 
		(This is returned for Standard, Web, and Business Intelligence.)
	3 = Enterprise 
		(This is returned for Evaluation, Developer, and both Enterprise editions.)
	4 = Express 
		(This is returned for Express, Express with Tools and Express with Advanced Services)
	5 = SQL Azure
	
	Script:   select serverproperty ('engineedition')
*/

use master
go

------------------------
-------------SYNTAX REFERENCE
-------------   C2 AUDITING  
/*

 
	The following script will turn on C2 Auditing.  After setting the value, you must 
	stop and restart the server for C2 audit mode to be take effect.
	
	%\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. 
	This is for default instance of SQL Server and file will be in 
	audittraceyyyymmddHHMMSS format.  
	
	Note:  Where percent (%) is the Drive letter. Example C:\... or D:\... etc.
	
	SELECT * FROM ::fn_trace_gettable
	(
	  '%:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\audittrace20100504131012.trc'
	, default
	)

*/

sp_configure 'show advanced options', 1 ;
go
reconfigure ;
go

sp_configure 'c2 audit mode', 1 ;
go
reconfigure ;
go

--	To disable this feature.

--sp_configure 'c2 audit mode', 0
--go

------------------------
------------------------
------------------------
-------------SYNTAX REFERENCE
/********  COMMON CRITERIA COMPLIANCE *******/
/*

sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'common criteria compliance enabled', 1;
go
reconfigure
go

*/
------------------------
-------------SYNTAX REFERENCE
/****** FIND CONFIGURATION PARAMETERS *******/
/*

declare @show_advanced_options_config int
set		@show_advanced_options_config = 
		( 
		select cast (value as int) from sys.configurations where name = 'show advanced options' 
		)

select	@show_advanced_options_config
go

declare @default_trace_enabled_config int
set		@default_trace_enabled_config = 
		(
		select cast (value as int) from sys.configurations where name = 'default trace enabled' 
		)

select @default_trace_enabled_config
go

declare @allow_updates_config int
set		@allow_updates_config = 
		(
		select CAST (value as int ) from sys.configurations where name = 'allow updates'
		)

select @allow_updates_config

*/
------------------------
------------------------

PRINT '***    STIG VULNERABILITY KEY:  V0005685	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002507
Short Name:         DBMS audit data maintenance
Long Name:          Audit trail data should be retained for one year.
					
					Without preservation, a complete discovery of an attack or suspicious 
					activity may not be determined. DBMS audit data also contributes 
					to the complete investigation of unauthorized activity and needs to be 
					included in audit retention plans and procedures. 
					
					Develop, document and implement an audit retention policy and procedures.
					It is recommended that the most recent thirty days of audit logs remain 
					available online. After thirty days, the audit logs may be maintained 
					offline.  Online maintenance provides for a more timely capability and 
					inclination to investigate suspicious activity.
*/

/*
	Note:	This will require further management of the C2/CC trace files.  Once produced
			to be maintained for a period of 30 days online, then old .trc files copied to
			disk. An SQL Job can be easily created, scripted, and perform the work using
			standard xcopy, and forfiles for retention ( -365 ) days.  Actual management
			of these files, and subsequent capacity falls onto the hosting facility. 
			
*/



PRINT '***    STIG VULNERABILITY KEY:  V0002507	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0003807
Short Name:         DBMS software access audit
Long Name:          All applications that access the database should be logged in the 
					DBMS audit trail where available.
					
					Protections and privileges are designed within the database to 
					correspond to access via authorized software. Use of unauthorized 
					software to access the database could indicate an attempt to bypass 
					established permissions. Reviewing the use of application software to 
					the database can lead to discovery of unauthorized access attempts.
					It is a finding if applications that access the database are not 
					logged in the audit trail.
					
					Review the DBMS audit trail to determine if the names 
					[or unique identifiers] of applications used to connect to the 
					database are included.  If an alternate method other than DBMS 
					logging is authorized and implemented, review the audit trail to 
					determine if the names [or unique identifiers] of applications 
					used to connect to the database are included.  If application access 
					to the DBMS is not being audited, this is a Finding.  If auditing 
					does not capture the name [or unique identifier] of applications 
					accessing the DBMS at a minimum, this is a Finding.
					
					Modify auditing to ensure audit records include identification of 
					applications used to access the DBMS.  Ensure auditing captures the 
					name [or unique identifier] of applications accessing the DBMS at a 
					minimum.  Develop or procure a 3rd-party solution where native DBMS 
					logging is not employed or does not capture required information.
*/

/*
		Note:	This is already addressed in the C2\CC Configurations specified in the 
				STIG VULNERABILITY KEY:  V0005685
*/

PRINT '***    STIG VULNERABILITY KEY:  V0003807	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015152
Short Name:         DBMS password complexity
Long Name:          DBMS login accounts require passwords to meet complexity requirements.
					
					Weak passwords are a primary target for attack to gain unauthorized 
					access to databases and other systems. Where username/password is 
					used for identification and authentication to the database, requiring 
					the use of strong passwords can help prevent simple and more 
					sophisticated methods for guessing at passwords.  DBMS login account 
					passwords do not meet complexity requirements.
					
					If the server is configured to allow SQL Server Authentication, 
					verify passwords are checked for complexity requirements where DBMS
					version permits.
					
					The following script will check the password policy on the server.
					This setting depends upon host system password complexity 
					settings. The host system must be configured to comply with Windows 
					STIG requirements.
					
					Example:  alter login [login name] check_policy = on
					
					When CHECK_POLICY is set to ON, the HASHED argument cannot be used.
					When CHECK_POLICY is changed to ON, the following behavior occurs:
					CHECK_EXPIRATION is also set to ON, unless it is explicitly set to OFF.
*/


/*

		Note:  
		If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be setto ON. 
		Otherwise, the statement will fail.  
		If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN 
		statement that has this combination of options will fail.
		You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. 
		For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:
		"Msg 15151, Level 16, State 1, Line 1
		"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."
		This is by design through Microsoft.

*/



------------------------
-------------SYNTAX REFERENCE
/*

To alter an existing sql account with configuration
changes to the check_policy, or check_expiration:

use master
go
alter login MyLogin with password = 'samepassword'
go
alter login test_must_change with
       check_policy = on
     , check_expiration = off
go

To alter existing login with new password:

use master
go
alter login MyLogin with password = 'newpassword'
go

*/
------------------------
------------------------
set nocount on
declare		@enable_check_policy	varchar (2000)
select		@enable_check_policy	= coalesce	
(@enable_check_policy + 'use master; alter login ' + name + ' with check_policy = on, check_expiration = on;' + CHAR(10), '')
from mydatabase_01.sys.sql_logins where name not like '%#%'

select (@enable_check_policy)
--exec   (@enable_check_policy)
go

PRINT '***    STIG VULNERABILITY KEY:  V0015152	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015614
Short Name:         DBMS residual data clearance
Long Name:          The DBMS should be configured to clear residual data from memory, 
					data objects and files, and other storage locations.
					
					Database storage locations may be reassigned to different objects 
					during normal operations. If not cleared of residual data, sensitive 
					data may be exposed to unauthorized access.  The DBMS is not 
					configured to clear residual data from memory, data objects or files,
					or other storage locations.
*/

/*	
Note:	This STIG has been satisified by STIG VULNERABILITY KEY:  V0005685
		Common Criterial Compliance configuration 
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015614	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015619
Short Name:         DBMS replication account privileges
Long Name:          Replication accounts should not be granted DBA privileges.
					
					Replication accounts may be used to access databases defined for 
					the replication architecture. An exploit of a replication on one 
					database could lead to the compromise of any database participating 
					in the replication that uses the same account name and credentials. 
					If the replication account is compromised and it has DBA privileges, 
					the database is at additional risk to unauthorized or malicious action.
					
					1. Ensure replication data is encrypted in transit. Review 
					documentation and evidence of configuration for encrypted connections 
					between remote databases participating in replication where 
					transmissions cross untrusted (support connections that do not have 
					a need-to-know access requirement to the data being replicated) networks.
					
					Use the following statement to determine encrypted state.
					
						Example:
							use master
							go
							exec sp_helpreplicationdboption
							go
							
					2. Confirm replication agents use dedicated accounts.
					
					Use the following statement to view the replication agent accounts.
					
						Example:
							use msdb
							go
							select p.name 'proxy name', c.credential_identity
							from sys.credentials c, sysproxies p, sysproxysubsystem s
							where c.credential_id = p.proxy_id
							and s.proxy_id = p.proxy_id
							and s.subsysstem_id > 3
							and s.subsystem_id < 9
							go
							
					If any user accounts are returned; run the following statement:
						Example:
							use msdb
							go
							
							exec sp_helpuser 'MyUserName'
							go
							
						Note: 
							If any GroupName other than db_owner is listed in any 
							database, this is a Finding. If any GroupName is listed 
							in any database other than replication databases, 
							this is a Finding

					3. Confirm Replication Agent accounts are assigned minimum privileges. 
					For each database, review assigned roles/permissions for each agent account.
					For each agent account listed under #2 above run the following statement.
					
						Example:
						use MyDatabase
						go
					
						exec sp_helpuser '[sqlaccount01]'
						exec sp_helprotect '[user name]'
					
					Note:	If any permission is listed, this is a Finding. Simply run the
							following statement once.
							
						Example:
						use MyDatabase
						go
						
						exec sp_helpsrvrolemember
						go
						
					If any replication agent accounts are listed, this is a Finding.
					
					4. Confirm only authorized Merge and Distribution Agent accounts are 
					listed in the Publication Access List (PAL) 
					For each replication database:	EXEC SP_HELPPUBLICATION 
					For each publication listed:	EXEC SP_HELP_PUBLICATION_ACCESS  '[publication name]'
					
					If any accounts are listed under publications that are not SYSADMINs, replication 
					merge(category REPL-Merge) or replication distributor (category REPL-Distribution) 
					agent accounts, this is a Finding. 
					
					5. Confirm minimum permissions are assigned to any local snapshot folders.
					
					6. (cont from 5) Confirm snapshot Agent accounts are granted only write permissions 
					to the snapshot folder. If the snapshot agent account has more than write access to 
					the snapshot folder, this is a Finding.
					
					7. Verify network shares are used for snapshot folders accessed by pull subscriptions
					If the server does not have a Publisher database, this check is Not a Finding. 
					
					For each publisher database run the following statement.
					
						Example:
							use MyDatabase
							go
							exec sp_helpsubscription
					
					If any subscribers listed indicate a remote database (a database on a different server), 
					then confirm the snapshot folder is defined as a network share. If it is not, this is 
					a Finding. Note: See folder information for the publication listed for the subscriber 
					under the SP_HELPPUBLICATION results. Windows shares are indicated with a share icon 
					and are indicated as shared in the directory properties \ share tab.
					
					8. Verify Agent accounts use Windows authentication
					See Agent accounts returned from #2 above
					If any accounts listed are not Windows accounts 
					(display [domain or computername]\[account name]), this is a Finding.
					Fixes:    DB-DG0100-SQLServer9 (Manual)
					
					Disable replication if replication is not required. 
					From the SQL Server Management Studio GUI:
							1.	Expand SQL Server
							2.	Right-click on Replication
							3.	Click Disable Publishing and Distribution
							4.	Complete the steps presented
							
							Secure replication if required, authorized and documented.
							1.	Create and use dedicated Windows-authenticated database accounts 
								for Replication Agent use.
							2.	Assign minimum database and file permissions to the Replication 
								Agent accounts.
							3.	Add only authorized Replication Merge and Distribution Agent 
								accounts (and SYSADMIN accounts) to the PAL.
								
					4. Use network shared for snapshot folders access by pull subscriptions
					Document replication in the System Security Plan, AIS Functional Architecture 
					documentation and authorize with the IAO regardless of requirement.
					
					*** Determine if Replication has been configured for this server. ***
					
					Run the following statement to determine if Replication has been 
					configuration for this environment.
					
						Example:
							use master
							go
							exec sp_get_distributor
							go
										
					If the value of installed is 0, and a review of the System Security 
					Plan confirms the use of replication is not required and not allowed, 
					this check is Not a Finding.  If the value of installed is 1, and 
					a review of the System Security Plan confirms the use of replication 
					is required and allowed, this is Not a Finding. If it is not required 
					or not allowed, this is a Finding.
					
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015619	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015147
Short Name:			DBMS dedicated software directories.
Long Name:          The DBMS data files, transaction logs and audit files should be 
					stored in dedicated directories or disk partitions separate from 
					software or other application files.
					
					Protection of DBMS data, transaction and audit data files stored by 
					the host operating system is dependent on OS controls. When different 
					applications share the same database process, resource contention and 
					differing security controls may be required to isolate and protect one 
					application's data and audit logs from another. DBMS software libraries 
					and configuration files also require differing access control lists.
					
					The DBMS data files, transaction logs or audit files should be stored in 
					dedicated directories or disk partitions separate from software or other 
					application files otherwise this is a finding.
					
					If the program file directory and disk partition is the same as either 
					the DefaultData or the DefaultLog directories, this is a Finding.
					
*/


/*  
Run the following script to check the existing data file partitions under each database.

exec sp_msforeachdb 'use [?]; select left(filename, 3) as ''?  Partitions'' from ?..sysfiles'

If each data file is on the same partition ( excluding TEMPDB ) then the default data paths
will need to change in accordance with STIG Requirements.  Additionally existing databases that
have been created, restored, or refreshed using the same Data file path will need to have their
Physical locations changed.  The following syntax reference will modify user databases, and not
System Database file locations.

*/

------------------------
-------------SYNTAX REFERENCE
/********** ALTER DATA FILE PATH ************/
/*

	To move data files you will need to record the existing logical names, and data file
	locations of each data file under their respective databases.
	Run the following script to determine data files logical name and data file paths.
	
		Example:
			use MyDatabase
			go
			select name, filename from sysfiles
			go
	
	Once this information is recorded you will need to kill all existing sessions in the database,
	and then detach it using the following script.
	
		Example:
			use master
			go
			
			set nocount on
			declare		@processkiller varchar(max)
			select		@processkiller = coalesce 
					   (@processkiller + 'kill ' + cast(spid as varchar(50)) + '; ' + char(10) ,'')
			from master..sysprocesses 
			where 
			db_name(dbid) = 'MyDatabase' 
			and spid <> @@spid	
			and spid <> 0
			exec (@processkiller)
			
			use master
			go
			alter database mydatabase set offline;
			
			
				Note:	At this stage you'll need to manually move the existing DATA file to 
						their new folder locations before running the following script.
			
			
			use master
			go
			
			alter database MyDatabase
				  modify file 
						(
						  name='MyLogicalName'
						, filename='D:\mssql\data\PhysicalFileName.MDF'
						);
			alter database mydatabase set online;
			
			use master
			go
			alter database mydatabase set offline;
			
			/*	
				Note:	At this stage you'll need to manually move the existing LOG file to 
						their new folder locations before running the following script.
			*/
			
			--use master
			--go
			
			--alter database MyDatabase
			--	  modify file 
			--			(
			--			  name='MyLogicalName'
			--			, filename='D:\mssql\data\PhysicalFileName.LDF'
			--			);
			--alter database mydatabase set online;
/*

------------------------
------------------------
-------------SYNTAX REFERENCE
------------------------DETACH & REATTACH
/*
/*
	Once the active processes have been killed you can now run the following script.
*/

exec sp_detach_db 'MyDatabase'

/*	
	You can now move the data and log files to their new locations, and run the following
	script.  Make sure you specify the correct path to where you copied the files.
*/

exec sp_attach_db @dbname = 'MyDatabase', 
   @filename1 = n'c:\program files\microsoft sql server\mssql\data\MyDatabase.mdf', 
   @filename2 = n'c:\program files\microsoft sql server\mssql\data\MyDatabase_log.ldf' 
------------------------
------------------------
------------------------SYNTAX REFERENCE
------------------------CHANGE DEFAULT FILE PATH
/*

use master
go
exec xp_instance_regwrite 
		  'hkey_local_machine'
		, 'software\microsoft\mssqlserver\mssqlserver'
		, 'defaultdata'
		, reg_sz
		, 'D:\mssql\data'  -- Provide the full data file path here (.mdf).
go
exec xp_instance_regwrite 
		  'hkey_local_machine'
		, 'software\microsoft\mssqlserver\mssqlserver'
		, 'defaultlog'
		, reg_sz
		, 'L:\mssql\data'  -- Provide the full data file path here (.ldf).
go

*/

/*	

PRINT '***    STIG VULNERABILITY KEY:  V0015147	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015627
Short Name:         DBMS administrative privilege assignment
Long Name:          Administrative privileges should be assigned to database accounts via 
					database roles.
					
					Privileges granted outside the role of the administrative user job 
					function are more likely to go unmanaged or without oversight for 
					authorization. Maintenance of privileges using roles defined for discrete 
					job functions offers improved oversight of administrative user privilege 
					assignments and helps to protect against unauthorized privilege assignment.
					
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015627	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015628
Short Name:         DBMS application user role privileges
Long Name:          DBMS application users should not be granted administrative 
					privileges to the DBMS.
					
					DBMS privileges to issue other than Database Manipulation Language (DML) 
					commands provide means to affect database object configuration and 
					use of resources. Application users do not require these privileges 
					to complete non-administrative job functions. Where applications require 
					administrative privileges to execute non-administrative functions, 
					exploits of the application can lead to unauthorized administrative 
					access to the DBMS.
					
					Review privileges assigned to application user roles in the database.
					If any privileges other than SELECT, INSERT, UPDATE, DELETE or EXECUTE 
					are assigned to application user roles, this is a Finding.
					
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015628	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015631
Short Name:			DBMS Administrative data access
Long Name:          Access to DBMS system tables and other configuration or metadata should 
					be restricted to DBAs.
					
					Administrative data includes DBMS metadata and other configuration 
					and management data. Unauthorized access to this data could result in 
					unauthorized changes to database objects, access controls, or DBMS 
					configuration.  Access to DBMS system tables and other configuration 
					or metadata is not restricted to DBAs.
					
*/

/*	
	Note:	The System Level, and SQL Server Level Administrators will add/create their own
			DBA groups in accordance with their policies.  We do not add/create seperate SQL
			Server Administrative rights during the configuration process.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015631	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015632
Short Name:         DBA account use
Long Name:          Use of DBA accounts should be restricted to administrative activities.
					
					Use of privileged accounts for non-administrative purposes puts data 
					at risk of unintended or unauthorized loss, modification or exposure. 
					In particular, DBA accounts if used for non- administration application 
					development or application maintenance can lead to miss-assignment 
					of privileges where privileges are inherited by object owners. It 
					may also lead to loss or compromise of application data where the 
					elevated privileges bypass controls designed in and provided by 
					applications.  Use of DBA accounts is not restricted to administrative 
					activities.
					
*/

/*	Note:	DBA Groups which are added to this environment as part of the IT Policy from
			Engineers that manage it are created according to their Production Policies.
			Application accounts are NOT granted System Level Administrative Permissions
			to the SQL Server according to the Installation and Configuration parameters
			during setup.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015632	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015153 
Short Name:         DBMS account password expiration
Long Name:          DBMS account passwords should be set to expire every 60 days or more 
					frequently.

STIG VULNERABILITY KEY:  V0015634
Short Name:         DBMS account password easily guessed
Long Name:          DBMS account passwords should not be set to easily guessed words or 
					values.
					
STIG VULNERABILITY KEY:  V0015635					
Short Name:          DBMS default passwords
Long Name:           DBMS default accounts should be assigned custom passwords.
				

	Note:	The following Vulnerability Keys already meet compliance based on RDBMS
			Password & Complexity Policy Checks.  See STIG VULNERABILITY KEY:  V0015152
			above.
			
				STIG VULNERABILITY KEY:  V0015153 
				STIG VULNERABILITY KEY:  V0015634
				STIG VULNERABILITY KEY:  V0015635
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015153	HAS BEEN SATISFIED.'
PRINT ''
PRINT '***    STIG VULNERABILITY KEY:  V0015634	HAS BEEN SATISFIED.'
PRINT ''
PRINT '***    STIG VULNERABILITY KEY:  V0015635	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015638
Short Name:         DBMS default account names
Long Name:          DBMS default account names should be changed.
					
					Well-known DBMS account names are targeted most frequently by attackers 
					and are thus more prone to providing unauthorized access to the database.
					
					This is based on the "sa" default SQL Login.   Simply rename the "sa"
					with the following script.
					
						Example:
							use master
							go
							alter login sa with name = NewAccountName
					
					To disable the sa account.
					
						Example:
							use master
							go
							
							alter login sa disable
					
*/




PRINT '***    STIG VULNERABILITY KEY:  V0015638	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015644
Short Name:         DBMS access control bypass
Long Name:          Attempts to bypass access controls should be audited.
					
					Detection of suspicious activity including access attempts and 
					successful access from unexpected places, during unexpected times, 
					or other unusual indicators can support decisions to apply 
					countermeasures to deter an attack. Without detection, malicious 
					activity may proceed without impedance.
					
					Run the followin script to determine if the appropriate audit level 
					has been configured.  If the config_value returned is not 'All' 
					or 'Failure', this is a finding.
					
						Example:
							use master
							go
							exec xp_loginconfig 'audit level'
							
					Perform the following steps to enable login auditing through the GUI.
						
						From the SQL Server Management Studio GUI:
							1. Navigate to the SQL Server instance name
							2. Right-click on it
							3. Select Properties
							4. Select Security tab or page
							5. Review Login Auditing selection
							6. Select "Failed logins only" or "Both failed and successful logins" from the Login Auditing section
							7. Apply changes
							8. Exit the SQL Server Management Studio GUI
*/

/*	Note:  The following script will ensure the Audit Level is configured for 'Failed Logins' */

use master
go
set nocount on
exec xp_instance_regwrite 
		  'hkey_local_machine'
		, 'software\microsoft\mssqlserver\mssqlserver'
		, 'auditlevel'
		, reg_dword, 2
go


PRINT '***    STIG VULNERABILITY KEY:  V0015644	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015645
Short Name:			DBMS Privileged action audit
Long Name:          Changes to configuration options should be audited.
					
					The default audit trace provides a log of activity and changes 
					primarily related to DBMS configuration options. The default audit 
					trace option does not provide adequate auditing and should be disabled.
					If the changes to configuration options are not audited this is a
					finding.
					
					The following script will check to see if the default trace has been 
					enabled.  If the 'Config_Value' is 0, then this is not a finding. If
					
					
					Example:
						select cast(value as int) 'config_value' 
						from [master].sys.configurations
						where name = 'default trace enabled'

*/

/*	Note:	The following script will automatically disable the 'Default Trace' */

use master
go
exec sp_configure 'show advanced options', 1
exec sp_configure 'default trace enabled', 0
reconfigure
go

PRINT '***    STIG VULNERABILITY KEY:  V0015645	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015646
Short Name:         DBMS audit record content
Long Name:          Audit records should contain required information.
					
					If C2 Auditing is enabled (See Check DM0510: C2 audit mode), 
					this check is Not a Finding.
					
					Note:
					C2 Auditing has been replaced by 'Common Criteria Compliance' and if
					this featuer is currently enabled then this is not a finding.
					
*/

/*
		Note:	This is already addressed in the C2\CC Configurations specified in the 
				STIG VULNERABILITY KEY:  V0005685
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015646	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015148
Short Name:         DBMS network port, protocol and services (PPS) use
Long Name:          DBMS network communications should comply with PPS usage restrictions.
					
					Non-standard network ports, protocol or services configuration or usage 
					could lead to bypass of network perimeter security controls and 
					protections.  This is a finding DBMS network communications do not 
					comply with PPS usage restrictions.
					
					The port configurations can be determined by performing the following
					operations.
					
					View the port entries from the registry values.
					
						From Regedit find the following:
						
						HKEY_LOCAL_MACHINE \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.[#] \ MSSQLServer \ SuperSocketNetLib \ Tcp\IP[#]  \ TCPDynamicPorts
						HKEY_LOCAL_MACHINE \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.[#] \ MSSQLServer \ SuperSocketNetLib \ Tcp\IP[#]  \ TcpPort
						HKEY_LOCAL_MACHINE \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.[#] \ MSSQLServer \ SuperSocketNetLib \ IPAll \ TCPDynamicPorts
						HKEY_LOCAL_MACHINE \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.[#] \ MSSQLServer \ SuperSocketNetLib \ IPAll \ TcpPort
				
					If any value (including 0) is entered for TCP Dynamic Ports, this is 
					a Finding. A blank value indicates dynamic ports are not enabled and 
					is Not a Finding.
					
						
					View the port entries directly using the SQL Server Configuration Manager.
					
						1.	Go to: Start - Run:  sqlservermanager10.msc
						2.	Expand 'SQL Server Network Configuration'
						3.	Select 'Protocols for MSSQLSERVER'
						4.  On the right pane; Righ-Click TCPIP, and select 'Properties'
						5.	Select the 'IP Addresses' Tab.
						6.	Scroll to the very bottom.  View the 'IPALL' section.
						7.	View the following port values:
								TCP Dynamic Ports: #####  ( Clear this value )
								TCP Ports: ####
						
						8.	Change TCP Ports to: 1433
						9.	Set all TCP Port values for ports accessed across a network 
							boundary to TCP Port 1433.	Ensure port is registered in the 
							PPS CAL for use outside the enclave: http://iase.disa.mil/ports/index.html
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015148	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015150
Short Name:         DBMS System Security Plan
Long Name:          The DBMS requires a System Security Plan containing all required information.
					
					This is based on documentation, and not covered as a technical configuration in SQL Server.  This is not a finding.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015150	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015103
Short Name:         DBMS Audit Tool
Long Name:          An automated tool that monitors audit data and immediately reports 
					suspicious activity should be employed for the DBMS.
					
					Audit logs only capture information on suspicious events. Without an 
					automated monitoring and alerting tool, malicious activity may go 
					undetected and without response until compromise of the database or 
					data is severe.  An automated tool that monitors audit data and 
					immediately reports suspicious activity is not employed for the DBMS.
					
					Review evidence or operation of an automated, continuous on-line 
					monitoring and audit trail creation capability for the DBMS is deployed 
					with the capability to immediately alert personnel of any unusual or 
					inappropriate activity with potential IA implications, and with a 
					user-configurable capability to automatically disable the system if 
					serious IA violations are detected.
					
					Develop or procure, document and implement an automated, continuous 
					on-line monitoring and audit trail creation capability for the DBMS 
					is deployed with the capability to immediately alert personnel of any 
					unusual or inappropriate activity with potential IA implications, 
					and with a user- configurable capability to automatically disable 
					the system if serious IA violations are detected.
				
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015103	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015117
Short Name:         DBMS audit log backups
Long Name:          The DBMS audit logs should be included in backup operations.
					
					DBMS audit logs are essential to the investigation and prosecution of 
					unauthorized access to the DBMS data. Unless audit logs are available 
					for review, the extent of data compromise may not be determined and 
					the vulnerability exploited may not be discovered. Undiscovered 
					vulnerabilities could lead to additional or prolonged compromise of 
					the data. If the DBMS audit logs are not included in backup operations.
										
*/

/*	
	Note:	Backup operations around Audit Logs, Event Logs, or any other file based logging
			solutions are inherited by the hosting facility.
*/


PRINT '***    STIG VULNERABILITY KEY:  V0015117	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002426	
Short Name:         C2 audit mode
Long Name:          C2 Audit mode should be enabled or custom audit traces defined.
					
					The C2 audit mode uses a system-defined trace to collect audit 
					information for MS SQL Server 2000 and higher. It utilizes all 
					security event categories defined within SQL Server, not all of 
					which are required by the Database STIG. Without required auditing, 
					accountability and investigative support is limited.

*/

/*
		Note:	This is already addressed in the C2\CC Configurations specified in the 
				STIG VULNERABILITY KEY:  V0005685
*/

PRINT '***    STIG VULNERABILITY KEY:  V0002426	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002427
Short Name:         Fixed server role members
Long Name:          Fixed Server roles should have only authorized users or groups 
					assigned as members.
					
					Fixed server roles provide a mechanism to grant groups of privileges 
					to users. These privilege groupings are defined by the installation or 
					upgrade of the SQL Server software at the discretion of Microsoft. 
					Memberships in these roles granted to users should be strictly 
					controlled and monitored. Privileges assigned to these roles should 
					be reviewed for change after software upgrade or maintenance to 
					ensure that the privileges continue to be appropriate to the 
					assigned members.  This is a finding if fixed Server roles have 
					unauthorized users or groups assigned as members.
					
					Review the Database, Account and Role as generated. Confirm the 
					account(s) assigned to fixed server roles are documented, authorized 
					and accept as Documentable in VMS.
					
					R with the name of the fixed server role.

					
					Server Role members can be checked with the following script:
					
						Example:
							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'
								
				
					Remove fixed server role assignments from unauthorized users. Grant 
					fixed roles to authorized personnel only. Remove unauthorized accounts 
					from assigned roles with the following script:
					
						Example:
							exec sp_dropsrvrolemember  
								  '[account name]'
								, '[fixed server role name]'
						
						replace [account name] with the name of the account and 
						[fixed server role name]
						
					
*/

/*	
	Note:	No Built-in, or Custom Server Roles are utilized excluding the default Sysadmin
			which is only granted to the appropriate Domain Groups by the Hosting Facility.
			One SQL Login is required for the Application system to work properly which
			is already outlined in the installation and configuration documents.
*/

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'

/*  The following statement will automatically remove sql logins from the SQL Server Sysadmin role, and disable the sa */

declare	@dropfromsysadmin varchar(max)
select	@dropfromsysadmin = 
coalesce (@dropfromsysadmin + 'exec master..sp_dropsrvrolemember ''' + name + ''',' + ' ''sysadmin''; ' + char(10), '')
from master..syslogins 
where sysadmin = '1' and name not in ('my_new_sa')
and name not in ('NT SERVICE\SQLSERVERAGENT','NT SERVICE\MSSQLSERVER','NT AUTHORITY\SYSTEM')

select (@dropfromsysadmin)
exec (@dropfromsysadmin)
--alter login sa disable

PRINT '***    STIG VULNERABILITY KEY:  V0002427	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0003335
Short Name:         SQL and database mail use
Long Name:          SQL Mail, SQL Mail Extended Stored Procedures (XPs) and Database Mail 
					XPs are required and enabled.
					
					The SQL Mail, SQL Mail Extended Stored Procedures (XPs) and Database Mail 
					XPs are used by database applications to provide email messages to and 
					from the database. This capability may easily be abused to send 
					malicious messages to remote users or systems. Disabling its use helps 
					to protect the database from generating or receiving malicious email 
					notifications. It is a finding if the SQL Mail, SQL Mail Extended Stored 
					Procedures (XPs) or Database Mail XPs are enabled on the system and are 
					not required.
					
					Run the following script to check on existing Mail configurations.
					
						Example:
							use master
							go
							select cast(value as int) 'config_value' from [master].sys.configurations
							where name = 'sql mail xps'
							
							select cast(value as int) 'config_value' from [master].sys.configurations
							where name = 'database mail xps'
					
*/

/*	
	Note:	SQL Server Database Mail, and SQL Mail is not required for this application environment 
			so mail configurations have not been explicitly setup, or configured.

			Regardless; The following script will disable SQL Server mail configurations to 
			meet STIG compliance.
			
*/

use master
go

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'sql mail xps', 0
reconfigure

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'database mail xps', 0
reconfigure
go

PRINT '***    STIG VULNERABILITY KEY:  V0003335	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0003336
Short Name:         SQL Server Agent email notification
Long Name:          SQL Server Agent email notification usage if enabled should be 
					documented and approved by the IAO.
					
					SQL Mail accepts incoming database commands via email. This can introduce 
					malicious codes or viruses into the SQL server environment. It is a
					finding if the SQL Server Agent email notification usage is enabled and 
					not documented or approved by the IAO.
				
*/

------------------------
-------------SYNTAX REFERENCE
/*

/*	The following script will enable Agent Mail */

/*

use [msdb]
go
exec msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
go
exec master.dbo.xp_instance_regwrite n'hkey_local_machine', n'software\microsoft\mssqlserver\sqlserveragent', n'usedatabasemail', n'reg_dword', 1
go
exec master.dbo.xp_instance_regwrite n'hkey_local_machine', n'software\microsoft\mssqlserver\sqlserveragent', n'databasemailprofile', n'reg_sz', n''
go

*/

/*   Reading a key via xp_regread  */

/*

declare @registry_value varchar(10)
set		@registry_value = ''

execute xp_regread
  @rootkey = 'root_key',
  @key = 'registry_key',
  @value_name = 'value_name',
  @value = @registry_value output
  
declare @value varchar(100)
declare @key varchar(100)

set @key = 'winrar\shell\open\command'

exec master..xp_regread 
 @rootkey = 'hkey_classes_root',
 @key = @key,
 @value_name = null,
 @value = @value output

select @value

*/
------------------------
------------------------

/*	Note: We do not employ Agent mail in conjunction with this application.  This is not configured or enabled */

PRINT '***    STIG VULNERABILITY KEY:  V0003336	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002461
Short Name:         xp_cmdshell option
Long Name:          Extended stored procedure xp_cmdshell should be restricted to authorized 
					accounts.
					
					The xp_cmdshell extended stored procedure allows execution of host 
					executables outside the controls of database access permissions. 
					This access may be exploited by malicious users who have compromised 
					the integrity of the SQL Server database process to control the host 
					operating system to perpetrate additional malicious activity. This is a
					finding if the Extended stored procedure xp_cmdshell is not restricted 
					to authorized accounts.
					
*/

use master
go

sp_configure 'show advanced options', 1 
reconfigure
go

sp_configure 'xp_cmdshell', '0'
reconfigure with override
go

PRINT '***    STIG VULNERABILITY KEY:  V0002461	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002472
Short Name:         OLE automation procedures option
Long Name:          OLE Automation extended stored procedures should be restricted to 
					sysadmin access.
					
					Extended stored procedures allow SQL Server users to execute 
					functions external to SQL Server. An extended stored procedure is 
					a function within a Windows DLL that can be referenced as a stored 
					procedure. While this feature is a powerful extension of SQL Server, 
					it also increases the risk of SQL Server users gaining unauthorized 
					access to the operating system. The Windows account used by SQL 
					Server to log on determines the security context used by extended 
					stored procedures. Certain sensitive extended stored procedures 
					should be closely monitored. These sensitive stored procedures 
					include the OLE Automation stored procedures. OLE Automation stored 
					procedures can be used to reconfigure the security of other services 
					including IIS (Internet Information Server).  OLE Automation extended 
					stored procedures are not restricted to sysadmin access.
					
					Review the accounts, objects and permissions as generated. Confirm 
					the permissions to the objects are documented, authorized and accept 
					as Documentable in VMS. Confirm the OLE Automation procedures option, 
					where enabled, is documented, authorized and accept as Documentable in VMS.
					
					If the OLE Automation Procedures have not been enabled in the SQL
					Server system configurations ( sys.configurations ), then this is not
					a finding.
*/

use master
go

sp_configure 'show advanced options', 1 
reconfigure
go

sp_configure 'ole automation procedures', '0'
reconfigure with override
go

PRINT '***    STIG VULNERABILITY KEY:  V0002472	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002473
Short Name:         Registry extended stored procedures access
Long Name:          Registry extended stored procedures should be restricted to sysadmin 
					access.
					
					Extended stored procedures allow SQL Server users to execute functions 
					external to SQL Server. An extended stored procedure is a function 
					within a Windows NT DLL that can be referenced as a stored procedure. 
					While this feature is a powerful extension of SQL Server, it also 
					increases the risk of SQL Server users gaining unauthorized access 
					to the operating system. The Windows NT account used by SQL Server to 
					log on determines the security context used by extended stored 
					procedures. Certain sensitive extended stored procedures should be 
					closely monitored. These sensitive stored procedures include the registry 
					editing stored procedures. Registry extended stored procedures can 
					be used to read or change security information, including the NT password 
					database, from the registry.  Registry extended stored procedures 
					are not restricted to sysadmin access.
					
					Run the following script to determine what account have access to the
					extended stored procedures.
					
						Example:
							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
*/

use master
go
revoke execute on [sys].[xp_instance_regread] to [public]
go
revoke execute on [sys].[xp_regread] to [public]
go

PRINT '***    STIG VULNERABILITY KEY:  V0002473	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002485
Short Name:         Remote access option
Long Name:          Remote access should be disabled if not authorized.
					
					The remote access option determines if connections to and from other 
					Microsoft SQL Servers are allowed. Remote connections are used to 
					support distributed queries and other data access and command 
					executions across and between remote database hosts. The list of 
					remote servers determines the servers that have defined for remote 
					connections to and from the SQL Server instance. The list of remote 
					logins determines which users on remote servers can connect to and 
					from other SQL Servers. Remote servers and logins that are not 
					properly secured can be used to compromise the server.

*/

/*
	Note:	Remote Access is essential for the proper operation of this application environment
			and as such is authorized and documented.  This is not a finding.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0002485	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002487
Short Name:         Authentication mode
Long Name:          SQL Server authentication mode should be set to Windows authentication 
					mode or Mixed mode.
					
					SQL Server authentication does not provide a sufficiently robust 
					password complexity and management capability to meet stringent 
					security requirements. SQL Server allows use of Windows authentication, 
					a more robust and security authentication service, to control access 
					to the database. It is a finding if the SQL Server authentication mode 
					is not set to Windows authentication mode or Mixed mode.
					
					To see what Authentication Mode SQL Server is set at just run the
					following script.
					
						Example:
							use master
							go
							
							exec xp_loginconfig 'login mode'
							
							
*/

/*	Note:	As per our installation instructions the authentication mode is set to 'Mixed' */

PRINT '***    STIG VULNERABILITY KEY:  V0002487	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0002488
Short Name:         CmdExec or ActiveScripting jobs
Long Name:          SQL Server Agent CmdExec or ActiveScripting jobs should be 
					restricted to sysadmins.
					
					SQL Server Agent CmdExec and ActiveScripting subsystems allow the 
					execution of code by the host operating system under the security 
					context. Allow use of these features only to SYSADMINs and use only 
					where necessary to limit risk of database exploit to the host operating 
					system. Members of the SYSADMIN group have access to all proxies and 
					subsystems by default. Additional assignments are not necessary and 
					would be considered suspect.  This is a finding if the SQL Server Agent 
					CmdExec or ActiveScripting jobs have not been restricted to sysadmins.
					
					Run the following script to check for Jobs that are using CmdExec
					operations, or ActiveScripting.
					
						Example:
							use msdb
							go
							exec sp_enum_proxy_for_subsystem @subsystem_name = 'activescripting' 
							exec sp_enum_proxy_for_subsystem @subsystem_name = 'cmdexec'
					
*/

/*	Note:  The SQL Server database installation and configurations do not include any Job creations	*/

PRINT '***    STIG VULNERABILITY KEY:  V0002488	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015137
Short Name:			Error log retention
Long Name:          Error log retention shoud be set to meet log retention policy.
					
					For SQL Server, error logs are used to store system event and system 
					error information. In addition to assisting in correcting system failures 
					or issues that could affect system availability and operation, log 
					information may also be useful in discovering evidence of malicious 
					intent. Management of the error logs requires consideration and planning 
					to prevent loss of security data and maintaining system operation. It is
					a finding if the Error log retention is not set to meet log retention 
					policy.
					
					Review the SQL Server error log usage and determine a strategy for maintenance.
					The strategy should provide for the longest online retention that is 
					considered meaningful and useful. This is determined over a period for 
					operation and depends upon the amount of log data generated. Error logs 
					must be maintained for a minimum of one year (DG0030). Error logs should 
					be moved offline to satisfy this retention requirement. Design the provision 
					for evidence of retention and allow restoration (for review) of the error 
					logs in the System Security Plan.
										
					Review evidence that error log retention is maintained for a minimum of 
					one year. Error logs should be moved offline after 30 days or less 
					depending on system storage capacity.
					
*/


/*
	Note:	SQL Server Error logs are maintained beyond 30 days.  For durations up to a year
			and beyond through offline retention the criteria is left to the hosting facility
			to determine how best to handle this requirement.
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015137	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015125
Short Name:         SQL Server Agent permissions to proxies
Long Name:          Only authorized users should be assigned permissions to SQL Server 
					Agent proxies.
					
					Database accounts granted access to SQL Server Agent proxies are 
					granted permissions to create and submit specific function job 
					steps to be executed by SQL Server Agent. Unauthorized users may 
					use access to proxies to execute unauthorized functions against the 
					SQL Server instance or host operating system. Unauthorized users are 
					assigned permissions to SQL Server Agent proxies.
					
					Run the following script to confirm if Proxies are being used.
					
						Example:
							use msdb
							exec sp_enum_proxy_for_subsystem
							go
							
*/

PRINT '***    STIG VULNERABILITY KEY:  V0015125	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015113
Short Name:         SQL Server replication agent accounts
Long Name:          SQL Server replications agents should be run under separate and 
					dedicated OS accounts.
					
					Use of shared accounts used by replication agents require that 
					all permissions required to support each of the separate replication 
					agent roles (snapshot publication, distribution, log reading, merge 
					publication, queue reading, and replication maintenance) be assigned to 
					the shared account. This translates to excess privilege assignment to 
					the account to perform a specific job task and an exploit to the single 
					account means a compromise to all replication elements accessed by the 
					shared account. Separation of duties by use of separate and dedicated 
					accounts reduces the risk to the entire replication implementation.
					This is a finding if the SQL Server replications agents are not run 
					under separate and dedicated OS accounts.
					
*/

/*	Note:	SQL Server Replication has not been configured for this environment.  */


PRINT '***    STIG VULNERABILITY KEY:  V0015113	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015202
Short Name:         clr_enabled parameter
Long Name:          Use of Command Language Runtime objects should be disabled if 
					not required.
					
					The clr_enabled parameter configures SQL Server to allow or disallow 
					use of Command Language Runtime objects. CLR objects is managed code 
					that integrates with the .NET Framework. This is a more secure method 
					than external stored procedures, although it still contains some risk. 
					Where no external application execution requirements are required, 
					disallowing use of any improves the overall security posture of the 
					database. Use of Command Language Runtime objects is not disabled 
					and not required.
					
					Run the following script to see if the 'clr_enabled' option has been 
					set.
					
						Example:
							use master
							go
							
							select value from master.sys.configurations 
							where name= 'clr enabled'
							
					If the value of Config_Value is 0, this is Not a Finding.
					
*/

/*	Note:	The SQL Server configuration parameter clr_enabled is not required for this enviornment.  This is not a finding.*/


PRINT '***   STIG VULNERABILITY KEY:  V0015202	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015206
Short Name:         XML web service access
Long Name:          Only authorized XML Web Service endpoints should be configured on 
					the server.
					
					XML Web Service endpoints expose the database its data to web 
					service access. Where not carefully designed and implemented, web 
					services can unnecessarily expose the database to additional exploit 
					that compromises data confidentiality and integrity. Removing web 
					service endpoints helps to protect the database from unauthorized 
					web service access. Unauthorized XML Web Service endpoints are 
					configured on the server.
					
					
						Example:
							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
					
					Review the list of any endpoints returned. If no records are 
					returned, this is Not a Finding.
					
*/

/*	Note:  There are no XML Web Service endpoints configured for this environment */

PRINT '***   STIG VULNERABILITY KEY:  V0015206	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015165
Short Name:         Service broker access
Long Name:          Only authorized service broker endpoints should be configured on 
					the server.
					
					Service Broker endpoints expose the database to SQL Server messaging 
					communication access. Where not carefully designed and implemented, 
					messaging communication can unnecessarily expose the database to 
					additional exploit that compromises data confidentiality and 
					integrity. Removing messaging communication endpoints helps to protect 
					the database from unauthorized messaging communication access. This is
					a finding if there are unauthorized service broker endpoints are 
					configured on the server.
					
					Run the following script to check the list of Service Broker endpoints
					on this server. If no records are returned, this is Not a Finding.
					
						Example:
							use master
							go
							select name from [master].sys.service_broker_endpoints
					
*/

/*	
	Note:	Service Broker Endpoints are not configured on this server.  They are not required
			for the application environment.  This is not a finding.
			
*/

PRINT '***   STIG VULNERABILITY KEY:  V0015165	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015198	
Short Name:         Web assistant procedures option
Long Name:          The Web Assistant procedures configuration option should be 
					disabled if not required.
					
					The Web Assistant procedures are used by database applications to 
					create web pages. This capability may easily be abused to send 
					malicious messages to remote users or systems. Disabling its use 
					helps to protect the database from generating or receiving malicious 
					email notifications. It is a finding if the Web Assistant procedures 
					configuration option is not disabled and not required.
					
					Note:
					The SQL Server option known as 'Web Assistant Procedures' has been
					discontinued in SQL Server and nolonger exists as an option.										
*/
PRINT '***   STIG VULNERABILITY KEY:  V0015198	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015201
Short Name:         Cross db ownership chaining option
Long Name:          Cross database ownership chaining, if required, should be documented 
					and authorized by the IAO.
					
					Cross database ownership chaining allows permissions to objects to 
					be assigned by users other than the Information Owner. This allows 
					access to objects that are not authorized directly by the Information 
					Owner based on job functions defined by the owner. Unauthorized access 
					may lead to a compromise of data integrity or confidentiality. It is a
					finding if the cross database ownership chaining is enabled and not 
					documented or authorized by the IAO.
					
					Run the following script to see if there is any cross database
					ownership chaining under each database.  If the value is 0, then this
					is not a finding.
					
						Example:
							select value from sys.configurations
							where name = 'cross db ownership chaining'
					
					
*/

PRINT '***   STIG VULNERABILITY KEY:  V0015201	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015187
Short Name:         DisallowAdhocAccess for providers
Long Name:          Linked server providers should not allow ad hoc access.
					
					Ad hoc access allows undefined access to remote systems. Access to 
					remote systems should be controlled to prevent untrusted data to be 
					executed or uploaded to the local server. It is a finding if the Linked 
					server providers allow ad hoc access.

					The following script will dissalow adhoc access through the default 
					linked server providers.
					
					
*/

use master
go
exec master.dbo.sp_msset_oledb_prop 'adsdsoobject', 'disallowadhocaccess', 1
exec master.dbo.sp_msset_oledb_prop 'msdaosp', 'disallowadhocaccess', 1
exec master.dbo.sp_msset_oledb_prop 'msdasql', 'disallowadhocaccess', 1
exec master.dbo.sp_msset_oledb_prop 'msidxs', 'disallowadhocaccess', 1
exec master.dbo.sp_msset_oledb_prop 'sqlncli10', 'disallowadhocaccess', 1
exec master.dbo.sp_msset_oledb_prop 'sqloledb', 'disallowadhocaccess', 1
go

PRINT '***   STIG VULNERABILITY KEY:  V0015187	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015166
Short Name:         Ad hoc distributed queries option
Long Name:          Database Engine Ad Hoc distributed queries should be disabled.
					
					Adhoc queries allow undefined access to remote database sources. 
					Access to untrusted databases could result in execution of malicious 
					applications and/or a compromise of local data confidentiality and 
					integrity. It is a finding if the Database Engine Ad Hoc distributed 
					queries are not disabled.

*/

select * from sys.configurations
WHERE name = 'ad hoc distributed queries'
go

sp_configure 'ad hoc distributed queries', 0
reconfigure
go

select * from sys.configurations
WHERE name = 'ad hoc distributed queries'
go

PRINT '***   STIG VULNERABILITY KEY:  V0015166	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015173
Short Name:         Database TRUSTWORTHY status
Long Name:          Database TRUSTWORTHY status should be authorized and documented or 
					set to off.
					
					
					The TRUSTWORTHY database setting restricts access to database resources 
					by databases that contain assemblies with the EXTERNAL_ACCESS or 
					UNSAFE permission settings and modules that use impersonation of accounts 
					assigned elevated privileges. Unless all assemblies and code for the 
					database have been reviewed, especially in the case where databases 
					have been detached and attached between server instances, leaving the 
					TRUSTWORTHY status to off can help reduce threats from malicious 
					assemblies or modules.  It is a finding if the Database TRUSTWORTHY 
					status is not authorized and documented and not set to off.
					
					Run the following sript to get a list of databases which have been 
					configured with the 'trustworthy' setting.  If no results are returned
					this is not a finding.
					
						Example:
							select name
							from master.sys.databases
							where is_trustworthy_on = 1
							and name <> 'msdb' and state = 0
					
					If any databases are found with this configuration, and they are not 
					authorized for this setting it can be changed using the following 
					script.
					
						Example:
							alter database [database name] set trustworthy off
							
*/

/*	

Note:	The following script will automatically turn off the trustworthy configuration for all databases
			excluding system level databases.
*/

set nocount on
declare	@disabletrustworthy		varchar(2000)
select	@disabletrustworthy = 
coalesce (@disabletrustworthy + 'alter database [' + name + '] set trustworthy off' + char(10) + 'go' + char(10), '')
from master..sysdatabases where name not in ('msdb', 'tempdb', 'model')

--select (@disabletrustworthy)
exec (@disabletrustworthy)


PRINT '***   STIG VULNERABILITY KEY:  V0015173	HAS BEEN SATISFIED.'
------------------------
------------------------
PRINT ''
------------------------
------------------------
/*
STIG VULNERABILITY KEY:  V0015211
Short Name:         SMO and DMO XPs option
Long Name:          The SMO and DMO SPs option should be set to disabled if not required.
					
					The SMO and DMO XPs are management object extended stored procedures 
					that provide highly privileged actions that run externally to the DBMS 
					under the security context of the SQL Server service account. If these 
					procedures are available from a database session, an exploit to the 
					SQL Server instance could result in a compromise of the host system and 
					external SQL Server resources including the SQL Server software, audit, 
					log and data files. Access to these procedures should be disabled unless 
					a clear requirement for their use is indicated and authorized.  This is
					a finding if the SMO and DMO SPs option is not set to disabled and not 
					required.		
*/

select * from 
sys.configurations
WHERE name = 'smo and dmo xps'

PRINT '***   STIG VULNERABILITY KEY:  V0015211	HAS BEEN SATISFIED.'
PRINT ''

use master
go

alter login sa disable

PRINT ''							
PRINT '***   NOTE:  THE SA HAS BEEN DISABLED.  THE NEW SA IS:  my_new_sa'							

------------------------
------------------------
PRINT ''
PRINT ''
PRINT ''
PRINT '------------------------'
PRINT '------------------------'
PRINT ''
PRINT ' ALL SQL SERVER STIGS HAVE BEEN COMPLETED. YOU MAY NOW EXIT THIS APPLICATION'
PRINT ''
PRINT '------------------------'
PRINT '------------------------'
Advertisements

1 reply »

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