TSQL Cryptographic Patterns – part 9: we’d better take this OFFLINE

There is a compelling defense-in-depth rationale for enabling AUTO_CLOSE on a database where sensitive data is stored.


When AUTO_CLOSE is ON the database will cleanly shutdown when the last active user session disconnects or moves to another database. To be cleanly shutdown means that a database can be opened again later without the need for SQL Server to run recovery on that database. Everything in the log has been processed into the data set. FWIW: we don’t get to decide when recovery runs, the database engine makes that determination. We do get to mess around with the CHECKPOINT a little more in SQL Server 2012 with the TARGET_RECOVERY_INTERVAL database option that overrides the server recovery interval. That actually does appears to be a step in the direction of exposing control of AUTO_CLOSE though probably not intentional.

Using AUTO_CLOSE is easy:

  • Once enable there is nothing to do.
  • The easiest way to tell if AUTO_CLOSE is ON is to query the is_auto_close_on column in sys.databases.
  • The easiest way to tell if a database with AUTO_CLOSE ON is cleanly shutdown is to query the is_cleanly_shutdown column in sys.databases.
  • The most eye-opening way to tell if the database is closed at the present time is to copy the .mdf or .ldf. If you can copy the files the database is cleanly shut down, if you cannot the database is open and accumulating resources i.e., data pages, locks, latches, versions, query plans, connections, etc..

(Note that there are a few respectable bloggers claiming that AUTO_CLOSE is marked for deprecation since SQL 2008. I believe there is some confusion. The blogs I have seen with this claim reference the SQL Server 2000 DMO AutoClose Property page as evidence. If you look, you will notice that all the DMO documentation pages for SQL Server 2000 carry the same deprecation warning. Pretty sure DMO is the deprecated technology not AUTO_CLOSE. I could be wrong.)

When a database cleanly shuts down all resources held for that database are freed from memory. Log records are processed such that no recovery is required when the database “opens”. Encryption keys and certificates are closed preventing any free rides on encryption hierarchies opened during legitimate use. DMV data collected from that database disappears. The file system locks on all log and data files are released. Data is flushed from cache. If TDE or file system encryption is in use, this moves all data behind that layer of obfuscation. The unloading is asynchronous, happening within 300ms.

The main difference between a database with AUTO_CLOSE ON when cleanly shutdown and an OFFLINE database is the AUTO part. That is, an administrator must manually transition the database between ONLINE and OFFLINE and back while AUTO_CLOSE automagically transitions the database between the unmodifiable state and the usable state for any valid request.

I notice that databases do not get the is_cleanly_shutdown bit set when the database is taken OFFLINE. While I cannot repro on demand, I also noticed that taking the test database ONLINE will force a recovery when that database goes back ONLINE every now and again. The documentation is clear that an OFFLINE database is cleanly shutdown. Wonder what’s up with that?

SELECT name, is_auto_close_on, state_desc, is_cleanly_shutdown
FROM sys.databases WHERE PATINDEX('Test%', name ) = 1;

name              is_auto_close_on state_desc  is_cleanly_shutdown
----------------- ---------------- ----------  ------------------- 
Test_OFFLINE                     0    OFFLINE                    0 
Test_AUTO_CLOSE                  1    ONLINE                     1 

The pooled connection overhead and bottlenecking that comes with AUTO_CLOSE are fairly well known. Most of the time that is about all one needs to know to avoid AUTO_CLOSE. The experts simply tell us to turn AUTO_CLOSE off and leave it at that. In fact, the best practice policies included in the SQL Server installation will disable AUTO_CLOSE on all databases.

Enabling the best practice policies is far better than not using policies or following the painful trajectory of trial and error to “find” the correct best practices. In all cases beware the dogma. A well-considered policy built upon best practices, patience and perststence is preferred.

Applications that create and store sensitive data are at risk of compromise if adequate considerations are not given to vulnerabilities that exploit the available SQL Server resource metadata and/or SQL Server primary storage buffers. The query cache, for example, can be helpful in understanding the data store and the data flow. This is useful information for man-in-the-middle, SQL Injection attackers or insider hi-jinx. Likewise, the sys.dm_exec_query_requests DMV or sys.sysprocesses compatibility view will point the uninitiated and uninvited to every client serviced by a database host. From there a SQL Injection attacker can map the application, identify weak hosts inside the DMZ and perhaps establish a SQL Injection based command line access targeting the weak internal node. The ways to be hacked are many.

The security implications of database resources are not normally considered in application design. If anything, database architectures error on the side of keeping resources loaded and exposed by making more memory available to the SQL Server. This increases the risks that cached data, data storage patterns, data flow patterns and cached query text can be mined for malicious purpose. To be sure, database resource exploits do not represent the low hanging fruit, but equally as certainly most of the low hanging fruit has by now been plucked. Nonetheless, within the context of a well-considered defense-in-depth data security architecture securing database resource access is essential. Presuming adequate system obfuscation of buffers in the free pool, releasing resources held in memory will provide a layer of protection against exploits of SQL Server memory space.

From another perspective: only if the storage location is secured and encrypted would it be wise to leverage AUTO_CLOSE as a security layer. Anyone with read access to the storage location can copy the database files when cleanly shutdown. An un-encrypted database file can also be opened in EMERGENCY mode (READONLY) on another SQL Server – illustrating the value of encrypted storage.

Applications with a relatively low rate of change and highly sensitive data, such as the Encryption Hierarchy Administration T-SQL utility that provided example T-SQL for this series and some witness protection relocation databases are candidates for the anti-sniffing and anti-hijacking protections afforded by resource unloading. Furthermore, when TDE is also configured and database resources are unloaded, the most complete benefit for TDE can be achieved. Under such conditions there are no back-doors or alternative access paths that can circumvent the encryption.

I decided to put it to a quick test. The test output below shows the resource caching behavior around AUTO_CLOSE’s clean shutdown and OFFLINE under 3 configuration scenarios:

  3. AUTO_CLOSE ON with Active Service Broker

Connecting to BWUNDER-PC\ELEVEN...
Set database context to 'master'.
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
    Feb 10 2012 19:39:15 
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> 
(Build 7601: Service Pack 1)

Warning: The certificate ~snip~ has not been backed up ~snip~
Set database context to 'tempdb'.
CREATE FUNCTION tempdb.dbo.fnServerResources
CREATE PROCEDURE tempdb.dbo.CheckResourcesFromTempDB
Set database context to 'TestDb'.
CREATE PROCEDURE TestDb.dbo.CheckDbResources
#1  SET AUTO_CLOSE ON                 -- database resources ----------------
                                      sessions   objects   q-stats     locks
try invoke AUTO_CLOSE                        3         2         0         2
Changed database context to 'master'.
wait a second...                             0         2         1         0
cleanly shutdown                             0         0         0         0
Changed database context to 'TestDb'.
initiate OFFLINE                             1         2         0         2
Changed database context to 'master'.
SET OFFLINE                                  0         0         0         0
SET ONLINE                                   1         1         0         2
#2  SET AUTO_CLOSE OFF                -- database resources ----------------
                                      sessions   objects   q-stats     locks
try invoke AUTO_CLOSE                        1         2         0         2
Changed database context to 'master'.
wait a second...                             0         2         1         0
not shutdown                                 0         2         1         0
Changed database context to 'TestDb'.
initiate OFFLINE                             1         2         1         2
Changed database context to 'master'.
SET OFFLINE                                  0         0         0         0
SET ONLINE                                   1         1         0         2
 Configure Service Broker
CREATE PROCEDURE TestDb.dbo.TestQActivationProcedure
1 events enqueued
#3  SET AUTO_CLOSE ON                 --- database resources ---------------
                                      sessions   objects   q-stats     locks
try invoke AUTO_CLOSE                        2         3         1         3
Changed database context to 'master'.
wait a second...                             1         3         2         1
not shutdown                                 1         3         2         1
Changed database context to 'TestDb'.
initiate OFFLINE                             2         3         2         3
Changed database context to 'master'.
SET OFFLINE                                  0         0         0         0
SET ONLINE                                   1         1         0         2
Disconnecting connection from BWUNDER-PC\ELEVEN...

Unfortunately AUTO_CLOSE does not pass this sniff test. It simply is not reliable under many common SQL configurations. It is not consistently good at returning resources to a busy desktop or in closing keys. A persistent connection, a daemon process, a scheduled job, replication, mirroring or Service Broker activation – among other things – can interfere with a clean shutdown leaving the database memory work space and cache always available for malicious perusal. AUTO_CLOSE would too easily becomes a phantom security layer. You might find some comfort that it is enabled but you can never be certain that the protection is working.

The best way to be sure a database is shut down when idle is to take the database OFFLINE. That would also require a step to bring the database online before each use. Given that necessity, detaching the database would also work with the added advantage that the database reference is removed from any server scoped metadata in the catalog.

This entry was posted in Encryption Hierarchies, Secure Data, Testing. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.