Enable and configure auditing in SQL Server 2008 R2

2 11 2013

Since I mentioned the auditing feature in my prior post I decided to post a quick script to setup and start database auditing in SQL Server 2008 R2.

/* 
Created By: Christopher Provolt
Created: August 2, 2013
Description: Create and intialize database auditing (SQL Server 2008 R2)

**** NOTES ****
Replace ServerName With the name of the server if it is a named instance also include the instance name i.e. ServerName-InstanceName-Audit

Replace Server Audit Name with the name generated for the intial Server Audit object

Make sure to set appropriate file paths to match your environment  */

USE [master] GO

/* Create the initial Server Audit Object */ 
CREATE SERVER AUDIT [ServerName-Audit] TO FILE(FILEPATH = 'E:\Audit\', MAXSIZE=50MB, MAX_ROLLOVER_FILES=200) WITH (QUEUE_DELAY = 1000, ON_FAILURE=CONTINUE) GO

/* Create the Server Audit Specification */  
CREATE SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification] FOR SERVER AUDIT [ServerName-Audit] ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_ACCESS_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OPERATION_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (LOGOUT_GROUP), ADD (SCHEMA_OBJECT_ACCESS_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP), ADD (TRACE_CHANGE_GROUP) GO

/* Enable the Server Audit */ 
ALTER SERVER AUDIT [ServerName-Audit] WITH (STATE = ON)  GO   

 /* Enable the Server Specification Audit */  
ALTER SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification]  WITH (STATE = ON)  GO
Advertisements




Revoke Public / Guest Permissions and tempdb is created from model

2 11 2013

Probably the worst and maybe most important step to STIGing a SQL Server is to remove public and guest permissions, I found a great script over at Fort SQL, http://blogs.technet.com/b/fort_sql/archive/2010/02/04/remove-public-and-guest-permissions.aspx, to perform this action. I would suggest if using this script first ensure that you run it as script only, there is a variable that can be set. This will allow you to roll back any all of the changes that get made when the script is ran in execute mode.

If you use this script it will break your applications that use the SQL Server! It is not a question of if but more of how badly! I recommend that prior to running this script you enable the built-in auditing features of MSSQL Server 2008, as the information collected is invaluable to resolving the permissions issues that will occur.

That being said I ran this script against a database server that hosted the database for SCCM 2007. I knew that SCCM was going to break and was very prepared to deal with that. It wasn’t until several days later that the SQL Server was rebooted and SCCM stopped deploying patches. Looking at the failures in the audit log I quickly realized that guest required the select permissions to sys.sysobjects in the tempdb.

use tempdb
grant select on sys.sysobjects to guest;

executing this resolved the issue and SCCM happily began chugging away with deploying software!

This resolution only worked as long as the SQL Server did not shutdown, as the tempdb is re-created at every startup.  Looking for solutions to this issue many places I looked advised to just create a job to run using the SQL Agent service everytime it started. This seemed like ghetto fix for the problem, what happens when that service fails to start or the script fails to execute? I know the answer is I’m going to have to manually update permissions again! After some more research I found that tempdb is created in the eyes of the model and if I set the permissions in the model database they would translate to the tempdb upon reboot.

use model
grant select on sys.sysobjects to guest;

The problem is now resolved and no longer or will ever require intervention from me.