Common Criteria Compliance - Description, Usage, and Implementation within SQL Server

Common Criteria Compliance (usually abbreviated as CC) is a method of assuring that I.T. products meet a defined set of security specifications.  It attempts to standardize the evaluation of security for a wide variety of systems.  The purpose of this article is to give a brief explanation of the overall purpose of CC, and a detailed analysis of its usage within SQL Server (in particular, SQL Server 2005.)  For more information on the actual evaluation process and history of CC in general, refer to wikipedia's entry.

The Common Criteria (CC) establishes a common set of requirements for the security functionality of I.T. products.  It is “intentionally flexible”, meaning that a certain amount of freedom is initially given to the implementation of the described requirements.  It falls upon the vendor to determine an appropriate implementation, depending on the intended Evaluation Assurance Level (EAL).  A specific application, operating system, or even a list of configuration files is referred to as a Target of Evaluation (TOE).  The terms have a considerably broad range upon what they can reference.  In this case, our TOE is a SQL Server installation.

The EAL is a rating given to a package of security requirements, ranging from EAL1 to EAL7.  A higher number implies a more rigorous verification process for deciding if the implementation is consistent with the requirements.  It does not necessarily imply a higher degree of security.

Delving into the exact nature of the verification process is well beyond the scope of any SQL DBA’s needs.  This 62-page document from Microsoft describes the evaluation process and high-level implementation.   However, more detailed configuration is required to ensure that our SQL server is consistent with the established guidelines for the varying levels of EAL compliance.

Microsoft has provided a security configuration option for EAL1.  This is a server-level option available via SSMS:

Right-click on the server name in object explorer.  Select properties.

Click the security tab.  The checkbox enable common criteria compliance is located near the bottom.

It can also be activated via T-SQL script:

  
sp_configure 'show advanced options', 1;
GO 
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE
GO

 

Important: In SQL Server 2005, this option is available only on enterprise, developer, and evaluation editions.  If you receive the error message: The configuration option 'common criteria compliance enabled' does not exist, or it may be an advanced option, it is likely you are running an edition of SQL Server that does not support this option.

You can also check the sys.configurations table.  If available, CC will be configuration_Id 1577.  If it's not listed, you are running a SQL Server edition that does not support CC.

After this option has been enabled, the following elements will be active:

Residual Information Protection (RIP) – memory is overwritten with a bit pattern prior to being reallocated.  This could potentially have a mild impact on overall server performance.

This implementation of RIP appears to be of Microsoft’s design; like many requirements described in CC, the wording is flexible (or vague, if you prefer) and could have a number of acceptable implementations.

Login auditing becomes active - SQL Server tracks both successful and unsuccessful logins, including the number of attempts made overall.  Querying the Sys.dm_exec_sessions DMV will display this information.

Change in behavior to column/table DENY and GRANT permissions – Normally, a column-level grant will override a table-level DENY and permit access to that specific column.  With CC enabled, this is no longer true, and access will be denied.

Enabling this option achieves EAL1 for your SQL Server installation.  In order to comply with EAL4+, several other events must occur:

  1. The default trace must be running.  This is a server-side trace that is enabled by default.  To verify it is active, execute:
SELECT* FROM fn_trace_getinfo(default);
 

Several rows with a traceid column with the value of “1” should be present.  If no rows are returned, enable the default trace using the following code:

  
EXEC master.dbo.sp_configure 'allow updates', 1;
GO
EXEC master.dbo.sp_configure 'show advanced options', 1;
GO
EXEC master.dbo.sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'allow updates', 0;
GO

 

2.  A second server-side trace with specific options must be executed upon startup of the SQL service.  Microsoft has provided the script for this trace.  There are a few components to it:

The first section checks to see if you’re running SQL Server 2005 SP1.  It’s very unlikely that you are, but there doesn’t seem to be any reason why the stored procedure wouldn’t work on subsequent service packs.  I removed this section, and the trace executes correctly on SP3.

  
-- If the version is not SP1 then do not run the script
IF SERVERPROPERTY(N'ProductVersion') <> '9.00.2047.00'
BEGIN
	RAISERROR('You can turn on EAL1 trace only on SQL Server 2005 SP1', 20, 127) WITH LOG
END

USE master
GO

if object_id('dbo.sp_create_evaltrace','P') IS NOT NULL
	drop procedure dbo.sp_create_evaltrace
GO

 

The second section attempts to determine the location of your \LOG directory based on registry entries.  If this doesn’t work, you can just to set the @Tracefile parameter manually.  It then sets the parameters of the trace using sp_trace_create.  The important number here is the second parameter (6).   This is the options parameter.  A 6 specifies that both options 2 and 4 are active -  2 causes the trace file to rollover at 100 meg increments to a new file, and 4 shuts down the SQL service if the trace fails.  If a single trace file is desired for some reason, the rollover can be disabled by using only the 4 option.   This is acceptable within the confines of CC; but the server must stop if the trace fails.  The rollover size is also configurable.

  
CREATE PROCEDURE sp_create_evaltrace
-- Create the trace
AS
	-- Declare local variables
	declare @rc int
	declare @on bit
	declare @instanceroot nvarchar(256)
	declare @scriptname nvarchar(50)
	declare @tracefile nvarchar(256)
	declare @maxfilesize bigint
	declare @filecount int
	declare @traceid int 

	set @maxfilesize =100
	set @filecount =100

	-- Trace file name
	set @scriptname = 'cc_trace_' + REPLACE(REPLACE(CONVERT( varchar(50), getdate(),126), ':', ''), '.','')

	-- Get the instance specific LOG directory
	-- Get the instance specific root directory.
	set @instanceroot = ''
	exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @instanceroot OUTPUT

	IF @instanceroot = '' OR @instanceroot = NULL
	BEGIN
		-- Exit the procedure
		raiserror ('Could not obtain the instance root directory using xp_instance_regread.', 18,127)
		return(1)
	END

	-- Prepare the Trace file.
	IF SUBSTRING(@instanceroot, Len(@instanceroot)-1, 1) != '\'
		set @instanceroot = @instanceroot + '\'

	set @tracefile = @instanceroot + 'LOG\'+ @scriptname


	-- Create the trace
	exec @rc = sp_trace_create @traceid OUTPUT, 6, @tracefile, @maxfilesize, NULL , @filecount
	IF (@rc != 0) 
	begin
		return (1)
	end
 

The third section contains the events logged by the trace, which are briefly described in the comments of the code.

  
	-- Add Trace Events
	set @on = 1
 
	-- Audit Login
	exec sp_trace_setevent @TraceID, 14, 1, @on  -- TextData
	exec sp_trace_setevent @TraceID, 14, 11, @on  -- LoginName
	exec sp_trace_setevent @TraceID, 14, 14, @on  -- StartTime
	exec sp_trace_setevent @TraceID, 14, 21, @on  -- EventSubClass
	exec sp_trace_setevent @TraceID, 14, 23, @on  -- Success
	exec sp_trace_setevent @TraceID, 14, 64, @on  -- SessionLoginName

 

There are quite a few and many of the meanings aren't clear from the comments.  In more detail, here's what each does.  Pay special attention to the scopes described in the GDR event classes, as similar language is used for the subsequent classes.

Audit Schema Object GDR Event - GDR refers to Grant, Deny, Revoke.  The terminology here can get a bit confusing.  This event class doesn't apply to permissions on an actual schema, but to any permissions GDR'd on objects within a database (tables, views, etc) that are underneath a schema.

Audit Database Scope GDR Event - Refers to GDR events at the actual database level (such as CREATE TABLE), but not to individual objects within a database.

Audit Database Object GDR Event - Refers to GDR events on an actual schema (such as GRANT SELECT on SCHEMA::MySchema to MyUser), but not to objects underneath schemas or high-level database permissions.

Audit Server Scope GDR Event - Applies to server-scope GDR events, such as GRANT CREATE ANY DATABASE, that don't apply to a specific object.

Audit Server Object GDR Event - GDR events as the apply to existing server-level objects.

Audit Login: Successful logins.

Audit Logout: All user logouts.

Audit Login failed: Failed Logins.

Audit Login Change Property Event: From MSDN: "occurs when you use the sp_defaultdb stored procedure, the sp_defaultlanguage stored procedure, or the ALTER LOGIN statement to modify a property of a login". Does not get triggered by ALTER LOGIN password changes; that's a separate event class.

Audit Login Change Password Event: Triggered whenever a login's password is changed.

Audit Add Login to Server Role Event: Somewhat poorly named; applies to both adding and removing a login from a server role (sp_addsrvrolemember and sp_dropsrvrolemember)

Audit Add Member to DB Role Event: Again, applies to both adding and removing.  Triggered by sp_addrolemember, sp_changegroup, and sp_droprolemember.

Audit App Role Change Password Event:  Triggered when an application role's password is changed.

Audit Database Object Access - MSDN: "The Audit Database Object Access event class occurs when database objects, such as schemas, are accessed."  This doesn't apply to selecting from tables, views, etc.   I'm not sure this ever really comes into play in any meaningful manner.

Audit Schema Object Access Event: The big one.  Logs any object access to tables, views, stored procedures.  This event will likely account for greater than 99% of the activity in the trace.

Audit Backup/Restore Event: Straightforward.

Audit DBCC Event: Any DBCC execution is logged.

Audit Change Audit Event: Audit-Related events, such as the stopping and starting of traces.

Audit Database Management Event: CREATE, ALTER, or DROP DATABASE events.

Audit Database Object Management Event: Logs CREATE, ALTER, and DROP on Database-level objects.  A bit quirky; It logs CREATE TABLE statements, but not DROP TABLE statements.  I believe after the table is created, it falls only under the Schema Object Management event class.  This class logs both CREATE and DROP on schemas, however, as they are always database-level objects.

Audit Schema Object Management Event: Logs CREATE, ALTER, DROP on any objects underneath a schema - Tables, Views, but not actual schemas.  MSDN claims this tracks server-level objects, but this does not appear to be true and doesn't fit in with the conventions used by the other similarly-named event classes.

Audit Server Principal Impersonation Event and Audit Database Principal Impersonation Event: Supposedly, these track impersonation events such as SETUSER and EXECUTE AS at the database and server level.  I couldn't get either of them to trigger by putting SETUSER in a stored procedure or a SQL agent job.  Neither triggered with EXECUTE AS in the definition of a stored procedure, either, so I'm not sure how helpful these classes could be.

Audit Server Object Take Ownership Event, Audit Database Object Take Ownership Event, and Audit SchemaObject Take Ownership Event - Server/Database/Schema(Object)-scoped ownership changes.  None of these include the actual ownership change of a database; that's a separate class.

Audit Change Database Owner - Tracks SP_CHANGEDBOWNER.

Audit Server Operation Event - MSDN says, "The Audit Server Operation event class occurs when Security Audit operations such as altering settings, resources, external access, or authorization are used."  I made some changes to numerous server settings, but the actual text data in the trace was "Reconfigure with Override" - not the most verbose output.

Audit Server Alter Trace Event - any modifications to a trace, including creating one.

Audit Server Object Management Event - Create/Alter/Drop on server-level objects (Not principals such as logins) like DDL triggers defined on ALL SERVER.

Audit Server Principal Management Event - Create/Alter/Drop on server principals, although I couldn't get it to trigger for ALTER LOGIN statements (CREATE and DROP were fine).

Audit Database Operation - MSDN again: "The Audit Database Operation event class occurs when operations in a database, such as checkpoint or subscribe query notification, occur."

 

The last entry inside the stored procedure body is the execution of the trace. Finally, outside of the stored procedure code, the SP is marked to automatically execute when the SQL service starts. the trace is started via sp_trace_setstatus.

  
	-- Set the trace status to start
	exec sp_trace_setstatus @TraceID, 1
	
	print 'INFO: Successfully created the trace with ID ' + CAST (@traceid AS varchar(10))
	return (0)
GO

declare @rc int

-- Set the proc for autostart
exec @rc = sp_procoption 'dbo.sp_create_evaltrace', 'startup', 'on'
IF @rc != 0
BEGIN
	print 'ERROR: sp_procoption returned ' + CAST(@rc AS NVARCHAR(10))
	print 'ERROR: Could not set sp_create_evaltrace for autostart'
END

-- start the eval trace
exec dbo.sp_create_evaltrace
GO

 

 

The parameters for sp_trace_setstatus are:

0: Stops a trace.

1: Starts a trace.

2: Closes a trace and deletes it from the server.  A trace must be stopped before it can be closed.

3.  Since we’ll be running this stored procedure at startup, the “Scan for startup procs” option must be enabled.  This is off by default, but will be set to active once a stored procedure is marked to execute at startup - so once the final line of code in the above script is executed, the option should be on. It can also be modified through SSMS under the server properties – advanced tab.

Once the above steps have been completed, stop and restart the SQL service.  Once the server starts again, you should see a trace file generated in the path specified in the second section of code above.  At this point, the implementation of CC is complete.