New features of ALTER SERVER CONFIGURATION in SQL Server 2012

While working on a chapter I am writing, I was going through the ALTER SERVER CONFIGURATION topic in the Books Online and noticed some really useful changes in the syntax that I hadn’t seen before.  Starting in SQL Server 2008 R2, ALTER SERVER CONFIGURATION was introduced to allow for process affinity configuration and to replace the affinity mask sp_configure options for SQL Server, which were marked as deprecated at the same time.  SQL Server 2012 extends the usage to also control diagnostic logging and failover clustering properties as well.

Diagnostic logging in SQL Server 2012 is accomplished through the execution of sp_server_diagnostics, which Joe blogged about last year here and again here.  It can be turned ON or OFF using ALTER SERVER CONFIGURATION, and you can also specify the PATH, MAX_SIZE, and MAX_FILES options to control the logging location, file size, and maximum number of rollover logs to be created and maintained by SQL Server.

— Disable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

— Enable Diagnostics logging
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

— Configure logging path
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG PATH=N'C:\SQLskills\Logs';

— Configure max file sizes
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_SIZE=100 MB;

— Configure max number of files to keep
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_FILES=5;

The flexible failover policy in SQL Server 2012 allows for more fine grained control of when a SQL Server Failover Cluster actually performs a failover of the instance.  This is accomplished through the execution of sp_server_diagnostics and is based on the output of the health checks being performed by the procedure.  The FailureConditionLevel can be set by ALTER SERVER CONFIGURATION, along with the HealthCheckTimeout value to control when a instance performs a failover or restart associated with an unhealthy condition.  You can also set the logging level for the Failover Cluster, and a number of SQLDumper options as well.

— Set failover on any qualified failure condition
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY FailureConditionLevel = 5;

— Set the health check timeout to 120000 milliseconds (2 minutes)
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 120000;

Resolving Error 33204 SQL Server Audit could not write to the security log

This topic came up on a forum post recently, and after responding a couple of times to the thread I had to jump into one of my VMs to figure out what exactly the problem was. 

If you use Server Audits in SQL Server, one of the features that you might be interested in is the ability to have the audit data written into the Windows Security Event Log, especially if your company already has data collection software in place to harvest the log records for auditing purposes.  However, if you try and actually implement a Server Audit in SQL Server 2008 or SQL Server 2008 R2 that writes to the Security Event Log, unless the service is running under the Local System account, the audit will fail.  To demonstrate this, we’ll create a server audit with a server level audit specification to track object accesses in the server and then try to start the audit.  The SQL Server services in my test VM are running under a local low privilege user named SQLServiceAcct, and all of the steps described in the Books Online topic Write SQL Server Audit Events to the Security Log have been followed to enable the instance for writing to the Windows Security Log.

CREATE SERVER AUDIT [Audit_ToSecurityLog]
TO SECURITY_LOG
WITH(QUEUE_DELAY = 0, ON_FAILURE = CONTINUE)
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec_ObjectAccess]
FOR SERVER AUDIT [Audit_ToSecurityLog]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO

ALTER SERVER AUDIT [Audit_ToSecurityLog]
WITH (STATE = ON);

Once we attempt to enable the server audit the following error will be logged into the SQL Server ERRORLOG file:

2012-06-22 20:48:09.92 spid53      Error: 33204, Severity: 17, State: 1.
2012-06-22 20:48:09.92 spid53      SQL Server Audit could not write to the security log.

Now, since I knew for a fact that I had followed all of the steps in the Books Online, I decided to fire up Process Monitor and setup a filter on the Process Name so that if it wasn’t sqlservr.exe the events were excluded from the display.

image

Then I disabled the server audit and enabled it again so that I could see what events occurred in Process Monitor leading to the failure writing to the Security Event Log.

image

Here we can see that the problem is that SQL Server needs to Read/Write access to the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security registry key but does not have permission.  So to fix this I opened up the registry editor to this key and then added the SQL Server service account for my VM to the key with Full Control as show below.

image

image

Once this was completed, I went back into SQL Server Management Studio and disabled and re-enabled the audit again.  This time no error was logged to the ERRORLOG file, so I went back to Process Monitor to see what changed.  This time we can see that SQL Server actually added a key under the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security key named MSSQLSERVER$AUDIT, which makes it possible for the instance to write into the Security Event Log.

image

If we refresh the key in the registry editor we can see the new key and the values set underneath it.

image

I tested this in SQL Server 2012 RTM and the behavior is exactly the same.  Once the MSSQLSERVER$AUDIT key has been created, you can remove the permissions from the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security key and the auditing will continue to work (I had to test it out of curiosity).

Testing the audit after fixing the registry permissions yields the expected events in the Security Log:

Log Name:      Security
Source:        MSSQLSERVER$AUDIT
Date:          6/22/2012 8:43:47 PM
Event ID:      33205
Task Category: (3)
Level:         Information
Keywords:      Classic,Audit Success
User:          SQL2K8R2-IE2\SQLServiceAcct
Computer:      SQL2K8R2-IE2
Description:
Audit event: event_time:2012-06-23 00:43:47.4280312
sequence_number:1
action_id:SL 
succeeded:true
permission_bitmask:1
is_column_permission:true
session_id:52
server_principal_id:261
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:898102240
class_type:U
session_server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_sid:010500000000000515000000f84a18afb97103f8a5b4f260e8030000
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SQL2K8R2-IE2
database_name:AdventureWorks2008R2
schema_name:Sales
object_name:SalesOrderDetail
statement:SELECT * FROM Sales.SalesOrderDetail
additional_information:
.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQLSERVER$AUDIT" />
    <EventID Qualifiers="16384">33205</EventID>
    <Level>0</Level>
    <Task>3</Task>
    <Keywords>0x80a0000000000000</Keywords>
    <TimeCreated SystemTime="2012-06-23T00:43:47.428031200Z" />
    <EventRecordID>47907</EventRecordID>
    <Channel>Security</Channel>
    <Computer>SQL2K8R2-IE2</Computer>
    <Security UserID="S-1-5-21-2937604856-4160975289-1626518693-1001" />
  </System>
  <EventData>
    <Data>event_time:2012-06-23 00:43:47.4280312
sequence_number:1
action_id:SL 
succeeded:true
permission_bitmask:1
is_column_permission:true
session_id:52
server_principal_id:261
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:898102240
class_type:U
session_server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_sid:010500000000000515000000f84a18afb97103f8a5b4f260e8030000
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SQL2K8R2-IE2
database_name:AdventureWorks2008R2
schema_name:Sales
object_name:SalesOrderDetail
statement:SELECT * FROM Sales.SalesOrderDetail
additional_information:
</Data>
  </EventData>
</Event>

Fixing VirtualBox “Could not find an open hard disk with UUID” Errors with Differencing Disks

A few weeks back, I needed to make space on my laptop SSDs for some new VMs for future training classes we’ll be doing and as a part of doing this I decided to copy a number of my VirtualBox VMs to an external hard disk.  What I had expected was that this would work the same way that it does in VMware Workstation, where the next time I opened the VirtualBox VM .vbox file it would realize that the files had been moved and prompt me for what to do. Instead what I got was a error message similar to the following:

image

Failed to open virtual machine located in D:/Virtual Machines/SQL2K12-SVR4/SQL2K12-SVR4.vbox.

Could not find an open hard disk with UUID {0966d12e-d694-41ec-81ce-8f6e57c530a9}.

Result Code:
VBOX_E_OBJECT_NOT_FOUND (0x80BB0001)

Component:
VirtualBox

Interface:
IVirtualBox {c28be65f-1a8f-43b4-81f1-eb60cb516e66}

If I looked at the system, all of the files were there, so I decided to try copying it back to the original location and once again got the same error message.  At this point I was pretty much flustered because I had over 200GB of VMs that I had moved and were no longer functioning, comprising 14 of my test and teaching VMs for various topics.  After about an hour of trying to figure this out I gave up and essentially accepted that I had lost dozens of hours of work building my environments and set about to rebuilding them in VMware Workstation which allows me to copy and move VMs at will without having these kinds of problems pop up.

Today I was working through rebuilding one of my demo environments and I decided to try and get into my VirtualBox VMs again to try and save a little time on this.  I knew that I could create a new VM and use the existing hard disk from the copied files to boot into the VM, but the problem with that in the past was that it changed out all the hardware and removed the vNIC settings requiring Windows to be reconfigured.  However, what I found was that by creating a new VM and then selecting the existing disk, I was able to now open the vbox file successfully from the file system to get the VM back into Oracle VM VirtualBox Manager.  I was able to successfully open up each of my “previously lost” VMs by opening the .vhd for the VM in the Create New Virtual Machine Virtual Hard Disk window and then opening the .vbox file.  Once this was done, I was able to cancel the Create New Virtual Machine dialog and still access my VMs.  Based on this experience I am going to continue moving my VM’s back over to VMware Workstation which has never left me out to dry like this when copying VMs but at least for now I have my VMs back.