Auto close database

Job details

Name:	Auto close database
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if the contained databases are closed or not after a connection terminates.
Long description:	Checks if the contained databases are closed or not after a connection terminates.
Version:	1.2
Default schedule:	2 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when “AUTO_CLOSE“ is disabled.
disable AUTO_CLOSE	NO	If set to “YES“ the alert will disable “AUTO_CLOSE“ for every contained databases by running ALTER DATABASE [db_name] SET AUTO_CLOSE OFF.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: This monitoring job aims to check the AUTO_CLOSE setting of contained databases in Microsoft SQL Server environments to ensure they are disabled after user sessions end, enhancing security by mitigating unauthorized database access risks post-connection.
Why: The job is critical because it enacts best security practices, especially in environments where contained database authentication happens inside the database rather than at the server or instance level. Keeping AUTO_CLOSE disabled prevents the potential reopening of databases for user authentication, which can be a significant security concern. If AUTO_CLOSE is enabled, unauthorized users might exploit this to access the database content.
Manual checking: To manually check the current AUTO_CLOSE settings of the databases, you can use the following SQL command:

SELECT name, containment, containment_desc, is_auto_close_on FROM sys.databases WHERE containment <> 0

Implementation Details

Automatic Script: The job includes SQL scripts to create tables for tracking AUTO_CLOSE status over time and procedures for checking, reporting, and potentially altering AUTO_CLOSE settings based on the job’s configuration.
Dependencies: The job relies on several components including database info tables and history tables to function correctly, all of which are automatically managed through the script.
Scheduled Checks: The task is configured to run periodically, ensuring continuous compliance and security enforcement without the need for manual oversight.

Reporting and Analysis

Current Settings Report: Provides a snapshot of current AUTO_CLOSE settings for all eligible databases.
Historical Data: Tracks changes and checks of AUTO_CLOSE settings over time, giving insights into compliance trends or deviations.
Aggregated Statistics: Summarizes database checks, offering a broader view of how often and with what results the AUTO_CLOSE settings have been verified.

Configuration Options:

Default action on discovery of AUTO_CLOSE being enabled: The job can be configured to disable AUTO_CLOSE automatically, which reflects an active management stance towards maintaining security standards.
Historical Data Retention: Allows configuration of how long to store historical data, mitigating the risk of bloated databases due to accumulated old data.

System Requirements and Compatibility

Applicability: Specifically designed for Microsoft SQL Server and Azure SQL Managed Instances with versions higher than 2005.
Compliance and Encryption Settings: Categorized under ‘none:Configuration and Encryption Settings’ in dbwatch, pointing towards its focus on configuring database security properties correctly to ensure data safety and compliance with internal or external regulations.

This job not only aids in ensuring security but also helps in aligning the database configurations with best practices and compliance requirements, essential for maintaining the integrity and security of the database environment.